Duplicate Records

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

I have a form that users will enter infomation into my tblAttendance. The
info they are adding is "EmpID", "DateAbsent", and "AbsentType". How can I
set this up so that if these 3 fields match an existing record that the user
gets prompted that the record already exists? And if it does exist just undo
the record they are trying to enter.
 
Easist way is to join them into a key field with no duplicates. Then Access
will prevent it and provide feedback for you.
 
Put the table in design view and select indexes. Under index name enter
whatever you want to call the combined fields such as KeyUniqueTimeOff. In
the field name column, one on each row enter your EmpID, DateAbsent, and
AbsentType. You will not have the index name to the left of all rows, just
the first one. The rows in field name without a index name to the left fall
under the first field index name. Something like below. They will sort as
one field with first row field highest sort order and so on down the rows.
While then put cursor on first row KeyUniqueTimeOff and in the boxes at the
bottom of the set unique to yes and ignore nulls to no (must have entry).
Don't know if you want this to be the primary key for the table so for now
just leave it no. Now if you save and try and enter duplicate access will
tell you record already exists.

Index Name FieldName Sort Order
KeyUniqueTimeOff EmpID Ascending
DateAbsent Ascending
AbsentType Ascending
 
Got it Pete. Now how do I create a user friendly message if there is a
duplicate record when they try and save the record? Right now it gives me the
standard message:

"The changes you requested to the table were not successful because
they would create duplicate values in the index, primary key, or
relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again."
 
Use the entry form's on error event to intercept the error and generate
your own error message and routine to handle the error.

You will have to determine the error number and handle it. Sorry, but I
can give you the code at this time.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Hi,
Just a suggestion for the future. I like many others I come and go from
group when I'm not busy. Once someone gives you a solution to your original
question folks may stop watching the thread. Rather than come back and ask
new question in same thread you may consider asking new question in new
thread. Using this one for instance, couple of thoughts. Have your folks
entered other info, it would be cruel to have them fill out a form full and
then tell them sorry, duplicate key. In this case you may want to check and
see when they answer the three questions of ID, absent, type is there
already a record like this one before they answer all the other questions.
A quick automated search will save them time and frustration. See
http://support.microsoft.com/kb/102527 or
http://www.databasedev.co.uk/duplicates.html for part of the solution but
you would have to combine your three fields and then check/trap the problem.
These are only a couple of solutions, search google for many more. Now if
this is the only info in the record then as John Spencer states you would
just trap the error and raise your own error mesage. Try a Google on
trapping errors and post back with questions. Pete

..
 
Back
Top