Incrementing PK, avoid duplicates

[BruceM]

Is it possible to use code to assign an incrementing primary key value (that
is to say, the PK value is one greater than the previous saved record) in a
multi-user environment in such a way that the value will be displayed on a
form when the user begins the record, but will be reassigned if another user
starts a new record at the same time? I am creating a PK along the lines of
RPT 05-01, RPT 05-02, etc. as a text field. No problem there, except that if
I assign the value in the form's Current event (If Me.NewRecord Then ...)
there could be a duplicate PK error (3022) in a multi-user environment. I
suppose I could save the record as soon as it is started, thus locking in the
PK, but I had hoped to use error handling to go back and get a new PK in the
event of duplication. For the (possible) convenience of anyone replying to
this question I will say the number is assigned through a called procedure
named AssignNumber.

 

[Graham Mandeno]

Hi Bruce

You should be able to trap the error in Form_Error and generate a new
number.

Alternatively, you could do a lookup on the table in Form_BeforeUpdate to
see if the number has been used in the meantime.

 

[BruceM]

Thanks for the reply. I couldn't figure out how to trap the error in
Form_Error. The error showed up at command buttons that included code for
saving the record. However, I did contrive a solution, whereby I assigned
the number in the form's Current event (if it is a new record), copied it to
an unbound text box, then cleared the text box bound to the PK field. In the
form's Before Update event I assign the number (the field is still empty),
and compare it to the number in the unbound text box. If they are different,
the user is notified via a message box. I think I see what you mean by the
lookup in Before Update, which I guess does sort of what I did, but by a
different route.

Hi Bruce

You should be able to trap the error in Form_Error and generate a new
number.

Alternatively, you could do a lookup on the table in Form_BeforeUpdate to
see if the number has been used in the meantime.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Is it possible to use code to assign an incrementing primary key value
(that
is to say, the PK value is one greater than the previous saved record) in
a
multi-user environment in such a way that the value will be displayed on a
form when the user begins the record, but will be reassigned if another
user
starts a new record at the same time? I am creating a PK along the lines
of
RPT 05-01, RPT 05-02, etc. as a text field. No problem there, except that
if
I assign the value in the form's Current event (If Me.NewRecord Then ...)
there could be a duplicate PK error (3022) in a multi-user environment. I
suppose I could save the record as soon as it is started, thus locking in
the
PK, but I had hoped to use error handling to go back and get a new PK in
the
event of duplication. For the (possible) convenience of anyone replying
to
this question I will say the number is assigned through a called procedure
named AssignNumber.