Getting rid of messages in a multiple index setting...

[tunacat]

I have a table that I've set up with multiple indexes in order to eliminate
duplicate fields. However, when the user tries inadvertently adds a
duplicate, there are several message boxes that pop up. I would just like
the one box explaining the mistake. How do I get rid of all the message
boxes? Thanks for your help in advance!

Melanie

 

[Jeff Boyce]

Melanie

Are you saying that one message pops up for each "duplicate"? Or are you
saying that for a single duplicate, multiple messages pop up?

What does the message box(es) say?

One approach to (temporarily!) turning off the pop-up message that informs
the user there is a duplicate index value and it won't be added is to turn
the warnings off. I say TEMPORARILY! because you absolutely want to know if
anything else isn't working correctly, so you could turn warnings off during
the addition.

To do this, you'd probably need to be working in a form, so you could
control the process (you really don't want to expose the users to the tables
directly). Take a look at Access HELP on SetWarnings.

Another approach would be to do some checking behind the scenes on the form
and never even attempt to save the duplicate information. This might be
friendlier than letting the user enter all the info and THEN tell them
they've duplicated something already in the database.

 

[tunacat]

Thanks Jeff! How would I go about your second approach? I think it would be
best to prevent someone from having to type in everything only to be told the
data is already existing. I still need it prevent duplication only if
multiple fields are the same. (ex. if the same Date field and Shift Field
have already been entered, we can't let them put it in again)

 

[Jeff Boyce]

Melanie

A couple ideas come to mind...

You could force completion of the "suspect" controls first by disabling all
the other controls until after the suspects have been completed (and
tested). To test, you'd write a small snippet of code that tries to find
existing records with the values on the form. If it finds any (i.e., 1),
you'd cancel the process and inform the user that what s/he entered already
exists. This is slightly less obnoxious than entering ALL the data.

Another approach would be to use combo boxes for the suspect controls, and
set the LimitToList property to Yes. If the user entered something already
in the database, the control would display it, a visual indication that they
need to use something else. If you use the NotInList event too, you provide
a way to enter a new (not-in-the-list) value.