weird error message on prevent duplicate name entry

[lkwander]

So now, I'm using the code below to prevent duplicate name entry and it is working great - EXCEPT when I enter a first or last name which contains a ' (ie, O'Tool, O'Malley, O'Hern)...anyone got any ideas for me on how to make this not happen?

The error I get is:

Run Time error '3075'

Syntax error (missing operator) in query expression '[Last Name]='O'Hern' And [First Name]='Lori'.

The code i'm using is:

Private Sub Last_Name_AfterUpdate()

'Check for duplicate first and last name using DCount

If DCount("*", "[Constituents]", "[Last Name]= '" & Me![Last Name] & "' And [First Name] = '" & Me![First Name] & "'") > 0 Then
Beep
MsgBox "This first and last name already exists in the database. Please check that you are not entering a duplicate constituent before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub

 

[adevera]

I believe ' is a reserved character (use for string expression). So when you try to query out O'Hare, or O'Connel, Access is trying to tie the ending string expression.

 

[KenHigg]

This may work:

If DCount("*", "[Constituents]", "[Last Name]= """ & Me![Last Name] & """ And [First Name] = """ & Me![First Name] & """")

Ken