Avoid Duplicate Record

  • Thread starter Thread starter JoeCL
  • Start date Start date
J

JoeCL

In a data entry form, how can I checked if the data in the
field already exist in the table. I have a field called
Claim Number; if the user enters a Claim Number, the
program should check the table if the number entered in
the field is already used. Please note I am not familiar
with VB. Thanks.
 
You see I acquired this database from another person I
just added this claim number field so most of the records
right now have blank claim number and they don't really
have assigned claim number. It was only implemented when I
suggested it. So uniqueness would not work.

Is there a way in a field in the form where I can query a
table for duplicates? Thanks a lot.
 
Sure, you could use this simple function: DLookup("FieldName" , "TableName"
, "Criteria = n") in the beforeUpdate Event e.g.

Private Sub ClaimNumber_BeforeUpdate(Cancel As Integer)
Dim claimNo As Long
claimNo = DLookup("ClaimNumber" , "MyTable" , "ClaimNumber =" &
Me.ClaimNumber.Value )

If Not IsNull(claimNo) Then
Msgbox "You have entered a duplicate value"
Cancel = True
End If

End Sub
 
Back
Top