Allocate a number upon saving form?

[CW]

I have an invoice form on which I want the next number to be allocated (using
DMax on the InvNo in tblInvoices) but only upon saving the form.
At the moment it is allocated upon opening the form but that's causing a
problem if the user aborts for any reason - it still uses up a number.
I want it to be assigned only if and when the form is saved, rather than
when it is simply closed.
Many thanks
CW

 

[ruralguy via AccessMonster.com]

You could have some problems in a multi-user environment but use the
BeforeUpdate event of the form inside of a Me.NewRecord test to assign the
value.

 

[Linq Adams via AccessMonster.com]

As Allan indicated, in a multi-user environment it's best to generate the
number as near as possible to actually saving the record, but exactly how
does generating it at the beginning "waste" the number if the record is
aborted? If the record is aborted, which most people would take to mean not
saved, DMax() will return the same number again!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[CW]

Ah - I just double-checked my form - actually I've got the code in Before
Insert, which I guess is the cause of my problem, is it not? Stoopid. I'll
move it to AfterUpdate and see what happens then.
Thanks for your advice
CW

 

[CW]

Ah - I just double-checked my form - actually I've got the code in Before
Insert, which I guess is the cause of my problem, is it not? Stoopid. I'll
move it to AfterUpdate and see what happens then.
Thanks for your advice
CW

 

[ruralguy via AccessMonster.com]

You did not answer Linq's question and you want your code in the BeforeUpdate
event of the form and not the AfterUpdate event of anything.

Ah - I just double-checked my form - actually I've got the code in Before
Insert, which I guess is the cause of my problem, is it not? Stoopid. I'll
move it to AfterUpdate and see what happens then.
Thanks for your advice
CW

You could have some problems in a multi-user environment but use the
BeforeUpdate event of the form inside of a Me.NewRecord test to assign the

[quoted text clipped - 8 lines]