check a record for Duplicates Records, Before Crea

Question

I am new to this site and new to vba code. Another user posted this question and the answer code seemed to be the code I was looking for. I want to check several fields for duplicate data, flag a message and go to the record. I was hoping this code would do that. Except when I add my own fields to check I get error code 3077 - syntax error and I am not sure how to fix it. any help would be appreciated. The line highted for this error is the 3rd line down. I am using access 2010. I am not sure how to link to the question but was advised I should ask a new question regarding my problem.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "[ID] <> " & Me.ID & " AND [TitleText] = " & Me.TitleText & " AND [UnitCode] = " & Me.UnitCode & " AND  [AcademicYear] = " & Me.AcademicYear & " AND    [Titleofchapterjournalarticle] = " & Me.Titleofchapterjournalarticle
    If Not rst.NoMatch Then
        Cancel = True
        If MsgBox("A record matching these fields already exist", vbYesNo) = vbYes Then
            Me.Undo
            DoCmd.SearchForRecord , , acFirst, "[ID] = " & rst("ID")
        End If
    End If
    rst.Close
End Sub

Answer 1

I think you mean that the fourth line down is the one highlighted, rst.FindFirst....

If that is the case, there is a syntax error in your FindFirst string. It could be that your TitleText or Titleofchapterjournalarticle fields contained spaces or logic code (OR, AND). To prevent this, surround any string type fields with ' (single quote) inside the string.

The final result will look like:

rst.FindFirst "[ID] <> " & Me.ID & _
 " AND [TitleText] = '" & Me.TitleText & _
 "' AND [UnitCode] = " & Me.UnitCode & _
 " AND [AcademicYear] = " & Me.AcademicYear & _
 " AND [Titleofchapterjournalarticle] = '" & Me.Titleofchapterjournalarticle & "'"