[AccessD] Prevent Duplicates

One small change to the code. You say PermitNumber is a text field, so the
code (in the BeforeInsert and BeforeUpdate events) should be...


  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

. i.e the criteria string (expanded so you can see) is "PermitNumber = ' " &
[PermitNumber] & " ' ") 

Lambert

-----Original Message-----
From: Heenan, Lambert 
Sent: Wednesday, December 14, 2005 12:02 PM
To: 'Access Developers discussion and problem solving'
Cc: 'Hollis, Virginia'
Subject: RE: [AccessD] Prevent Duplicates


The reason it never fires is that BeforeUpdate only applies to *existing*
records. You need to run the same code in the BeforeInsert event.

Lambert

-----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: [AccessD] Prevent Duplicates


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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com