Create auto Invoices Numbers

A

Alvin

On my customers form I have a comand button that opens another form for the
orders. how can I auto creat invoice numbers for each order? I tried seting
the default on the [table.invoice#] to 1000+1 and that works if I only place
1 order per customer. I think it would be better in Vb code but I don't know
how to write the code. Can someone please help?
 
B

Brian

Do you want each customer to have his own sequence starting with 1000, or do
you want the numbers pulled from a common pool? Assuming the latter:

Just make the InvoiceNumber field an auto-number field.

If you can't live with it starting at 1 and going up, then you can hide this
field and have another one (the visible one) that is set to the auto-number
field + 1000 at the time the record is created. Or you can even just append
999 dummy records into the table and then delete them without doing a
compact/repair before the first legitimate invoice (since an auto-number
field only gets reset to 1 when a compact/repair is performed when the table
has no records). I know there is also a way to specify the starting point of
an auto-number sequence using Seed, but I don't think it is a native Access
command.

Or, you can use =DMax("[InvoiceNumber]","[Invoices]")+1 as the default
value for your invoice number field.

With method #2, if two users open a new record at the same time, both get
the same InvoiceNumber, and whoever saves his record first gets to keep the
number; the other user will get a duplicate key error.

Auto-number is the only easy way to guarantee that two users do not create
the same number simultaneously.
 
M

Marshall Barton

Alvin said:
On my customers form I have a comand button that opens another form for the
orders. how can I auto creat invoice numbers for each order? I tried seting
the default on the [table.invoice#] to 1000+1 and that works if I only place
1 order per customer. I think it would be better in Vb code but I don't know
how to write the code. Can someone please help?
Click to expand...


The 99.99% safe way to do this is to set the number in the
form's BeforeUpdate procedure. Use code something like:
Me.InvNo = Dmax("InvNo", "InvTable")
 
D

DubboPete

Marshall Barton said:
The 99.99% safe way to do this is to set the number in the
form's BeforeUpdate procedure. Use code something like:
Me.InvNo = Dmax("InvNo", "InvTable")
Click to expand...

Shouldn't the next invoice number therefore be

Me.InvNo = Dmax("InvNo", "InvTable") + 1

?

Pete
 
M

Marshall Barton

"Marshall Barton" wrote
DubboPete said:
Shouldn't the next invoice number therefore be

Me.InvNo = Dmax("InvNo", "InvTable") + 1

?
Click to expand...


Yes it should, thanks for pointing it out.
 
Top