How can I simulate AutoNumber

  • Thread starter Thread starter Larry Hodges
  • Start date Start date
L

Larry Hodges

I have an invoice entry system. When I generate a new record (Invoice), I
want it to also generate the Inv number, which would be the highest existing
Invoice number + 1. Also, that may not be the last record in the set
either, so it would have to sort the recordset, then add 1 to the highest
existing number.

I will give the user the option to manually change if also (which is why I'm
not using AutoNumber). So, in addition, it needs to check to see if that
number is already in use once the field is changed, prior to updating the
record.

Any input would be greatly appreciated.

-Larry
 
You can use the DMax function.
NewInvoiceNum = DMax("[InvoiceNum]","MyTable") + 1

If the InvoiceNum field has a unique key, Access will automatically
prevent you from saving a duplicate. This is the preferred method.
Otherwise, you can use DLookup to find the existence of a duplicate.

HTH,
Barry
 
Barry Gilbert said:
You can use the DMax function.
NewInvoiceNum = DMax("[InvoiceNum]","MyTable") + 1

If the InvoiceNum field has a unique key, Access will automatically
prevent you from saving a duplicate. This is the preferred method.
Otherwise, you can use DLookup to find the existence of a duplicate.

HTH,
Barry
Click to expand...

Thanks Barry...worked great. I really appreciate the help!

-Larry
 
Back
Top