Can't save record

[Junior]

Just created a multiple index on two fields in my table and set it to
unique values - in order to eliminate duplicates in the table... this works
fine - sort of...

Now- In my entry form - when i try to enter duplicate info for these felds
i get an error
'changes request were not successful, blah, blah, either change the data or
redefine the index'

but the message doesn't alert until i have entered alot of data and try to
leave the record
and the only way i can get out of this is to close the program so the record
is not saved---
is there another way to handle restricting duplicate entries?
perhaps in VBA in - can someone geve me some hints? or some sample air code?

The two fields i have set a multiple index on are SSN and AppDate so that
there cannot be
a duplicate ssn and AppDate pair.

Thanks

 

[Rick Brandt]

Just created a multiple index on two fields in my table and set it to
unique values - in order to eliminate duplicates in the table... this works
fine - sort of...

Now- In my entry form - when i try to enter duplicate info for these felds
i get an error
'changes request were not successful, blah, blah, either change the data or
redefine the index'

but the message doesn't alert until i have entered alot of data and try to
leave the record
and the only way i can get out of this is to close the program so the record
is not saved---
is there another way to handle restricting duplicate entries?
perhaps in VBA in - can someone geve me some hints? or some sample air code?

The two fields i have set a multiple index on are SSN and AppDate so that
there cannot be
a duplicate ssn and AppDate pair.

You could do a DLookup() in the BeforeUpdate event of both controls on the
form searching for entries in the table with the same values. Then pop up a
MsgBox and set Cancel = True when you find a match.

 

[Junior]

Rick- thanks - that gives me some ideas- unfortunately the form controls are
on separate tabbed pages - but maybe i could do a msgbox on the 1st
control(SSN) alerting the user of a possible duplicate problem - and then
run a delete query to delete the duplicate record if the second control is
filled with a duplicate (AppDate).
thanks for the tip..

 

[Rick Brandt]

Rick- thanks - that gives me some ideas- unfortunately the form controls are
on separate tabbed pages - but maybe i could do a msgbox on the 1st
control(SSN) alerting the user of a possible duplicate problem - and then
run a delete query to delete the duplicate record if the second control is
filled with a duplicate (AppDate).
thanks for the tip..

The location of the controls on TabPages (separate or otherwise) should
make no difference. You would have to run the test in the BeforeUpdate of
both controls because you cannot be sure which one might have an entry made
in last. The "usual" sequence might mean that a particular one is edited
last, but you can't discount the possibility that the user might change the
entry on the first one after having made an entry in the second. If that
change were to result in a duplicate, then your test needs to be run and
prevent it.