Question:
I am writing a program that needs to keep a running total of ID numbers allocated for this reason. The ID numbers are in an auto-increment field. I need a mechanism to keep a count of numbers assigned so that it may be incremented so that the next ID number can be displayed prior to the record being posted. Any ideas?
Answer:
Sure. Don’t use an auto-increment field. I’ve found that auto-increment fields are really useful for storing free-form information that you want keyed, without having to use the data itself as a key. However, with things like invoice numbers, sales order numbers and the like, auto-increment can be dangerous.
The way I approach this is to use a separate table that has a single integer field, and at any time will have only one record. The field holds batch number of some sort. To get the current number it’s just a matter of opening the table and getting the current value of the field. To increment, I merely grab the current value and add 1 to it.
While on the surface, it may appear that this method may introduce some complexities to your program by virtue of the added table and access code, it is far simpler and much more efficient than trying to get the max value from an auto-increment field.
