I would like to prevent duplicate names from being entered into a table, but I have an autonumber field as my primary key.
JP ID: Autonumber (primary key)
I would like to be able to enter 2 John Doe's as long as they live at different addresses, but I would like the system to prevent 2 John Doe's with the same address from being entered into the system. I could do this by ditching the autonumber field and keying the name and address fields together, but I'm sure there is a better way than that.
in the BeforeUpdate event procedure of the form which is doing this file maintenance, use the following code:
If DCount("*","tblCustomers","[FirstName]=me![FirstName] AND [LastName]=me![LastName] AND [Address]=me![Address] AND [City]=me![City])<>0 then
MsgeBox "Sorry, this customer already exists!"
Ideally, you want to index all four of these fields.
Remember, whichever of these fields are string fields, you are going to have to do the following to the criteria:
instead of [FirstName]=me![FirstName]
it will have to be [FirstName]='" & me![FirstName] & "'