Geek Answers Handbook

Creating a sequential account number in MS Access VBA, # restart every year

I need to create unique account numbers for my Access database (2010). The numbers should be in the format year + serial number, for example. 20101447 for the 1447th invoice of 2010. I looked around for a while, but many of Google’s results suggest using auto-type, and I’m pretty sure that this is not a very reliable way to do this. (because autonumbers are only guaranteed to be unique, nothing more)

Currently, the database will not be used by several users at the same time, but I do not think that you are going with a completely hacked solution.

EDIT I also found this website which discusses consecutive numbering using the DMax function. Scenario # 2 is exactly what I had in mind, and I think it is good enough for my use case. I will ensure that the user is notified in a rare (for me) event that the database has been changed before fully entering and saving a new account.

EDIT2 FYI: The numbering scheme is not a tax requirement, but simply our custom numbering. I did not want to change without a good reason.

+4
source share
2 answers

If you have the opportunity to change the database schema:

  • Add a sequential number field and year.
  • To get the invoice number, combine these values ​​accordingly.
  • When pasting, you will need to get in the current year, and then query the database for the maximum sequential number in which year = current year.
  • Use these values ​​in your tab.

If you cannot change the database schema:

  • Get the invoice number where the invoice number starts from the current year
  • Increase the invoice number.
  • Use these values ​​in your tab.

EDIT

If you can add another table, indicate the table that stores the serial number "max" for each year. Each insert locks the table, gets the value, and then increments it. Think of it like an account number generator table.

+3
source

Sample Code Solutions

How to implement multi-user custom counters in DAO 3.5 Ignore the version number 3.5 in this article. Use any version of DAO that matches your Access version.

How to implement multi-user user 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.

And what happens on January 2 when someone enters an invoice that should be dated December 31? And that will happen.

+2
source

More articles:


All Articles