| I am trying to create a sequencial invoice number from a starting
point. I am
| running a append query and want to input a starting invoice number
on a form,
| and then the query will advance the invoice number by 1 for each
invoice
| generated. I have it working by using an auto number for the
invoice but I
| need to input the starting number and go from there.
|
| Or is there a better way.
|
| Thanks
If it's strictly a number then you can use an AutoNumber field. These
normally start at 1, but there is a way to set a different starting
number. Access won't let you enter a different number either in form
view or dataset view. It will let you append a different number using
an Append query. Here's the steps you would go though:
1. Create a copy of the table that has your invoice number (We'll call
the original table A and the copy table B). The table will have to be
empty for this to work.
2. Depending on what it when you started, make sure Invoice Number in
table A is AutoNumber and Long Integer in table B.
3. Populate a dummy record in table B with the Invoice Number ONE LESS
than what you want to start with. If you want your invoice numbers to
start at 1000, put in 999.
4. Using an append query, append table B into table A.
5. You can now delete that dummy record in table A. The next record
will automatically be 1000 and so on.
2 words of warning.
1. DO NOT compact the database while table A still empty. Doing so
will reset the 'next record number' to 1.
2. If someone starts a new record and then cancels out of it, the
number is still used up. This will cause a gap in the sequence
numbers.
Also, as Joseph said, you could also do this with VBA. Look at locking
the field, the Before Insert event, and running a query to get the
next number.
Tom Collins
