Often we find ourselves in situations where we want to have some custom autonumber-like functionality, but getting it isn't as easy as you might think. You shouldn't use an actual Autonumber field, as per best practices they shouldn't ever be seen by the user, and DMax has it's problems as well...

One of the issues with using DMax in a situation like this arises in multi-user situations where the next number is obtained at the start of the record. If person A starts a record, and person B starts a record before person A gets around to saving theirs, then there's going to be some conflict in what you need to be a unique number. Also, DMax, being an aggregate function, will be a bit on the slow side when you start having many many records as each call to it will have to look at all of those records to get the highest one.


This code works by storing the next available number in a table which contains a single field and a single record. There is a seperate table for each incrementing requirement. When a new number is needed, this table is opened, locked, and the number is incremented, saved to that table, and then the table is closed and unlocked and the number is returned by the function.

There is no form interface for this sample. There are four tables (tblANumMsgs, tblANumQuotes, tblMessages and tblQuotes) and three modules (modELookup, modSleep, and basAutoNumbers). To demonstrate the functionality call the GetAutoNum() function from the immediate window.

Format 2000 for download. Created and testing in 2003, though it should work in any version. This only works on linked access tables! Extra verifications and locking methods will be required for other data sources.

Cheers,

Edit: 2010/10/12
Uploaded instructions

Instruction Supplements:
1) If you tables contain spaces you must wrap all table names in the code with square brackets to avoid a table not found error.

This post has been edited by jleach: Apr 17 2012, 05:13 AM

Jack,

Great addition to the archive. However, I am encountering a problem with this form of the call: ? getautonum(dsAutoNumQuote ,True) which errors out in A2010.

Glenn

Hello Glenn (and Jack)

The error occurrs because fldID in tblQuotes is defined as a TEXT data type, and thus is raising an error in ELookup() ... it should be Number/Long.

I am going to be a devils advocate to some extent due to your comment:

"You shouldn't use an actual Autonumber field, as per best practices they shouldn't ever be seen by the user"

That is a very debatable subject, and a detailed discussion is probably best left for another thread, and I beleive it to be so debatable that a consenses would not be reached in support of your claim. I personally have shown the user the Autonumber for some primary table in almost every app I develop. I consider it a "best practice" to consider what is best for the application's scope and purpose as well as how a replacement of an Autonumber will differ from the one Access (Jet/ACE) already provides you. The code you present is basically an "exposed code" Autonumber ... one that falls victim to all the same arguments on both sides of the fence regarding showing or hiding autonumbers. In short, you are implementing a lot of code as well as db objects in order to duplicate a functionality that comes standard with Access (Jet/ACE). In doing that, the process of uniquely identifying each record gets "closer" to the user, and thus increased your chances of your data becoming "unclean".

Thanks Brent,

I didn't have time to track it down. Until Jack has time to make the correction, anyone downloading the demo should note the correction and update accordingly.

Your comment about the 'autonumber issue' is well-taken. It seems to be a topic that is almost as contentious as the discussions of real vs surrogate keys. Consensus is indeed unlikely.

That said, I think that Jack's demo still has merit and could be useful for people who want to generate unique number series for control documents, such as invoices and purchase orders where continuity and integrity of the sequence can be important. Autonumbers can't be counted on to provide that assurance.

Glenn

The original attachment has been replaced with a version that doesn't require user input to discern the datatype.

Brent - interesting argument, and I can definately see where you're coming from with it. Probably I shouldn't have used that particular wording in the original post as it is debatable, so I apologize for that. One of the driving factors for me in doing this was those cases where an autonumber can start returning negative numbers... I can't give a customer an invoice number of -487796, and this function would keep that from happening. The rest of your points on the matter are very well taken though.

Cheers,

First and foremost --- the code presented definately has merit and is an excellent submission to the Code Archive. I personally find the technique presented to be superior to the DMax() method (IMG:style_emoticons/default/thumbup.gif) I wanted to state this positive message as my previous post may have come across as negating the quality or worth of the submission, which was not my intent, but may likely have been the result of the text I used to have a dialog on the topic of autonumbers (IMG:style_emoticons/default/dazed.gif) .

One benificial aspect of code generated autonumbers that I failed to mention, plus supports the use of them when the application needs them, is the fact that the developer can choose when to get the next number (something the internal autonumber does not allow), thus supporting the concept continutity that Glenn talked about.

PS>
"Negative invoice numbers" --- I have been known to show my Autonumbers in HEX format (IMG:style_emoticons/default/smirk.gif)

Personally, the biggest issue for me WRT showing autonumbers to end users is that they will then add meanings that should never have been added to the autonumbers. By and by, I'll have to field questions like "why aren't those sequential?", "where is XXX now?", "We have 3129 invoices but there's only 3043 invoices. Where's the rest?". Yes, I can train and educate the users but why? Doesn't it really mean that we're costing the client more money for fluff and why should they be paying for fluff? Even if I were just an employee sharing the product with other employees, it still costs the company some money in terms of lost payroll hours to train & educate the users when I can entirely sidestep the whole issue by not showing it to start with.

Some business insists on some kind of code such as sequential numbering appended to a YYYY-MM notation or whatever. I generally tend to regard this as a holdover from existing systems such as filing cabinets where such thing was necessary to keep it well-organized. With an electronic database system, we have a query engine so we can do away with the manual code. However, even so, there may be other requirements that makes the code necessary and in such scenarios, it really does makes sense to roll out our custom auto-numbering to follow along with the business rules governing the structure of code. I should point out that it's plausible that we can save the added complexity by just using normal autonumber as part of the code (e.g. YYYY-MM-XXX being a calculated field of say, Year([InvoiceDate]) & "-" & Month([InvoiceDate]) & "-" & [InvoiceID] ) but I would haphazard a guess that it's rare because non-sequential numbering tends to be intolerable even if it was initially understood to not be sequential.

Glad to see a great submission, and I agree that using a table (which is very similar to a Oracle or PostgresSQL sequence) to generate the number can be much more superior compared to DMax().

Well .. I debated as to whether to reply as I think we are bordering on hijacking the benificial Code Archive submission ... (IMG:style_emoticons/default/smirk.gif) ... But if someone is considering using a code generated number, its important to make that decision with as much info as possible ... so here goes:

>> I'll have to field questions like "why aren't those sequential?", "where is XXX now?", "We have 3129 invoices but there's only 3043 invoices. Where's the rest?". Yes, I can train and educate the users but why? <<

Are you saying that training is NOT needed if the values of a code generated identifying value is used? That is not the case at all, code generated values require just as much explanation as inherent Autonumber fields! With code generated id's your efforts of training go into explaining why no deletions, and possibly no cancellations, are allowed and that the value generated is sequential AS WELL AS contiguous, and WHY it has to be contiguous.

Possible question series between the two camps (BR's questions):
Q: Are those numbers sequential? ...
A1 (datAdrenaline camp): Yes, they are, but there may be gaps in the sequence due to canceled input or deleted records.
A2 (BR camp): Yes, they are, the number is assigned after you save the record {of course this depends are your code, but for this sample, this would be the case}, plus to keep the data values contiguous, deletions are not allowed for <entity name>. But records can be marked as "hidden" or "deleted". Hidden records can only be retreived by administrative tools and/or permissions.

Q: Where is XXX now?
A1: If a record is not found with the value you seek, then the record has been deleted or the value was used for a record that was canceled during input.
A2: If a record is not found with the value you seek, then the record has been hidden from the interface and is only accessible through the administrative tools.

Q: We have [a max invoice id of] 3129 invoices but there's only 3043 invoices. Where's the rest?
A1: Invoices are assigned a value that is unique upon invoice start, once assigned, it will not be reused. If a user cancels a new invoice, the number it was issued will not be re-issued to another invoice. In addition, if an invoice is deleted, the id of the deleted invoice is not re-used. Those scenarios create the gaps in series of invoice id's.
A2: The UI only shows 3043 invoices because that is the amount that are valid to be seen. All the other ids are hidden due to conditions of that invoice (ie: the input was canceled or the invoice was marked as invalid or 'deleted')

----

The moral of the story is that there is always training involved for every system that is deployed and the claim that money is lost by showing an ACE/Jet generated autonumber is -- well -- no so realistic. An Autonumber field is a unique identifier (incremented sequentially or randomly), nothing more, nothing less. If you add more meaning to it, or add specificification to it (ie: data must be contiguous) then you are going beyond its scope. But, if the scope of a system generated visible identifier does not go beyond that of the inherent Jet/ACE Autonumber, then I see no reason to avoid using an Autonumber for that purpose.

My definition for my visible autoids in my systems:

A system generated number used to uniquely identify the <entity title>. These numbers are sequentially generated, but the data is not garenteed to be contiguous because of canceled input or deleted records.

----

I would hate to think of a people management database that does not display a system generated number for each unique individual.

Jack,

Possible improvement ... Use ONE table to track you seed values ...

tblSeeds
--------
SeedId (Autonumber, Pk)
SeedName (Text, indexed uniquely) {values would be the Table Name}
SeedValue (Number/Long)

Use record locking (pessimistic) when doing your incrementing and such.

By using this method, you can then use tblSeeds as well as your code for other types of incremental needs as well. Plus you would not have an "explosion" of table objects to manage.

Actually, no. I said no training is necessary when any numbering system is NOT used at all. Search and find records by using person's name, company's name, invoice date or title, IMHO, requires less training and explanation. Users does not have to know there's numbers being generated under the hood nor should they care. As long the RDBMS can find it, everything is good.

I then went to discuss the fact that some business will insist on some kind of numbering system even though I do feel that it should not be necessary. As an analogy, we don't search for website number 183302714901 to find www.UtterAccess.com - we just type "UtterAccess" in our favorite search engine and boom, it's there. It also (in my mind, at least) makes more sense to search for a invoice that was placed on 9/3/2010 than "2nd invoice created for the month of September", and even if the customer can't remember the exact date but only that it was 2nd invoice placed in the month of September, we can search for any invoices on September, 2010 and see that 9/3/2010 invoice is the one that the customer is looking for. In other words, it's almost inevitable that people talk about data in terms of reference other than autogenerated IDs. But back to the point - some business will nonetheless insist on a numbering system, and as long we agree on some conventions (e.g. assign a number only at the save time, not as soon as it's dirtied, and prohibit deletions), sequential numbering should work well when properly implemented.

I hope this clears thing up.

Hi Brent. I intentionally set this up on seperate tables to keep table traffic to a minimum and reduce possible conflicts. During some import operations a table may be accessed repeatedly and seperate tables would greatly reduce the risk of more than one person trying to access a given table at a time.

I'll add some comments on the debate when I have time... I've got a few opinions as well that I'd like to get out there but I'm on the run at the moment.

Cheers,


edit:
I hadn't been familiar with methods to enfore record locking vs. page locking when this was originally written. I'll look into that more.

I've never heard of any company that would rather have their people search for an invoice by date or customer of vendor or when if it was placed second in a month. ALL correspondence is done through a generated ID number that is used back and forth between companies. If I were to tell my customers and vendors "I'm going to give you invoices and quotes purchase orders and certifications that don't have ID on them anymore, and nothing to discern them from any other one you may have except the date", I probably wouldn't be doing business for much longer. Yes, the RDMS may have some number buried away in a system, but in a real-life practicle situation, these numbers are import links between companies. A Primary Key, if you will, and you almost seem to be suggesting that we should be basing this key off a date - which is very.... unlike what I have come to know. I understand that in the RDMS there will be an actual key as well, but one can think of correlating companies as related tables in a much larger database... they need keys too.

There is also a debate about what users may think when there are gaps or the numbering system has a higher number than actual amount of invoices, etc. From my point of view, this doesn't matter. I never said there wouldn't be gaps... I fully expect there to be gaps, just like there may be with an actual Autonumber. There will be gaps in the sequence because these numbers, both Access Autonumbers and the Custom Autonumbers, would be generated at the insert of a new record, not at the save. When creating a purchase order, the employee needs to reference a PO number BEFORE the PO is saved... this saves time and work on the employee end. Of course the number should be available to them at the start of a new record - to do anything else is counterproductive. There WILL be gaps, but it's easy enough to say to the users "yes, there will be gaps, we know... if PO 3067 doesn't exist the PO 3067 doesn't exist, bottom line - back to work". What this custom autonumber function DOES do that a conventional autonumber CAN'T do is generate an unsigned guaranteed unique integer. That is the only purpose of it, and is something that there is no way to do in access without a custom implementation to do so. Sequencing, at bottom level, means nothing. I can give my customer Invoice #456 followed by #456789 followed by #2 and nobody cares, as long as they're unique coming from us and there's something there for both companies to reference. The functionality of the customer autonumber just so happens to be set up to attempt to maintain a sequence where possible, but that was never the core intention.

Autnumber:
-Guaranteed unique signed long integer value

My Custom Autonumber:
-Guaranteed unique unsigned (positive) long integer value


As far as paperwork numbering, again I'm having a really hard time swallowing the fact that companies should be able to do this without a unique numbering system (not necessarily sequential, but unique for each document type). I have hundreds of pieces of paperwork coming in and out per day, and 99% of them have and require an ID. This is standard bottom level ISO tracking design requirements...

At this point the debate can quite easily be shifted to something close to natural vs. surrogate keys. Does the RDMS have it's unique ID for linking? Yes... Do the business requirements define that an ID is needed for this particular type of document? Yes... do we use one key for the RDMS and a seperate one for the real-world application? No. etc. etc.

Cheers! (IMG:style_emoticons/default/thumbup.gif)

Hmmm. In the physical world, an accounting system that tolerates gaps in control number sequences is suspect and an open invitation to employee fraud. Let's say I work for XYZ manufacturing. We have some cash walk-in customers. The customer pays $100.00 over the counter in payment of invoice 12366 and leaves. I pocket the $100.00 and shred the invoice. Sometime later, an auditor notices the gap. Of course I tell her, "Oh that's just a quirk of our accounting system. That invoice never really existed." End of story? I think not. What you are looking at now is an expanded audit and balooning audit expenses.

Gaps in Purchase Order sequence numbers open similar opportunites for fraud.

Jack mentioned ISO. I can't quote chapter and verse, but I recall a UA discussion several years ago where the OP referred to an ISO requirement for the continuity of control document sequence numbers.

Incidentally, just this week, a local woman was sentenced to several years in jail for stealing $50,000+ from the charity she worked for. Chances are that she did the deed by evading the audit trail. Equally likely is that the flawed audit trail led to her detection, arrest and conviction.

Glenn

Interesting Glenn. I have yet to face a situation where sequential incrementation is indeed a strict requirement, and I can now certainly see how it would be in such circumstance, and I suppose it doesn't surprise me that ISO would dictate this in some form or another. Thanks for the input.

Great points all around.

Regarding the identifier for the invoices - I don't deny that it may make things easier to be able to talk about an invoice identified by something unique. However, what I'm saying is that it more likely, at least in my mind, makes sense to have a invoice filed on 9/5/2010 to Banana Republic identified by say, "BANREP20100905" than it is to identify it as "3812031". The former is more human readable & memorable and provides some information encoded that also aids in searching & finding the relevant data for this one invoice. My whole concern from the start is that when we're talking about identifying an invoice for user (as opposed to RDBMS which we can just slap any good old number and forget about it) is that it has to work without any trainings spent toward explaining that and this is not a quirk but an expected part of process. This won't change the fact that there will be organizations that requires a control numbering and in such circumstances there's little I can do to argue beyond than pointing that people are more likely to talk about events in terms of date, name, what they bought, a price they paid but when you ask for a confirmation number, they're all "um, i wrote it on a napkin, hang on let me look it for ... [long pause]... i think it was something like.... 023.... I don't remember." I really can't call this easy & convenient.

But that's just my $0.02 ZWD. (IMG:style_emoticons/default/smile.gif)

I had a client that runs a limousine service. They actually ran their business for 10 years using a
modified version of a Microsoft template. The person they hired to 'develop' the application used
an autonumber field named invoicenumber. It shows on every form that uses it. I could not
convince them that it needed to be changed and that it was worth paying me to do so.


Up until 2009 they were not required to pay sales tax on the services they provide. Along with
the the change in law came state audits. The very first question asked by the auditor was "why
are there gaps in the invoice numbers?"

The next day I received a phone call asking me what I could do about it...

Ace,

I'm glad to hear that at least this audit standard hasn't change in the 15 or so years since I was involved in administration/accounting. I was going from memory so to hear that that business rule is current and general is re-assuring.

Incidentally, my solution to the issue, which I am sure you don't need, is to use autonumber for data management ids and a second uniquely indexed field for document control numbers, populating the latter with routines such as Jack's at the heart of the code.

Glenn

Interesting that the conversation is turning towards the need for sequential numbering... because even with an actual autonumber field, the values are not guaranteed to be in sequence. I haven't completely thought it through, but it seems that verifying continuity in sequencing would involve an analysis of all the existing data to make sure there are no gaps, and filling gaps when they are found. I suppose one could keep a value stored (sequence check up till number #) and you could narrow the required analysis to work from that value up.

There's no strict requirement that the date of the document sequence be synced with the ID of the sequence, right? So if 555 gets filled a month after 777, as long as 555 is there it wouldn't matter, right?

And, what about starting numbers? Do ISO and Audits require that a record be kept stating what the starting number of an ID system was? I know of a good many ID systems that don't start at 1.

Hi Jack,

To my mind, one of the reasons for custom numbers is precisely because autonumbers do not guarantee continuity and sequence. I have to admit that was the direction my mind went in when I saw your original post. I realize now that I have taken things beyond your original intent.

I think sequences would ordinarily begin at 1 but tracking the actual beginning of a sequence would take care of the eventuality that a particular instance starts otherwise. Off the top of my head, I think that from an audit perspective, the concern is not so much that every number used since the beginning of time be accounted for but rather that the number series from the start to end of a fiscal period be in tact. So, if I am auditing (don't get me wrong, I am not really an auditor) a multi-year fiscal period beginning January 1 2005 and ending December 31, 2009, I would be interested in the last number used in 2004 and that all numbers are accounted for over the subsequent five years. If, in fact, some numbers got used out of sequence, it should still be possible to reconcile the series with actual activities.

Glenn