dcount to find duplicates with ' (or ANY character)

[mcw21j]

Hello,
I am using the following to search for duplicate text strings in a before update event on a form. I just want to allow the user to be aware of the duplicate value...these values do not have to be unique. The user needs to be able to save duplicate values if they want.

Private Sub ProjName_BeforeUpdate(Cancel As Integer)
  If DCount("*", "tblInvoices", "ProjName='" & Me.ProjName & "'") > 0 Then
    If MsgBox("Duplicate exists. Continue?", vbQuestion + vbYesNo, 
    "Duplicate") = vbNo Then
    Me.ProjName.Undo
    Cancel = True
    End If
  End If
End Sub

This works for the most part. But when I search for strings with an apostrophe, I cannot tab out of the control. I have tried a few other special characters which seem to be ok but I haven't tried everything.

Is there some modification to the code that would allow the search to include ANY character?

Thanks in advance!

 

[arnelgp]

If DCount("*", "tblInvoices", "ProjName=" & Chr(34) & Me.ProjName & Chr(34)) > 0 Then

 

[mcw21j]

Thanks so much arnelgp. It looks like that solved the single apostrophe problem but now the same problem occurs with quotes.

I was worried that other characters might cause the same issue. The ideal code would allow for any text character i can type on a keyboard...$#~`{[};":, etc, etc.

Thank you!

 

[theDBguy]

Thanks so much arnelgp. It looks like that solved the single apostrophe problem but now the same problem occurs with quotes.

I was worried that other characters might cause the same issue. The ideal code would allow for any text character i can type on a keyboard...$#~`{[};":, etc, etc.

Thank you!

Ah, a catch-22 situation then. Have a try at this version then and let us know. Cheers!

If DCount("*", "tblInvoices", "ProjName='" & Replace(Me.ProjName,"'","''") & "'") > 0 Then

 

[arnelgp]

If DCount("*", "tblInvoices", "StrComp([ProjName], " & Me.ProjName & ")=0") > 0 Then

 

[mcw21j]

I can swear I tried something like this before but I suppose my code is more sloppy. Thanks so much, theDBGuy Initial testing proves promising.

 

[theDBguy]

I can swear I tried something like this before but I suppose my code is more sloppy. Thanks so much, theDBGuy Initial testing proves promising.

Hi. You're welcome. Arnel and I were happy to assist. As you can see, there are several ways to fix the problem. Good luck with your project.

 

[arnelgp]

add more test with double quotes and single quotes in one textbox.

 

[mcw21j]

Thanks to both of you gentlemen. I am able to confirm that the replace function was successfully applied to my situation.

 

[theDBguy]

Thanks to both of you gentlemen. I am able to confirm that the replace function was successfully applied to my situation.

Hi. Excellent news! This is good info for everyone. Cheers!