Search a Word with VBA

Hello Friends,
I want to write a code in VBA that should search some specific words in the modules and there should be also an option to replace that words with another words. Can someone help me with the Code?


Post your answer or comment

comments powered by Disqus
Hi all!
I want to use a VBA to search a record with many conditions. I am a beginer, so I down know there are any way to do this? May I use a query? First I thinks the query but I do not know how to use a query with my variations.
Does anyone help me. I thanks very much!
-- Minh Hien--

Hello, I'm trying to search an entry of a recordset with VBA

The entry can only be found by searching 3 field values. In my case the correct entry can only be found if the data in the field Date, No_Employe and No_Project all match the data of the entry I want.

Findnext doesn't work, it only access one field and I need three.
Seek doesn't work since it need to search a key, and neither Date, No_Employe or No_Project are keys.

How can I proceed ?

Should I use multiples Findnext ( how do I do that ? ) ?


EDIT :

Also please note that I'm searching for 400-500 entries in a row, so speed is an issue.

Hi all,

This may be stupid question, can I display a word with a field I mean
i wish to display a word "program" attached with a field

like this [fieldname] program

eg: special program
easter program
christmas program

becuase this fieldname changes with different criteria...

Thanks in advance

thanks

I have a question. I want to able to search for a record in a form using VBA.
I've constructed the following while loop:

Sub SelectCurrentBeg(dBegid As Double)
Dim temp As Double
temp = Begid.Value

While temp dBegid
DoCmd.GoToRecord , , acNext
temp = Begid.Value
Wend
End Sub

Now the Begid.Value is a editbox on the form bound to a field in a table. The thing is, the value which the compiler returns from the editbox is always the same. Where on the form i see the value change everytime i go to a next record.
I've also tried repainting the form on every new record, but for some reason i always end up with the same value

What am I doing wrong??

Regards,

Ted de Vries

Hello,

I'm working on VBA to fill a Word document with Access data.
I need to kow the current character (cursor) position in my Word document to define a range (from the current position to current position + n characters) where I want to insert my data dynamically.

Since Range parameters are integers, I need an integer to locate where I am in my Word document.

It should be a basic Word VBA method or function, but I didn't found anything like that in Word objects methods until now. STRANGE isn't it ???

Any idea or tip ?

Thanks.

Using Microsoft Office 2007, and Windows XP. In Access 2007 a Word document is created, text is added, and Word closed with VBA. Then unable to open the document because it is 'locked by user'.

***********Code*********************
Dim wo As Word.Application
Dim path As String

Set wo = CreateObject("Word.Application")
wo.Documents.Add
wo.ActiveDocument.SaveAs FileName:=path

'Text added to the document

wo.ActiveDocument.Save
wo.ActiveDocument.Close False
wo.Quit
Set wo = Nothing
**********End of Code******************

The code is successful in creating the document and when looking in the directory, the file is there but with ~owner's file. When attempting to open the file with VBA, the message states the file is locked by the user who created the file.

Hello again,

I have an email function that uses an .oft template however, I want it to reference an attached .oft template in a table. This DB will be sent career field wide for my job so I need it to be as user friendly for those who have no experience working with VBA for ex they wouldn't know how to change the filepath on their own in VBA. Hopefully this is a simple question to answer.

Hi all,

Pretty much a complete n00b to anything thats more advanced than a few tables and forms but have managed to write a whole load of VBA script which essentially returns values into strings from a whole load of stuff in excel.

Now my question is, can i take the value in my string from the VBA and use it to replace an existing value in a table where I can specify the field and record of which I would like to replace?

I'm just completely at a loss with this.

Many thanks,

petna

Hi

I've done searches on this and am guessing I probably need Index and Match but have gotten completely confused with the answers that are there and can't see if any of them apply to my query.

I have a sheet with Primary Diagnosis, First Secondary Diagnosis, Second Secondary Diagnosis, up until 6th.

I have a list of diagnoses and want my formula to return the first one of any matches that appear in the range.

Sheet 2 is the list of values that I want to use to lookup from.

Sheet 1 is the main data, with column C where I want to put my formula.

What I want this to do is return in each row, the first instance any of the values from the lookup match, ie in the first one it will be I209. Not every row has more than one instance.

I have played with Lookup, HLOOKUP, Match and Index and can't figure it out.

Any help would be really greatly appreciated.

Thank you

Ally

I have a form with lots of information on it and I need to add a couple of combo boxes to search for the next record to display on the form via different criteria (customer surname, or address or order no etc). My problem is that there isn't much room left on the screen to put the combo boxes. How can I create a sort of pop up message box (ie probably another form) on which the combo boxes are used following which the main form then automatically shows the required record?

thanks


Dennis

I have a form (Section_Editor) that includes an OLE control (Editor_Window). The click event on the OLE control opens an existing or new Word document for editing. After editing, a ribbon control click retuns the edited document to the OLE control, returns some other values to other form controls and then quits Word.

It works, but when I then click on any other form control or navigate to another record, the Word document again pops up in Word. I can then return again to Access, with Word quitting again. At that point, I can click on other form controls or navigate to another record without the document launching again in Word (i.e., it only happens once).

I am a novice with VBA, so the problem may be obivious--but unfortunately not to me. Here is the code I am using:

ACCESS OLE CONTOL CLICK EVENT

Private Sub Editor_Window_Click()
On Error GoTo NoOLEObject
Me![Editor_Window].Verb = VERB_OPEN
Me![Editor_Window].Action = OLE_ACTIVATE
Exit Sub
NoOLEObject:
If Err = 2684 Then
Me![Editor_Window].OLETypeAllowed = OLE_EMBEDDED
Me![Editor_Window].SourceDoc = "C:Documents and Settings My DocumentsSection_Template.docm"
Me![Editor_Window].Action = OLE_CREATE_EMBED
Resume
Else
MsgBox "Error - " & Str(Err) & " : " & Error
End If
Exit Sub
End Sub


WORD RIBBON BUTTON CODE TO RETURN TO ACCESS

'Callback for ButtonA1 onAction
Sub ButtonA1Macro(control As IRibbonControl)
On Error GoTo Err_ButtonA1Macro
Dim acApp As Access.Application

'***Reference running instance of Access***
Set acApp = GetObject(, "Access.Application")

**********some omitted code that defines and sets SecVariableString and SignatureString**********

'***Transfer fields to Access form***
With acApp
[Forms]![Section_Editor]![Variables].Value = SecVariableString
[Forms]![Section_Editor]![Signature].Value = SignatureString
End With

'***Shut down Word document***
Application.Quit

Exit_ButtonA1Macro:
Exit Sub

Err_ButtonA1Macro:
MsgBox Err.Description
Resume Exit_ButtonA1Macro
End Sub

Thanks for any help!

This seems so basic, but it's stumped me. I have a form with the On Current property set to not allow edits - me.allowedits=False. The user has to push an Edit button for it to allow them to edit the record. This part works great. What I'm running into is an error message when trying to search while the editing is set to false - it brings up the search dialog box, but gives me an error message 2455 and jumps to my code when I try to search. I've tried setting up my own search button instead of using the default one on the toolbar, that unlocks the editing first then searches. But of course when this button is pushed it takes the focus off the field they were in , so when the search is performed it searches all fields instead of just allowing them to search the one field they were in. Hope this makes sense. Any suggestions?

Hello Everybody,

I would like To make a query that search a price from 2 criteria. If someone could help me please.

the table of cost price

Code: DATE CODE Article Price 01/02/2011 codeA articleA 789,45 07/02/2011 codeA articleA 750,02 11/02/2011 codeA articleA 724,99 01/05/2011 codeA articleA 773,25 01/06/2011 codeA articleA 820,03 the query that will search for the first criteria 'codeA' and second criteria the date with is the closest date before the invoice date and display the cost price.

Code: InvoiceDate CODE Article Price 05/02/2011 codeA articleA 789,45 15/02/2011 codeA articleA 724,99 01/05/2011 codeA articleA 773,25 18/06/2011 codeA articleA 820,03 thanks a lot for your help

I've created a form (Access2000) in which you can select a printer among the printers on your system. By clicking the "ok" button, the selected printer in this listbox now should be saved on all other printable forms in this application automatically. How can I set a printer with vba-code (prtdevmode and prtdevnames?) for a form?

I have a form with a subform that I send prebuilt filters too. When I select from my combo box which filter to apply it applies the filter but it does not show the filter icons above the fields on the datasheet...

Question 1: How can I get the subform datasheet to show the filter icon next to the filterd field?

Question 2: When I change the filter to "" or nothing the form does not unfilter. I even tried filteron = false but no change?

Thanks,

TheChazm

I have a query that contains a field with a calculation (a long series of if...then) Forgive me if my terminology is not right.

I would like to be able to change the calculation in that field depending upon user-input on a form.

Here is an illustraion of what I would like to do:

Take query named CALC based on a table with Columns A and B, both containing numeric data. My query includes a column(Field) that I name RESULT: A+B and the query's RESULT adds the data in A & B.

However, the user decides the B should be subtracted from A and via a checkbox on a form, a macro runs (or some sort of programming) that changes the RESULT field in the CALC to be RESULT: A-B.

Can I do this in Access? How?

Hi,

I added three new fields in an existing table with VBA. These new fields where all added at the end of the table (after the other fields). Is it possible to add them (or move them) at the beginning or inbetween two other fields with VBA?
If so, how can this be done.

Thanks already.

Dear everybody,
My code like this :
----------------------------------------
Private Sub imgCandidate_Click()
cdOpen.InitDir = CurrentProject.Path
cdOpen.ShowOpen
txtPhoto = cdOpen.FileName
imgCandidate.Picture = cdOpen.FileName
End Sub
-----------------------------------------
image Path in Database is : C:Photoimagename.jpg
---------------------------------------------------
I don't want to use the C:.. path. Cos i can only use it with local PC and store data in drive C. I want to use it with all PC in my network and i can store my data at anywhere i also can use it . i want save the image path in my database only is imagename.jpg.
(using Access 2003, display image in a form)
Help me to write the code for me please. i am a newby with VBA Access.Thanks.

Hope you can help,i know this should be simple but it is bugging me,
I have a form with a two cacading combo boxes, i have a button to delete the record, filtered by the second combo box. i use DoCmd.RunCommand acCmdDeleteRecord when the button is clicked.
it delets a record but not the one selected, it deletes the first one in the combo box list
Quote: Public Sub cmdExitAndDelete_Click()
On Error GoTo Err_cmdExitAndDelete_Click

If IsNull(cboStation) Then
MsgBox ("You have to enter data first!")
Me.cboStation.SetFocus
Cancel = True

ElseIf IsNull(cboStaff) Then
MsgBox ("You have to enter data first!")
Me.cboStaff.SetFocus
Else
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "frmDelete_Staff"
End If

Exit_cmdExitAndDelete_Click:
Exit Sub

Err_cmdExitAndDelete_Click:
MsgBox Err.Description
Resume Exit_cmdExitAndDelete_Click e

End Sub I have used this option with the same result
Quote: DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70


Peter,

Ok, I have taken a swag a trying to populate a unbound listbox on a form with VBA. I'm here so it means that my swag didn't fair so well. My ultimate goal is to limit the records that show in a form based on a users access level that is stored in a hidden form.

I have figured out how to filter the actual records displayed in the form in vba using a where stmt in the DoCmd.OpenForm code but I cannot figure out how to do the same for the listbox. If you are curious, I use the list box as a means to quickly select a record in the form rather that use the navigation buttons.

In general terms I envision some vba code that goes something like this:

If userAccess < X THEN
display all records in the listbox.

ELSEIF userAccess = X THEN
display only records A,B,C in the list box

ELSE
display no records in the listbox

My test code is below. As expected MS ACCESS popups a big error message that reads "HA HA HA, you have no idea what you are doing; keep trying." For one I have no idea which event to put this code in. ON top of that I have no idea if the code below is even close to being right.

I have tried ON Open for the form: Compile error: expected: expression
I have tried BEFORE UPDATE for the listbox: same error.

Me.lbLessonList.RowSource = SELECT qryLessonCardData.LNumber, qryLessonCardData.LTitle _
FROM qryLessonCardData _
WHERE qryLessonCardData.Inactive = 0 _
AND qryLessonCardData.LDivision = 1 _
ORDER BY qryLessonCardData.LNumber;

Help?
Sean

When I have a frontend with VBA (for table additions etc) and a backend (all tables except Switchboard Items), when i change from my test-backend (USB) to the realtime-backend (Network drive/directory) the tables work, but all VBA calls (events like onLoad etc.) break (i get an error message). The only way I can fix it atm is by using the Compress and repair database, which restores the links between VBA and the forms etc.

What causes this to happen? Is it a known bug in Access 2010?

Hi,

I am attempting to create a word document from VBA code in Access. I am having a problem with Normal.doc when running my code. If I delete normal.dot my code works fine and the document is created ok the first time I run it. Every time after, I usually get a error stating "Normal.dot caused a serious error last time it was opened, do you want to continue opening it?" Other times, it will simply open word (with no document open). One other thing I've noticed is that sometimes when I try to close the document it will ask me if I want to save changes to normal.dot.

This is very frustrating considering I got the code straight from a book about Access.

Here is the code:

Dim objWord As Word.Application
Dim objTable As Word.Table

On Error Resume Next

'Use a running word instance if possible
Set objWord = GetObject(, Word.Application)

'Otherwise use a new instance
If objWord Is Nothing Then
Set objWord = New Word.Application
'If true, Word is not installed.
If objWord Is Nothing Then
MsgBox "Word is not installed on your system"
End If
End If

objWord.Documents.Add
objWord.Activate
objWord.Visible = True

objWord.Selection.TypeText Text:="203 Rose Avenue"

Set objWord = Nothing

Exit Sub

I have tried deleting normal.dot and also making it read only. I have also tried re-installing office. No luck there.
I have tried the code on two other machines. On one machine (Win XP Pro, Office XP Pro) it runs fine, but on another (Win XP Pro, Office XP Pro) it has the same troubles as the machine I am currently developing on (Win XP Home, Office XP Pro).

Someone please help!

1. Users click a command button.

2. A table is created (done via a make-table query) which contains all of the data from the currently displayed record which is to be linked to a Word Template file (unfortunately, due to some network issues, Word/the template file cannot access the query directly - at least that is the reason I have been told).

3. The word template file is opened via:Dim objWord as Object
Set objWord = GetObject("filename","Word.Document")(this is not my knowledge, I got the idea from searching the forum)

4. The user views the template in Word, adjusting anything if necessary and printing off the document.

5. The user saves the document.

Problems:

1) The User still needs to click on the merge field toggle toolbar icon to get the correct data to display (if set to automatically display they need to click it twice, off then on).

2) How can I get the file to save with a predetermined filename? - One of the merge fields is already set up to provide this information and I have placed this field on the first line of the template as white text so that it remains hidden, but if opened using the above method the document is opened as the file and not as a new document based on the template - if opened manually then then Save is selected the filename is picked up (although typical Word it is ignoring everything after the first the underscore that I substituted for the spaces) .

I'm not that good with VBA, I you can't already tell! So a guiding hand on how to go about acheiving the above would be useful.

The key points are:The user needs to review the document before printing it. The whole process should be as transparent to the user as possible. I'm *really* stretching my VBA knowledge here!
I have seen some other examples (which looked very complicated) which involve passing the SQL for the mail-merge. I am dubious as to whether this will work due to the network access problem (only some of the queries can be seen by Word when the file is located where it will need to be stored). Also, the SQL for the query will be quite long; although I would only copy it out of the Query builder, it is still likely to cause an issue in the VBA editor, for readability, if for nothing else.

Tim

I'm trying to use Access 2003 VBA to automatically fill in a Word form with text fields; however, in several instances, I'm having difficulty passing a parsed string to a field. The following code is an example of what I've been using.


	Code:
	strVerification = ""

If Forms!frmHazardsAndControls3_1.fraYesNo.Value = 1 Then
    
    'Control 3.a) is applicable
    If Forms!frmHazardsAndControls3_2.chk1.Value = -1 Then

        docWord.FormFields("chk3a").CheckBox.Value = True
        
        strVerification = strVerification & _
            "3.a)1. Venting analysis.#" & _
            "3.a)2. Review of Design.#" & _
            "3.a)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    'Control 3.b) is applicable
    If Forms!frmHazardsAndControls3_2.chk2.Value = -1 Then

        docWord.FormFields("chk3b").CheckBox.Value = True
    
        If Forms!frmHazardsAndControls3_2.txt3bEquivalentICD.Value  "" Then
        
            docWord.FormFields("bmkControl3b").Result = _
                "(" & Forms!frmHazardsAndControls3_2.txt3bEquivalentICD.Value & ")"
                
        End If
    
        strVerification = strVerification & _
            "3.b)1. Venting analysis.#" & _
            "3.b)2. Review of Design.#" & _
            "3.b)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    'Control 3.c) is applicable
    If Forms!frmHazardsAndControls3_2.chk3.Value = -1 Then

        docWord.FormFields("chk3c").CheckBox.Value = True
    
        If Forms!frmHazardsAndControls3_2.txt3cEquivalentICD.Value  "" Then
        
            docWord.FormFields("bmkControl3c").Result = _
                "(" & Forms!frmHazardsAndControls3_2.txt3cEquivalentICD.Value & ")"
                
        End If
        
        strVerification = strVerification & _
            "3.c)1. Venting analysis.#" & _
            "3.c)2. Review of Design.#" & _
            "3.c)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    'Control 3.d) is applicable
    If Forms!frmHazardsAndControls3_2.chk4.Value = -1 Then

        docWord.FormFields("chk3d").CheckBox.Value = True
        
        strVerification = strVerification & _
            "3.d)1. Venting analysis.#" & _
            "3.d)2. Review of Design.#" & _
            "3.d)3. QA Inspection and certification of the as-built " & _
            "hardware per approved design drawing.##"
        
    End If
    
    If Forms!frmHazardsAndControls3_2.chk1.Value = 0 _
    And Forms!frmHazardsAndControls3_2.chk2.Value = 0 _
    And Forms!frmHazardsAndControls3_2.chk3.Value = 0 _
    And Forms!frmHazardsAndControls3_2.chk4.Value = 0 Then
    
        strVerification = "See Unique Hazard Report: (fill in HR number here)"
        
    End If
            
    docWord.FormFields("bmkVerification3").Result = strVerification
    
Else

    'Hazard is not applicable
    docWord.FormFields("bmkVerification3").Result = _
        "N/A, no intentionally vented containers."
        
End If

When I run the code, if more than one checkbox is selected, the string strVerification parses together quite nicely within Access (I've checked this with breakpoints and the Locals window in VBE), but...when it comes time to pass it to the Word document, nothing happens. The text field remains blank.

If only one check box is selected, there's no problem at all, and the string passes fine to the Word document.

Similarly, but a little more strangely, I've got other instances of code that are similar in structure, but up to 3 check boxes can be selected and the string will parse and pass to Word without any problem, but with 4 or 5 checkboxes selected, the string won't pass.

I've compared the different instances of code without finding any differences in the structure, and am really at a loss why this is happening.

Any idea what might be causing this, and how I can fix it? I'm not a professional developer, so even the most basic advice is appreciated.

Cheers.


Not finding an answer? Try a Google search.