before update on saving record

[Guest]

I am working on an A2k3 db put together "quickly" by another person and now
have a situation to work on. On a form there is a drop list that the user
selects a prefix letter for the file number. Once the user selects the
appropriate letter the textbox next to it fills with the appropriate
sequential number for that prefix letter. This all works fine from the after
update event of the drop down list. What the situation is that userA could
be working on adding a record and userB begins to work on adding a record.
Both selected the same prefix letter. Since the record for either has not
been saved, they both will show the same sequential number in the textbox.
Whichever one finishes first will get the number and the other person will
get an error message. In the before update of the form I would like to put
some type of coding in there so when the second user goes to save, add
another record, or exit will get a message indicating that the number in the
textbox is already used or allow the next number to be generated.
I am a bit lost on how to go about the process. I thought about using the
"dirty" method but not sure how to construct it properly.
I seek help on this dilemma.
Thanks for any assistance on this.

 

[Marshall Barton]

I am working on an A2k3 db put together "quickly" by another person and now
have a situation to work on. On a form there is a drop list that the user
selects a prefix letter for the file number. Once the user selects the
appropriate letter the textbox next to it fills with the appropriate
sequential number for that prefix letter. This all works fine from the after
update event of the drop down list. What the situation is that userA could
be working on adding a record and userB begins to work on adding a record.
Both selected the same prefix letter. Since the record for either has not
been saved, they both will show the same sequential number in the textbox.
Whichever one finishes first will get the number and the other person will
get an error message. In the before update of the form I would like to put
some type of coding in there so when the second user goes to save, add
another record, or exit will get a message indicating that the number in the
textbox is already used or allow the next number to be generated.
I am a bit lost on how to go about the process. I thought about using the
"dirty" method but not sure how to construct it properly.

The common way to deal with this situation is to move the
code from the combo box's AfterUpdate event to the form's
BeforeUpdate event where the chance of two users colliding
is vanishingly small.

If it's imperitive that users see the number while adding a
bew record, then use DLookup to check if a record already
uses the number and, if so, change it using the same code as
you have for the combo box.