Avoiding duplicate records

[access345]

When entering new records into my database I am trying to avoid duplicates. This is the problem I am having. I have a field code name, another field serial number. A final field unique serial number in which I combine the code name with the serial number with a query. The problem is with this query. I am trying to execute this query on the form that is entering the code name and the serial number information on it. When I try to execute the query, the query says I have no records. If I close the form and check the table. The record is in the table without the field unique serial number. Please help

 

[PHV]

Why storing calculated/derived values ?
To avoid duplicates, create an unique composite index on (code name, serial number)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[access345]

In my situation this won't work because I have 5 different codes and all the codes start at 100 so there would be duplicates against the different codes. I only wanted to use one form to enter the serial number information. I could create 5 different tables with each code having it's own table. But how would I be able to structure the form so the information would go to the right table?

 

[Zion7]

maybe on beforeUpdate of serialnumber, see if combo exists.

varSerNumCodName = DLooKUp("SerialNumber","tblCode", _
"pkSerCode =" & SerialNumber & CodeName)

If Not IsNuLl(carSerNumCodName) Then
MsgBox "You've created a duplicate Key", _
vbOK,"Yo, Wuz Up Wi' Dat?"
Cancel = true
Else
pkID = SerialNumber & CodeName 'this on AfterUpdate
End If