DLookup to avoid duplicates and the " ' " problem

[HeatherE]

I'm currently using a DLookup for a form so that the user can't enter a duplicate customer record. Normally it works fine, but when I try to enter in a name with an apostrophe (i.e., O'Neill or O'Donnell), I get an error message. Can anyone help me with this? Here's the code I'm using:

Private Sub ContactLastName_BeforeUpdate(Cancel As Integer)
Dim Response As Integer

If Not IsNull(DLookup("[ContactLastName]", "Customers", "[ContactLastName]= '" & Me.ContactLastName & "' And [ContactFirstName] = '" & Me.ContactFirstName & "'")) Then
Response = MsgBox("There is already someone with the same First and Last name in the " & _
"table." & vbCr & vbCr & "Select Yes to continue, No to start again.", vbCritical + _
vbYesNo + vbDefaultButton2, "Duplicate Data")

If Response = vbYes Then
Exit Sub
Else 'vbNo (Default)
Me.Undo
Cancel = True
End If
End If

End Sub

 

[Jack Cowley]

DLookup("[ContactLastName]", "Customers", "[ContactLastName]= """ & Me.ContactLastName & """ And [ContactFirstName] = '" & Me.ContactFirstName & "'"))

For clarity I have put in extra spaces. Do not add the spaces...

"[ContactLastName]= " " " & Me.ContactLastName & " " "

 

[HeatherE]

Thanks for the fix! If I had only searched the archives, I would have realized the problem...

 

[Lyncroft]

Hello Jack. I've had this problem with the apostrophes and put a post up earlier today but without success............so you're my last resort.

I was having a problem with apostrophes and found this current thread which was great, ie, putting in the 3 quotes. My problem is I have a DLOOKUP that prevents a user putting in the same organisation and same first line of address. Using the 3 quotes works fine for the Organisation but how do I accommodate the first line of the address as they too have apostrophes. I still get the 3075 message.

f Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation] = """ & Me![Organisation] _
& """ And [Address1] = """ & Me![Address1] & "'"))

Spent all day on this one believe it or not!

 

[gellerche]

I found the best way is to replace the ' (or a ") with a `. You can use this example, where Details is the name of the field:

Dim snglchr, replchr, dblchr

snglchr = Chr(39)
dblchr = Chr(34)
replchr = Chr(96)

' *** IMPORTANT
' The following code removes any ' or " from the Error_Msg or Solution and replaces
' them with `. If these are not removed, the SQL statement below will stop at the ' or ",
' and there will be an error.
Details = Replace(Details, snglchr, replchr)
Details = Replace(Details, dblchr, replchr)

and then you use Details (or whatever field name you're comparing to) in your DLookup

 

[Pat Hartman]

The quotes are still not right in your DLookup(). I find the bunches of quotes hard to decipher so I create a constant and use that.

Public Const QUOTE As String = """"

If Not IsNull(DLookup("[Organisation]", "tblOrganisations", "[Organisation] = " & QUOTE & Me.[Organisation] _
& QUOTE & " And [Address1] = " & QUOTE & Me.[Address1] & QUOTE))

I keep all public variables and constants in a separate module so that they are easy to find.

 

[Lyncroft]

Thanks to everyone for your advice. One way of doing it is

If Not IsNull(DLookup("[Organisation]", "tblOrganisations", _
"[Organisation]= " & """" & Me![Organisation] & """" & _
"And [Address1]= " & """" & Me![Address1] & """")) Then

Thanks for the tip of the quotes. I must sit down and get it clear in my mind.

Thanks again