Don't save if record exists

[Mister-B]

Hi there,

I use a pretty substantial form (frmDaten) to enter data to a table. I wuld like to build some type of data checker into the form while data is being added to see if a new record being added already exists. If Fields 1 to 4 match up with an existing record, I would like a message to pop-up informing the user that the record already exists. I would also like for the new record not to be saved and the icing on the cake would be for Access to jump to the already existing record. Can anybody help?

Kind regards,
Martin

[June7]

Could have code associated with each of those 4 controls AfterUpdate event that looks for the combination of values in table. Use DLookup() domain aggregate expression and If Then Else conditional structures.

Hard to be specific without knowing your db.

[isladogs]

Create a unique index for the 4 fields so that duplication is not possible.
Access will then block any duplicates and popup a message indicating where the data entered would result in duplication.
If you don't like the standard Access message, you can create your own to replace it.

You could then ask the user if they want to view the original record.
If so, use a DLookup function to get the PK value for the record matching the four values in the index and open the form to that record.