Recordset Findfirst Syntax (1 Viewer)

[jmaty23]

I am trying to lookup a record in a recordset. If no match is found then run an append query. I am having trouble coding the findfirst syntax. Any help is appreciated.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim WO As String
    WO = Forms!frmdsh_workorder!txtWO

  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblwoinspection", dbOpenDynaset, dbSeeChanges)
    With rs
        .FindFirst "[Point] = '" & "GMP's" & "' And [WO] = '" & WO & "'"
            If rs.NoMatch Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "qryInspectionPointAppend_WOCloseout_GMP"
                DoCmd.SetWarnings True
            End If
    End With

When I run the Function, it throws a RT error #3077 on the .findfirst line. (Syntax Error (Missing Operator) in expression. )

 

[namliam]

is because of the (extra) ' in your value....

try
.FindFirst "[Point] = """ & "GMP's" & """ And [WO] = """ & WO & """"

 

[jmaty23]

I made the changes that you suggested. When I run the Function now, Access freezes and crashes when I run it like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim WO As String
    WO = Forms!frmdsh_workorder!txtWO

  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblwoinspection", dbOpenDynaset, dbSeeChanges)
    With rs
        .FindFirst "[WO] = """ & WO & """ And [Point] = """ & "GMP's" & """"
            'If rs.NoMatch Then
            '    DoCmd.SetWarnings False
            '    DoCmd.OpenQuery "qryInspectionPointAppend_WOCloseout_GMP"
            '    DoCmd.SetWarnings True
            'End If
    End With
    GoTo ProcedureExit

 

[jmaty23]

I ended up changing the way I lookup the record. It is working good now, thanks for your help!

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWO As String
Dim strPoint As String
    strWO = Forms!frmdsh_workorder!txtWO

  Set db = CurrentDb
  strPoint = "GMP's"
  Set rs = db.OpenRecordset("Select * FROM tblwoinspection WHERE [WO] = """ & strWO & """ AND [point] = """ & strPoint & """", dbOpenDynaset, dbSeeChanges)
    With rs
            If rs.RecordCount = 0 Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "qryInspectionPointAppend_WOCloseout_GMP"
                DoCmd.SetWarnings True
            End If
    End With