Link to home
Start Free TrialLog in
Avatar of J G
J G

asked on

VBA script to check for duplicate primary keys on lost focus

When entering a new record I want access to check for duplicate pk entry when a user tabs out of the field.  If the user enters a duplicate value desired behavior would be to have a message box saying "pk already exists in database"

How would this be accomplished?
Avatar of Joseph Krausz
Joseph Krausz

First of All. A PK is not meant to even be able to have duplicates, as the Auto-number Data type should be assigned. furthermore, the PK isn't something to be entered by the User. What I assume you want to do is. to have a unique field and make sure the user do not duplicate it. to accomplish this you can use the Before_Update event of the form and lookup the field' Value with DLookup whether it already exists or you can simply set the table to allow unique records for this particular field, so the user will get an access error, that's duplicate
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@hnasr

Please explain why you encourage user-entering PK, instead of leaving the PK as a meaningless field just for the purpose of PK and adding another field for unique entries
Avatar of J G

ASKER

Because I am making a produce database.  The Product UPC/PLU has to be unique in order for it to sell at the POS, so it is easier to make it the PK.
Avatar of J G

ASKER

OCuld you explain this part of the Dcount:


 "aid='" & aID & "'")



what is the aID refer to?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of J G

ASKER

why wouldn't this go into on lost focus event?
why wouldn't this go into on lost focus event?
It doesn't go into the Lost Focus event because you don't want to run the code if the value hasn't changed.  But you do want the code to run if the user has forgotten to even enter an aid.  Validation code should only go into the form's BeforeUpdate event or in some cases a specific control's BeforeUpdate event.  Doing validation in any other events is fruitless since you cannot prevent the bad data from being saved.  The "empty" check MUST go into the FORM's BefoeUpdate event, so I tend to just put all edits in that event unless I have a specific reason for doing the validation in the control event.  There is no way to use the Lost Focus event for an "empty" check because if focus never enters the control, then focus never leaves it so your code would never identify a missing value.

It is never a good idea to use a field over which you have no control as the primary key of a table.  Use an autonumber.  Then add a unique index on the Product UPC/PLU to prevent duplicates.  hnsar gave you sample code that will enable you to produce a meaningful error message for the user rather than allowing Access to generate a generic one that will be confusing.

Since the code runs in the BeforeUpdate event of the form, focus has moved from the data entry field so I like to put the focus back into the field with the error and exit the sub immediately when an error is found.  You also need to ensure that the aid field is actually populated.

Private Sub aID_BeforeUpdate(Cancel As Integer)
    If Me.aid & "" = "" Then
        Msgbox "aid is required.",vbOKOnly
        Cancel = True
        Me.aid.SetFocus
        Exit Sub
    End If
    If DCount("aid", "a", "aid='" & aID & "'") > 0 Then
        MsgBox ("pk already exists in database")
        Me.aid.SetFocus
        Cancel = True
        Exit Sub
     End If
End Sub
why wouldn't this go into on lost focus event?
If you type x and leave the control, and you realize, just after taping out of control,  that this value will cause a duplicate, then you tab back and correct it. If code is in lost focus it will bother you with unnecessary information. This is added to what PatHartman
 pointed out if tab out without a change.
Avatar of J G

ASKER

Ok Guys....

In this particular form I would like it to be on Lost Focus.

I was able to get the following code to work in a public module:  fields and table are now:  (PLU, tbl_item, Forms![New_Item_Form]![PLU])

Function PLU_OnLostFocus()
    If DCount("PLU", "tbl_Item", "PLU='" & Forms![New_Item_Form]![PLU] & "'") > 0 Then
        MsgBox ("PLU already exists in database")
        Forms![New_Item_Form]![PLU].SetFocus
        End If
   
End Function

This is working how I want it to now with one exception.  after I hit ok on the message box, the focus jumps from [New_Item_Form]![PLU] to the next field in the form.  How can I get it to stay?
Try it without the message box.
Avatar of J G

ASKER

when I comment out the msgbox line of code, it just tabs to the next field.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Modified previous comment.
FYI, the sequence of events IS:
control BeforeUpdate
control AfterUpdate
Form BeforeUpdate
Form AfterUpdate
control LostFocus

----- so - by the time the lostfocus event even runs, the bad data in the control is already saved!!! so if you click the form close button, no matter what your message says --- the data is already saved by the time the control's Lost Focus event even runs!!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial