Create auto Invoices Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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")
 
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
 
"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.
 
Back
Top