CODEWITHSUNDEEP
vbams-access
firmphem
firmphem

Reputation: 39

Expected end of statement error with FindFirst and findNext

The following code is meant to compare a field value PURE_QP1 of a recordset to another field value PURE_QP1 of another second set. But i am getting end of statement expected error. My knowledge of Access vba is admittedly low. The code is meant to first check if the productcode is present in recordset rst. if it is, then it checks if it is compliant by finding its PURE_QP1 (which coud be more than 1) in another table. the condition for compliance is such that all its QP1s must be found in the table. 

Dim db   As DAO.Database
Dim rst  As Recordset
Dim rst1 As Recordset

If Nz(Me!Combo_Product_number) <> "" Then
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Q_compliant_FCM_EU", dbOpenDynaset)
    Set rst1 = db.OpenRecordset("T_DOSSIER_FPL", dbOpenDynaset)


    Do While Not rst.EOF
        If rst.Fields("PRODUCT_CODE") = Me!Combo_Product_number Then
            rst1.FindFirst "[PURE_QP1] = '"rst.Fields("PURE_QP1")"'"

            If rst.NoMatch Then
                MsgBox ("Product code is NOT compliant to FPL")
                Exit Sub
            End If

            rst1.FindNext"[PURE_QP1] = '"rst.Fields("PURE_QP1")"'"

   Loop
   MsgBox ("Product code is compliant to FPL")

Else
   MsgBox ("Product is not available in FCM_EU")
End If



End If

End Sub

Expected end of staement error is showing in

rst1.FindFirst "[PURE_QP1] = '"rst.Fields("PURE_QP1")"'"

and

rst1.FindNext"[PURE_QP1] = '"rst.Fields("PURE_QP1")"'"

Upvotes: 0

Views: 146

Answers (1)

Krish
Krish

Reputation: 5917

You have an extra End If just before End Sub. That End If should go above Loop command to close the If rst.Fields("PRODUCT_CODE") = Me!Combo_Product_number Then if block.

Also your code regarding rst1 is wrong.

rst1.FindFirst "[PURE_QP1] = '"rst.Fields("PURE_QP1")"'"

should be

rst1.FindFirst "[PURE_QP1] = '" & rst.Fields("PURE_QP1") & "'"

the & sign to join strings are missing in your code.

PS: Have no idea what your code supposed to do, because your find first and find next logic seems to be incorrect.

Upvotes: 1

Related Questions