I have order forms with Invoice numbers
The invoice numbers need to skip 200 count every day.
There is a max of 200 orders that can be entered a day.
Mondays starting number is 14400
Tuesdays starting number is 14600 and so on
I have a table that has the starting number for each day. Right now I'm using a DLookup after the user enters in the date to pull up the starting number.
I need this to be more automated and I'm not sure how to write this.
What I need the code to do is find the start number off the table then find the next avalible invoice number to use.
Example: Monday 14400, 14401, 14402
Bascialy look up the last number used on that date and add 1.
Maybe I explained this wrong.
The table itself has an OrderID number which is the key field and is automated. I'm not concerned with this number assignment.
The Invoice number is something the user sees. This is also used to reffrence billing and tracing.
The invoice number is generated depending upon the date the invoice is entered for. The user may enter an invoice today but use a date a week from now. This is why I have another table designating the start invoice number for each day.
I dont want my invoice to count 1 - 10 day to day.
Each day the numbers jump 200 from the previous days start number
14400 first day
14600 second day
14800 third day
But within those days the numbers need to count by 1 14401, 14402, 14403
If the start number for today is 14400 and there is an order entered using that invoice number. The user goes to enter another order the invoice needs to automaticly bring up 14401 as the next invoice number to use. If 14401 is used then it assigns the 14402 invoice number to the order.
The invoice table has 20 years worth of start invoice numbers.
Day a start number= 14401 5 orders 14401, 14402, 14403, 14404, 14405
Day b start number= 14601 3 orders 14601, 14602, 14603
Day c start number= 14801 6 orders 14801, 14802, ETC