detect duplicate records

[Shawny]

How do I notify the user of a duplicate record being added?
For a record with a value in an [IDfield], and a value in the [Datefield] - don't let another record be added with these same values in these 2 fields.
In other words: Don't let the user add 2 records for somebody with the same date.
Thanks

 

[Rob.Mills]

What you need to do is setup an index for both of these fields and mark it unique. Then it won't accept records where the combination of these fields exists already.

 

[Shawny]

After the user updates the second field, I am gonna put a save record macro. I would like to be able to show the user a message box explaining what is going on, instead of Access's msbox's.

 

[Rob.Mills]

Good idea.

 

[Shawny]

I would like to learn how to detect if a record is a duplicate (useing one field or more), and be able to give the user a message box explaining the situation.
How do I do that?

 

[Rob.Mills]

I've done something similar to this for just one field using a DLookUp. Try this:

If Not IsNull(DLookUp("[PrimaryKeyOfRecord]","YourTable", "[IDField]=Forms!frmName!IDField AND [DateField]=Forms!frmName!DateField")) then

msgbox "A duplicate record already exists."

End if

The syntax might need some adjusting but I've made this work for me before.