The trouble with updates

[scottian]

Can anyone spot what went wrong.

Im trying to update a couple of fields in the main table of a DB. But when i try to run this query i get an error
"Runtime Error 3075"
"syntax error (missing operator) in query expresion"


Function WorkFM()

Dim ThisDB As DAO.Database
Dim WMFRecSet As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT [STATUS], [CompDate], [FBR] " & _
"FROM tblMain LEFT JOIN [qryAccGroup] " & _
"ON tblMain.[FBR] = qryAccGroup.[FullReference] " & _
"WHERE (tblMain.[STATUS] Like 'pen*') AND (qryAccGroup.[FullReference] Is Null);"

Set ThisDB = CurrentDb()

Set WMFRecSet = ThisDB.OpenRecordset(strSQL, dbOpenDynaset)

Do Until WMFRecSet.EOF

WMFRecSet.MoveNext

DoCmd.RunSQL "UPDATE tblMain SET tblMain.[STATUS] = 'COMPLETED', tblMain.[CompDate] = Now() " & _
"WHERE tblMain.[FBR] = strSQL.[FBR];"

Loop

WMFRecSet.Close

End Function


any help is much appreciated





------------------------------------------------------------------------------
Ambition..........If you dont use it, you wont lose it

 

[scottian]

Sorry,
the error pointed towards this line

strSQL = "SELECT * FROM Table1 WHERE index is null"





------------------------------------------------------------------------------
Ambition..........If you dont use it, you wont lose it

 

[lespaul]

I don't see that SQL anywhere in the code you provided ??? You may want to use the Debug command to see what strSQL really is when you're running the code.

Leslie

 

[dhookom]

I think this might be what you want

Function WorkFM()
   Dim ThisDB As DAO.Database
   Dim WMFRecSet As DAO.Recordset
   Dim strSQL As String

   strSQL = "SELECT [STATUS], [CompDate], [FBR] " & _
      "FROM tblMain LEFT JOIN [qryAccGroup] " & _
      "ON tblMain.[FBR] = qryAccGroup.[FullReference] " & _
      "WHERE (tblMain.[STATUS] Like 'pen*') AND (qryAccGroup.[FullReference] Is Null);"

   Set ThisDB = CurrentDb()
   Set WMFRecSet = ThisDB.OpenRecordset(strSQL, dbOpenDynaset)
   Do Until WMFRecSet.EOF
      WMFRecSet.MoveNext
      [GREEN]'If FBR is numeric[/GREEN]
      DoCmd.RunSQL "UPDATE tblMain SET [STATUS] = 'COMPLETED', [CompDate] = Now() " & _
         "WHERE [FBR] = " & WMFRecSet![FBR]
      [GREEN]'If FBR is text[/GREEN]
      DoCmd.RunSQL "UPDATE tblMain SET [STATUS] = 'COMPLETED', [CompDate] = Now() " & _
         "WHERE [FBR] = """ & WMFRecSet![FBR] & """"

   Loop
   WMFRecSet.Close
   Set WMFRecSet = Nothing
   Set ThisDB = Nothing
End Function

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

scottian . . .

Also note:
[ol][li]By putting [blue]WMFRecSet.MoveNext[/blue] at the beginning of the [blue]D0 Loop[/blue], you skip the 1st record in the recordset.[/li]
[li]With your current position of [blue]WMFRecSet.MoveNext[/blue] your sure to generate an error![/li]
[li]2 Above is of no consequence since you don't use [blue]WMFRecSet[/blue] in the loop.[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Golom]

Note that, in

DoCmd.RunSQL "UPDATE tblMain SET tblMain.[STATUS] = 'COMPLETED', tblMain.[CompDate] = Now() " & _
"WHERE tblMain.[FBR] = [red]strSQL.[FBR][/red];"

the reference in [red]red[/red] will be interpreted as a reference to field [FBR] in a table (or query) named strSQL. In fact, strSQL is the name of a string in your program and not a database table or query.

The recordset based on strSQL is WMFRecSet so you probably want something like

DoCmd.RunSQL "UPDATE tblMain " & _
             "SET tblMain.[STATUS] = 'COMPLETED', " & _
             "    tblMain.[CompDate] = Now() " & _
             "WHERE tblMain.[FBR] = [red]" & WMFRecSet.[FBR][/red]

You will need to put quotes around it if [FBR] is a character field. Note also TheAceMan1's observation that your MoveNext should be after the DoCmd ... not before it.