Duplicate Record Error Checking

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

How can I trap and write code to check for duplicate
records during a form refresh? If a record with the same
ID already exists in the underlying table I need to check
for this and then have some code to force entry of a
unique key value.
 
In the BeforeUpdate event of the primary key control try something like the
following:

Private Sub Custid_BeforeUpdate(Cancel As Integer)
if not isnull(dlookup("Custid","myTable","Custid=" & me.Custid)) then
msgbox "Record Already exists!"
cancel=true
endif

End Sub


The Dlookup will return a null if there is no match so you are looking
for a Not null condition to indicate a duplicate. If any of your
fields are text you will have to wrap the value in Quotes so that the
value is passed to the Dlookup as a literal string. There are many
posts in this newsgroup that describe how to do this if you aren't
sure - or post again and I'll give you the syntax.
 
Back
Top