VBA "IF Then statement" syntax for word 2007

[mcneilkm]

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 ...

   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

[jscher2000]

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?

[kmurdock]

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?

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

[mcneilkm]

this was a huge help... thank you