Re: Notify User of Duplicate Records on Multiple Fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


Matt

Your original post mentioned that you want your users to be able to call up
an existing record so they can edit it.

The design approach I mentioned should let you do just that. By giving them
a combobox (5 column: ID, plus other four; first column hidden), they'd be
able to select any existing record and "load" it (that's where the
AfterUpdate comes in for the unbound combobox).

If you'd like them to be able to create new records, you can add a command
button that changes the form to allow data entry.

By the way, if you are treating the combined 4 fields as a primary key, then
you need to "migrate" those same four fields to your "child" table to get
the relationship working. A potentially simpler approach would be to make
your Autonumber field in the parent table your primary key, and use a single
(Integer) field in the child table as a foreign key.

You can still set a "no duplicates, unique" index on the combination of the
4 fields, you'd just not be using the combination as your primary key.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Matt_10R via AccessMonster.com" <u47131@uwe> wrote in message
news:8cd954658233f@xxxxxx
The setup is working and it's not. It is preventing duplicate
combinations
of the 4 fields. However, if a duplicate combination is entered, the user
receives the standard duplicate record warning, then the user will try to
delete the entries from the 4 text boxes and and receives a warning to the
effect that the record cannot be saved. If the user clicks on the button
to
drop the data from the record and proceed, the warning for duplicate
record
comes up again and the process loops (because I do not allow the users to
delete records from a form). Everything works as expected unless a
duplicate
combination is entered in the 4 fields. The end result is that the user
has
to shut down Access and start it again (and usually ends up calling me).

I'm trying to take a proactive step to prevent this from occurring and
redirect the user before a record is created (thus, before warnings come
up).
I've read a lot of threads on using Dlookup() to check for a match in a
single field before the record is opened and redirecting the user before
Access has a chance to identify duplicate information, BUT I haven't found
any details on applying this to multiple fields on a form.

--
Message posted via http://www.accessmonster.com



.

Quantcast