I have the below code in field called Vendor Name. This prevents duplicate entries and takes the user to the existing record. It works great except if I add a new Vendor Name that has an apostrophe (example: Staple's Office Supplies) I get an error (3075). Is there anyway to prevent this?
Private Sub VendorName_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim VendorID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
VendorID = Me.VendorName.Value
stLinkCriteria = "[VendorName]=" & "'" & VendorID & "'"
'Check Vendor table for duplicate Vendors
If DCount("VendorName", "UtilVendors", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Vendor Name " _
& VendorID & " has already been entered." _
& vbCr & vbCr & "You will now be taken to the record.", _
vbInformation, "Duplicate Information"
'Go to record of original Vendor Name
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End If
End Sub