dcount to find duplicates with ' (or ANY character) (1 Viewer)

mcw21j · Oct 24, 2019

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.

Code:

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 · Oct 24, 2019

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

mcw21j · Oct 24, 2019

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 · Oct 24, 2019

mcw21j said:
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!

Code:

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

arnelgp · Oct 24, 2019

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

mcw21j · Oct 24, 2019

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 · Oct 24, 2019

mcw21j said:
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 · Oct 24, 2019

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

mcw21j · Oct 25, 2019

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

theDBguy · Oct 25, 2019

mcw21j said:
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!

dcount to find duplicates with ' (or ANY character) (1 Viewer)

mcw21j

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

mcw21j

Registered User.

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

mcw21j

Registered User.

theDBguy

I’m here to help

arnelgp

..forever waiting... waiting for jellybean!

mcw21j

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread