Access VBA Code to Prevent Duplicates in my Form Query

[Abbsters]

Hi all,

I have been searching the web and found an "On Error Resume Next" code to prevent users from entered duplicate values for only one field.
Terms:
ICE Detainers - Raw Data = my table where my data is stored
Booking Nbr = the field I cannot have duplicates in

Below is my code that I am using:
Private Sub txtBookingNbr_BeforeUpdate(Cancel As Integer)
On Error Resume Next

If DCount("*", "ICE Detainers - Raw Data", "Booking Nbr='" & Me.txtBookingNbr & "'and Booking Nbr='" & Me.txtBookingNbr & "'") > 0 Then
MsgBox "This Booking Number already exists. Please enter a unique Booking Number to continue. If you would like to search for this duplicate, close the whole detainer form by clicking the X at the top of this form and reopen again from the Main Menu", vbInformation, "Duplicate Booking Number"
Me.txtBookingNbr.SetFocus
Cancel = True

Me.txtBookingNbr.Undo

End If
End Sub

I did get my code to work and the custom duplicate message does pop up. However, when I do input a unique value into this field the Duplicate message pops-up, acting like my unique value is a duplicate. I did double check my table to make sure I was inputting a unique value, which I am. Is there something wrong with my code? PS> I am a novice VBA coder, so please keep help in Lame's terms. .

 

[dhookom]

If you have spaces in field names you pay the penalty of requiring []s. You were missing space and seem to apply the same filter twice.

If DCount("*", "[highlight #FCE94F][[/highlight]ICE Detainers - Raw Data[highlight #FCE94F]][/highlight]", "[highlight #FCE94F][[/highlight]Booking Nbr[highlight #FCE94F]][/highlight]='" & Me.txtBookingNbr & "'[highlight #FCE94F] [/highlight]and [highlight #FCE94F][[/highlight]Booking Nbr[highlight #FCE94F]][/highlight]='" & Me.txtBookingNbr & "'") > 0 Then

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

If his [tt]Booking Nbr[/tt] field is really a Number, no single quotes are required around [tt]Me.txtBookingNbr[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson