Preventing Duplicate Entries

[Don]

I have written a bit of code to prevent a record from being entered if it is
already in the database. I've done this before and have had success. For
whatever reason, I can't get this to work. The code completes the first part
of the "If" statement and then ends, even if the information in the record
already exists. I've incuded the code below. I'd appreciate any feedback.
Thanks...

What I'm trying to say is this. If the item I'm entering in ITEM_NO on my
form is in the tblITEMS table, I want the message box to warn me and then
undo my entry. If the ITEM_NO is not in the tblITEMS table, I want to set
the focus to ITEM on my form.

Private Sub txtITEM_BeforeUpdate(Cancel As Integer)

If IsNull(DLookup("ITEM_NO", "tblITEMS", _
"ITEM_NO ='" & Forms![frmITEM_MAINTENANCE]![ITEM_NO] & "'")) Then
Me.ITEM.SetFocus
Else
MsgBox "This item is already in this database!", vbCritical,
"Duplicate Entry Attempt"
Cancel = True
Me.ITEM_NO.Undo
End If

End Sub

 

[Keith Wilby]

What I'm trying to say is this. If the item I'm entering in ITEM_NO on my
form is in the tblITEMS table, I want the message box to warn me and then
undo my entry. If the ITEM_NO is not in the tblITEMS table, I want to set
the focus to ITEM on my form.

Private Sub txtITEM_BeforeUpdate(Cancel As Integer)

If IsNull(DLookup("ITEM_NO", "tblITEMS", _
"ITEM_NO ='" & Forms![frmITEM_MAINTENANCE]![ITEM_NO] & "'")) Then
Me.ITEM.SetFocus
Else
MsgBox "This item is already in this database!", vbCritical,
"Duplicate Entry Attempt"
Cancel = True
Me.ITEM_NO.Undo
End If

End Sub

It looks OK to me except that I'd use the Me keyword instead of using the
forms collection:

If IsNull(DLookup("ITEM_NO", "tblITEMS", "ITEM_NO ='" & Me.ITEM_NO & "'"))
Then

Not sure if that's the problem but it's worth a try.

HTH - Keith.
www.keithwilby.com