Trapping a Duplicate Key Error

  • Thread starter Thread starter BK
  • Start date Start date
B

BK

I have a bit of a problem. I have a form that has some 30 different
controls on it. The very first one is JobNumber, which is the primary
key for the table. Now they could enter all the data for a given job
and when they finally get to the end and go on to a new job it could pop
up a duplicate job number entry, which is annoying because they just
spent all that time entering data that had already been entered. I want
it to pop up basically right after they enter the job number. How could
I do something like this.

I've used a DoCmd.RunCommand acCmdSaveRecord in an OnExit event which
does pop the error. This poses a problem because this text box is
generally locked. I just unlock it when data entry is done. After data
entry is done I want them to be able to go to another current job (by
using a combobox that finds the record) but then relock that textbox so
they can't mess with the job number. I relocked it in the form's
AfterInsert event. But since I do a SaveRecord in the box's OnExit, the
AfterInsert event also fires when I leave the textbox and if the person
makes a mistake, they can't go back and edit the job number because it's
locked. Am I making sense? So I guess I really need a way to fire that
dupe PK error without having to save the record. I'm working on
AccessXP in an ADP.

TIA,
Bill
 
Well, if they get the dupe key error, the record should remain unsaved, &
they should be able to correct the value & try to save the record again, no?
They should not have to re-enter the data.

But, it does make sense to trap the duplicate value as soon as they have
entered it. Ty something like this in the BeforeUpdate event of the
JobNumber textbox. (I'm assuming that the value of that field in the
database table is numeric - not text):

if dlookup (1, "TheTableInQuestion", "JobNumber=" & me![JobNumber]) = 1
then
msgbox "duplicate!"
cancel = true
endif

Remeber, however, that they could always leave the job# field blank, fill
out the other fields, then try to save the record. This will hopefully get a
missing required field error at the form level. So you mst *always* be
preparted for form-level errors to occur...

HTH,
TC
 
Back
Top