Generating consecutive invoice number in MS Access VBA, # restarting every year

Question

I need to generate unique invoice numbers for my Access (2010) database. The numbers should be in the format year+sequential number, e.g. 20101447 for the 1447th invoice of 2010. I looked around for a while, but a lot of the Google results suggest using an autonumber and I'm quite certain that's not a very solid way of doing it. (because autonumbers are only guaranteed to be unique, nothing else)

The database isn't going to be used by multiple users at once for now, but I don't feel like going with a totally hacked together solution either.

EDIT I also found this website that discusses sequential numbering using the DMax function. Scenario #2 is exactly what I had in mind, and I think it's good enough for my use case. I'll make sure the user is notified in the rare (for me) event that the database got changed before completely entering and saving a new invoice.

EDIT2 FYI: The numbering scheme isn't a fiscal requirement, but just our custom numbering. I didn't want to change without a good reason to.

Answer 1

If you have the ability to change the database schema:

• Add a sequential number field and a year field.
• To get the invoice number, combine these values appropriately.
• When inserting, you will have to get the current year and then query the database for the maximum sequential number where year = current year.
• Use these values on your insert.

If you can't change the database schema:

• Get the max invoice number where invoice number starts with current year
• Increment the invoice number.
• Use these values on your insert.

EDIT

If you can add another table, have a table that stores the 'max' sequential number for each year. Every insert will lock the table, get the value and then increment it. Think of it as your invoice number generator table.

Answer 2

Solutions complete with sample code

How To Implement Multi-user Custom Counters in DAO 3.5 Ignore the 3.5 version number in this article. Use whatever version of DAO is appropriate to your version of Access.

How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1 The current version of ADO in Windows XP SP3 is 2.8 so ignore the ADO 2.1 part and use ADO 2.8. Although 2.1 will still work.

Also what happens on January 2nd when someone enters an invoice which is supposed to be dated December 31st? And this will happen.