VBA “IF Then statement” syntax for word 2007

Topic

New Reply

Hi all, at a loss for the correct syntax to see if a connected access database field equals a specific value… using variotions of the following but nothing is working:

some code …

Code:

   Set rst = dbs.OpenRecordset("tbl_Clauses_By_Func_Code_For_Surv_Report")
   Selection.GoTo What:=wdGoToBookmark, Name:="Code_140"
   
   With rst               '.ActiveDocument.Bookmarks
    Do While Not .EOF
      If {[functional code]} = "140" & "*" Then
      Selection.TypeText Text:=![ID] & ""
      Selection.MoveRight Unit:=wdCell, Count:=1, Extend:=wdMove
      Selection.TypeText Text:=![Clause Number] & ""

more code etc…

I have connected to an access database and am pulling table data into word using bookmarks… I want to use the “IF statement” to determine which bookmark to move to based on the data in the access field…

… can someone point me in the right direction

thank you
Kevin

Reply | Quote

Replies

My DAO is very rusty…

If [functional code] is the name of a field in the database, have you tried: rst![functional code]

If you want to match any [functional code] that starts with 140, perhaps you should compare just the first three characters:

If Left(rst![functional code], 3) = “140” Then

Does any of that help?

Reply | Quote

Kevin, I may have misunderstood what you’re attempting, but I believe you need to use Word-ish vba rather than Access-ish vba to get what you need. This is the kind of code I use to query an Access db. Will this get you on the right road?

Code:

Set oDataBase = OpenDatabase(“Name_Of_DB”)
Set oRecordSet = oDataBase.OpenRecordset(“tbl_Clauses_By_Func_Code_For_Surv_Report”)

dim strFieldData as String
strFieldData = “140*”

Selection.GoTo What:=wdGoToBookmark, Name:=”Code_140″
   
Do Until oRecordSet.EOF
    If oRecordSet(“Field_Name”) = strFieldData Then
        Selection.TypeText oRecordSet(“ID”)
        Selection.MoveRight Unit:=wdCell, Count:=1, Extend:=wdMove
        Selection.TypeText oRecordSet(“Clause Number”)
        Exit Do
    End If
   oRecordSet.MoveNext
Loop

DISCLAIMER: I haven’t tested this at all. Also, I added an Exit Do statement once the correct field has been found so it won’t keep cycling through the table.

HTH, Kim :bananas:

Reply | Quote

this was a huge help… thank you

Reply | Quote