Notify User of Duplicate Records on Multiple Fields

[Matt_10R via AccessMonster.com]

Hello everyone

I'm a new member, but I've been lurking a while, trying to find an answer. I
have an OK understanding of VBA and I’m pretty good with the rest of Access.
Here's my problem...

I have a form (frmUserEntry) linked to a query (qryUserEntry) that is based
on my table (you quessed it: tblUserEntry) with "Prefix", "Number", "Date",
and "Suffix" set as the primary fields. I setup the table so there can be
duplicate entries in any one field, but there must be a unique combination of
all 4 fields…

A 12345 01/01/08 01
A 12345 01/01/08 02
B 12345 01/02/08 01
B 54321 01/02/08 02
C 54321 01/01/08 01
Etc…

My goal is to circumvent the unfriendly Access duplicate record message,
display a message that alerts the user that their entry is duplicate, clear
the current form of their duplicate entry after they click on OK in the
message box, and finally open the built-in Find dialog so they can search for
the existing record and edit it as needed.

I’ve been playing with Dlookup() in the BeforeUpdate form event and I can get
the process to work exactly the way I want for a single field, but I’m having
trouble applying it to all 4 fields. I’m not set on using Dlookup(),
especially if there’s a more efficient way of doing what I want.

Thanks in advance!

 

[Jeff Boyce]

Matt

First, before anything else, reconsider the name of the field "Date". This
is a reserved word in Access, so when you say "Date" and Access reads
"Date", you two may not be referring to the same thing. Use a more
descriptive fieldname (e.g., StartingDate, Birthdate, ...).

If you are forcing your user to enter values for all the fields before
advising him/her that the combination they entered has already been used,
you will probably have some irritated users! Another approach would be to
let the user select from a combobox/list of all the existing records, and
(you) use the NotInList event to (then) add a new one.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Matt_10R via AccessMonster.com]

Thanks Jeff

I should probably do a little more explaining...

The names I listed are not the actual field names...just simplified for the
point of the discussion.

These 4 text boxes are the only available fields on a parent form. The child
form has 50+ fields and ultimately hits a child table by way of a query. The
parent table has these 4 fields and an AutoNumber (no duplicates and not a
primary key). The AutoNumber is linked on a one-to-many relationship to a
field on the child table (no primary keys). There can be only unique records
(of the 4 fields) on the parent table, but unlimited duplicates on the child
table. This structures the data as I planned.

In my scenario, the user only has to enter the 4 fields before getting a
duplicate record warning. Then, if there is a duplicate, the parent form
clears before a new record is started on the parent table. As a result of
our selection process as well as the nature of what is being tracked, the
chance of a duplicate is very low but still exists.

I would like to keep the fields as text boxes, but I’m flexible. Please
expand on your idea of using a combo box and the NotInList event. I’m not
sure how to arrange everything…4 separate combo boxes or one 4-column combo
box? Also, I’m not entirely sure of the coding to use.

 

[Jeff Boyce]

Matt

If your setup is working, no need to change...

What I discussed would involve a combobox (I stick mine in the header) that
is unbound, but which reads the table to get all current records. In the
AfterUpdate event of the combobox, I requery the main form, which is based
on a query that points to the combobox for a selection criterion (usually
the rowID).

When the form first opens, nothing's in the combobox, so the form displays
no record. When the combobox is used and a record selected, the form
requeries and gets the record associated with the selected record in the
combobox.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Matt_10R via AccessMonster.com]

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.

 

[Jeff Boyce]

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]

I'll give that a try.

Thanks