[AccessD] [SPAM SUSPECT] Prevent Duplicates

Not sure why it is not working, but why not index (with no duplicates) the
field in the database and trap the error that occurs when an attempt is made
to save/update the record?

If the fields are unbound, and you want to continue doing this in a similar
way, I'd probably check for the existence of that permit number in the
lostfocus event of the text box.  

If the form is bound, you might want to put something like this in the
beforeupdate event of the form.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hollis, Virginia
Sent: Wednesday, December 14, 2005 10:50 AM
To: accessD at databaseadvisors.com
Subject: [SPAM SUSPECT] [AccessD] Prevent Duplicates
Importance: Low


I do not want the user to duplicate a number already used when entering
a new record. They enter the permit number (text) for the record. I
tried the below code on the BeforeUpdate, AfterUpdate, LostFocus of the
field, but the duplicate MsgBox never pops up telling them it is a
duplicate permit number. They move to another field without the message
telling them to enter a different number & it does not even undo the
entry, it does not let them save the record either (nothing happens when
you click save). Do I have the quotes wrong for a text field around
PermitNumber? Or where should I place the code? Somewhere on the form or
does it go on the field?

 

Virginia

 

Private Sub PermitNumber_BeforeUpdate(Cancel As Integer)

  If Not IsNull(PermitNumber) Then

        If DCount("PermitNumber", "tblPermitLog", "PermitNumber = " &
[PermitNumber]) > 0 Then   

            MsgBox "You have entered a Permit Number that already
exists"

            PermitNumber.SetFocus

            PermitNumber.Undo

        End If

    End If

End Sub