0

I have an indexed field, and if a duplicate entry is attempted on a form, I want to give the user the option to navigate to the original record. I tried the following code, adapted from an answer to a similar problem I found here.

In my case, the [BOL] field is a text field that may contain multiple hyphens. What syntax do I need to use for Me.BOL? As an example, when I tried to enter "CT-J17-XUSH-T001" into my BOL field, I got the error: Run-time error '3070': The Microsoft Access database engine does not recognize 'CT' as a valid field name or expression.

Private Sub BOL_BeforeUpdate(Cancel As Integer)


'https://stackoverflow.com/questions/14608052/prevent-duplicate-records-query-before-creating-new-records

Set rst = Me.RecordsetClone
rst.FindFirst "[ShipmentNumber] <> " & Me.ShipmentNumber & " AND [BOL] = " & Me.BOL
If Not rst.NoMatch Then
    Cancel = True
    If MsgBox("BOL already exists; goto existing record?", vbYesNo) = vbYes Then
        Me.Undo
        DoCmd.SearchForRecord , , acFirst, "[ShipmentNumber] = " & rst("ShipmentNumber")
    End If
End If
rst.Close

End Sub
Community
  • 1
  • 1
Can'tSpeakTheLanguage
  • 3
  • 2

1 Answers1

1

Add single quotes around text field values:

rst.FindFirst "[ShipmentNumber] <> " & Me.ShipmentNumber & " AND [BOL] = '" & Me.BOL & "'"
Sergey S.
  • 6,296
  • 1
  • 14
  • 29