MSG box for duplicate records

[Arob]

Have looked under validating data in the help and have had no success with my problem. I want to have a message box appear after a duplicate value is entered into a particular form feild. Basically I would like use an IF statement which if the ID number entered is a duplicate then display the message box. Any help would be appreciated as to what the If statement should contain.

 

[Grumbledook]

In the before_update Event of the field enter the following code:

Private Sub yourfieldname_BeforeUpdate(Cancel As Integer)

If DCount("yourfieldname", "yourtablename", "yourfieldname = '" & Me!yourfieldname & "'&quot > 0 Then
MsgBox "Duplicate ID number, please insert a unique ID number"
DoCmd.CancelEvent
End If

End Sub

HTH
Dave

This will loop through and check for a unique id number and will return to the field until a unique number is entered.

As an aside, you can put the following code in the 'Event' event of the field to remind users that it requires a unique ID number

Private Sub nameofyourfield_Enter()
MsgBox "You will have to enter a unique product number in this field. If the product number is a duplicate, the database will reject it and wait for another input."

End Sub

 

[Arob]

That worked like a charm, thanks for your help

 

[Grumbledook]

Glad to have helped.

Dave