Force a check for duplicate keys/ indexes before a record save

[Mike S]

Good morning,

My question involves forcing a check for duplicates before a record is saved
in Access '03. The issue is that people are entering information through a
form into a record (20 fields or so), and when the record saves, the
'duplicate index/ primary key' warning appears. The operation is sensitive to
time, so it is very important to eliminate the wasted time. What I have tried
so far is running a ' save record' macro through the 'on update' event for
the field, but I also have 3 fields that are required, and the validation
rules and form update macros for those fields stop the save event. I also
tried a DLookup script, but I couldn't get it to work. (My Visual Basic
skills are not that strong to begin with, so I don't understand how to write
the script correctly if it could work).

What I am trying to accomplish is to force a check for duplicates on a field
update from a new record without a save event, or to ignore the form update
macros and validation rules for the other required fields when saved as part
of the field update event.

Thanks in advance for your advice,
Mike

 

[Jeff Boyce]

Mike

I don't know whether your situation is amenable to this approach ...

Rather than force data entry and THEN check for duplicates, can you provide
the users a way of trying to look up existing records and helping them add a
new one if an old one is not found?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mike S]

Jeff,

I could look into something like that. If there was a procedure where I
could run an event procedure that would prompt for the field data and do a
find record command, that might be a solution.

thanks for hte tip,
Mike