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.

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 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 .

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

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 ... ... 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!

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.

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

Glenn or anyone else who's familiar with auditing process (the only experience I have with auditing is one for caseworks which I don't think is in same realms as financial auditing)

Do you know what would be the general response to the idea I mentioned earlier of encoding the invoice with such thing as "BANREP20100905"? Would they still pooh-pooh and insist on a proper control numbering system? I suppose it will end up depending on which auditing rules we're looking at but what I'm hoping is for a general tendencies, a measure of how common such system would be considered acceptable under various auditing system?

Also, so I know - we all agreed that gaps means accounting tracking is suspect and auditor may not like that. Does it makes any difference if the same company always generated a end-of-month report enumerating all invoices in their exact state at the end of month, and compared it with an external source (e.g. bank statement for example) to show that both does balance out? Would auditor pooh-pooh this also (in general)?

Thanks!

Personally, I find anything that has leading characters other than 0's to be more difficult to read and mentally process than 'pure' numeric digits. The specific encoding you have suggested here would be awkward if the company issues more than one invoice per day (assuming that the numeric part of the code is a coded date) so you would need even more digits to distinguish individual invoices. I think it would be more difficult to test the integrity of the sequence than it would be with a purely numeric sequence, either mechanically or electronically.

There are also performance issues given that numeric indexes are faster to process that alphanumeric.

I also read someone recently (sorry I can't point to the article) commenting on that sort of encoding as a hangover from manual (i.e. paper) filining systems.

I thing that would make the process more difficult (read costly) because an auditor would have to review each of these reports. Many audit techniques rely on random selection of documents and transactions for testing. As far as I know,the random values are generated independantly from the client's data. If you are using simple numeric document control values then the audit can use a simple generic process to generate the test values. Any other form of coding would require a custom selection (again read $$$) to deal with the special format.

One example comes to mind that might clarify the issue Banana, is banks and credit cards. I don't know of any bank account or credit card numbers that have the kind of alphanumeric coding you are suggesting. I mention that specific sector because of the sheer volume of transactions that the have to handle and control on a daily basis. In Canada, at least, Banks might refer to themselves with cutesy acronyms (RBC, BMO, etc.) in their marketing and advertising on their credit cards and cheques, etc. the bank, branch, and customer account are all numerically coded. I don't have any knowlege of how they identify transactions internally, of course.

Glenn

Just to provide a perspective: In Oracle and PostgreSQL where we have the concepts of sequences, basically a database object that spits out a number. We can associate any number of tables to a single sequence. I don't know how often they actually associate a sequence to more than one tables but there are some scenarios where they like to re-use sequences across more than one table. Typically, when we create a new record, we have to call a nextval() and for any subsequential records in other tables, we call currval(). Both functions has to identify which sequence we want to obtain the number from, so if I had a sequence named "city_seq_id", and this was used to generate primary key for a table "city", the column "city_id" would have a default value of "nextval("city_seq_id"), and if I were to populate child records associated with city (let's say a table of neighorhoods?), I would call "currval("city_seq_id")" as part of the application logic. The latter is very similar to how we use "SELECT @@identity;" in Access to retrieve the last inserted ID but with more granularity.

Hope that helps.

I agree that the specific encoding I suggested would not work for any scenarios involving multiple invoices with same customers in a given day and to keep the same encoding would require adding more digits (e.g. time value, perhaps). I guess we just have our preferences WRT which is easier to read; a pure numeric ID or an encoded ID. I'm a big believer in working well with humans who don't always necessarily memorize a large sequence of numbers and need a quick'n'easy reference to locate the documents needed. This may also have been colored by the fact that I worked in Case Management, where everything is much more human-oriented - we never refer to our cases as ID = XXXX but rather by the name of the client, and by same token, we never refer to any documents associated with casework with numbers but rather dates and some significant information (e.g. "locate the service plan I created for client John Doe in month of September - it should contain something about obtaining the financial assistance").

I also speculated that for a small business, it would make more sense to have human-friendly encoding but maybe I'm the minority here.



I 110% agree. We wouldn't want to try and search & find a full record & all its associated records using that encoded ID - I think that in general, it should be OK to locate the record using encoded ID then use the internal autonumber to collect all other related data. But you're absolutely correct - comparing a sequence of alphanumeric characters is always more expensive than comparing a pair of numbers.



Wasn't it me? I mentioned it earlier up in the thread but in regards to the control number? Or did you mean outside the thread?



Ahh, makes perfect sense. Thanks.



I agree that in financial section, the sheer volume of transactions probably will make it very unlikely that a encoded ID will be useful and numbers are simpler to deal with. I'm coming from customers' viewpoint. Consider how one would log in to their bank online to do some statement stuff - they don't actually enter their account number (at least my banks hasn't tried to get my account number that way), I enter an username and password, and I see my accounts but only by last four digits, for obvious security reasons. Yet, I am more likely to mentally think of my accounts as "Personal Checking", "Personal Saving" and "Money Market" than *0001, *0002, *0003, and that is how my bank internet site would describe those accounts. Though I may need to use my full account number to perform a wire transfer and make a deposit, when I'm talking with a banker (after validating myself which may or may not include sharing the full account number) or working online, I don't actually use the full account name but more human-friendly name of "Personal Checking" and in fact, it is possible to name an account to some kind of custom name. (I don't think that's useful outside the internet site, though but I've never bothered with it, though.) Anyway, when we look from the customer's viewpoint, it *seems* to me that it makes more sense to supply a human-friendly ID that will improve chance of it being memorable and readable in future interactions. The same conversation would certainly not apply between say, an auditor and a banker who can only talk about data in terms of account numbers (the fact that an account is owned by John Doe has no bearings to either of them unlike the earlier scenario where I'm talking about my account).

I don't think it'd be wise to suggest that we have both control numbering and human-friendly encoding so most likely, we'll have to choose what best suit the business, I guess.

HI Banana,

I'm just going to make a general reply. I think we are pretty much in agreement that the solution has to fit the scenario and its business rules. You are coming from a perspective of finding information about people and I am coming from a perspective of accounting and audit controls.

I agree that referring to people as numbers is awkward and disrectful of the person. However, in the electronic data management realm, I see no need to create artificially encoded identifier to help with the search for a person's information. (My comment about paper filing systems may well have been referring to your earlier post in this thread.) When I am implementing people searches, I use combos or listboxes and let the user initiate the search by typing the person's surname for starters and then displaying distinguishing information to help narrow down the search.

Glenn

Wow ... lots of good dialog ...

Back to the what seems to be the start: "You shouldn't use an actual Autonumber field, as per best practices they shouldn't ever be seen by the user", in short, the visibility of an Autonumber.

To that end, I beleive our varied discussion supports my response that the statement of "shouldn't ever be seen by the user" is not a "best practice". Again, I say we should implement what is best for the application's scope and purpose and evaluate how a replacement of an Autonumber will differ from the one Access (Jet/ACE) already provides you. Note that in an application, it may be just fine to show an Autonumber for a table, and in another table of the same app its more appropriate to hide it. For example, on a very simple side of things, I show the Autonumber quite often on my "top level" (aka: Primary or Core) tables, however, rarely, if every, would I show the Autonumber associated with records from Domain (lookup) tables or the Autonumber associated with records from "child" tables. I often don't like absolutes when it comes to design practices since there are always scenarios in which a deviation is appropriate, or non-harmful.

----



I am with Jack, rarely have I found that companies use the mentioned methods as the primary search mechanism. Also, those mechanism's just don't apply in several application. I personally develeped an manufacturing process managment app, there is NO WAY we could use those mechanisms. The numbers were the key ... all other searches were secondary. With respect to training, now the focus is on how to verify the record you think is a match to what you seeking is indeed the record you are seeking.

PS ... when I know the website, I just type in its address, no need for my favorite search engine ... When I am on my personally network, I type in the IP addy's



With the NewValues property set to increment, it will take a LONG time before you get to the negative numbers (but you can get there!). If you Replicate the data, an Increment setting for New Values is automatically changed to Random, or the designer could specify Random, and thus introduce negatives. Any behavior other that what is specified in New Values is/was caused by bugs or inappropriate appending of data into the Autonumber field. Inserting records, could prove to be problematic for either system of numbering (Autonumber or code generated number). As I stated earlier, in a couple of replicated systems I've supported, I display the autonumber values as HEX (I was given the idea from a UA discussion quite sometime ago), thus avoiding the surprize of users when/if the experience negative id numbers.



This seems to assume the only record is on paper. Any audit I have been involved in (two) has solely been focused on the financials --- PO's matched to withdrawals, Invoice's matched to deposits... both were a nightmare, but neither audit required a contiguous series of numbers. I am not saying its not benificial, but I do know that gaps in series numbers was not an issue. Possibly because there was never any claim (via company policy or statement of adherence to a standard) indicating the numbers were contiguous. I am not an accountant/auditor/etc. so please do not use my experience as "the law"!


In a theoretical sense an identifier of "BANREP20100905" is a calculation and should not be stored Others may view it as a violation of normalization since it represents more than one attribute. I see an identifier like that and to me, its all metadata that should be easy to gather by looking at the document it represents. Also, I find it very efficient and self explanatory really to tell the user --- type in the id here, then hit enter. If the id in not known, then you can search by <blah> or <blah> over here.

Again these types of nuances go back to what specification do you need to adhere to; what do your users expect and what will they know (or can gather) when searching; and what type of information are you searching for. In your examples, you are assuming a two person interaction to find data --- likely a scenario that will involve different mechanisms of searching. Where as, if your job is order fulfillment and you are looking at a list of orders to fulfill, you'll likely want to search by the id number.


In my experience, the auditors did not care. It was based on financials, the internal mechanisms of tracking was only mentioned for the auditors to understand our processes.


I think they can garantee generation in sequence as well as continuity --- BUT --- the values generated may NOT be stored, or once stored, actions (like deletions) create the scenario of non-contiguous numbers. But, if the app your creating requires neither spec (continuity or sequence), then the Autonumber works very well and may warrent its display to, and use by, the user.

----

Ultimately, it is our responsibility to know how to implement what is required by the application that is being created in an efficient manner and avoid scope creep. Knowing the behaviors/constraints/etc of using Autonumbers, code generated numbers, surrogate keys, adherance to normalization, DAO, ADO, etc, etc ... helps us implement what is needed to keep the data clean, the application solid, and the client satisfied --- and thus reach Expert status, and leave everything grey (simplicity often rules the day).

Well, I don't know the specifics of your process but I'd think that even for purely numeric data, they still do encode some information. They may name a part 0013-49-0138, in which each components means something (e.g. part number, model number, batch number for instance). I'd be interested to know if a purely arbitrary number is actually used instead of some kind of encoded number as mentioned. Even for something complicated as screws, there are already published standard classifications that companies use to specify what kind of screws they want for their assembly and they are more likely to use that standard encoding than an arbitrary numbering, I would think.



I agree that this is technically a calculation and thus we have to give consideration as whether it's in fact appropriate. It should certainly not replace any data (e.g. Invoice date for example) which should still be in its own column. It also has the unfortunate effect of opening a can of worm in that if we revise an invoice on 2010-09-06, do we need to change the identifier to this or keep the original calculation (thus implying that it has to be stored at the time of creation albeit never ever updated).



Actually while I'm assuming two person interaction, we also need to take in consideration of who those people are. For example it's not uncommon that a client walks in, and find that the primary caseworker is not available for whatever so has to work with a different case worker who do not have all the information of where the client stands on the case, so the caseworker has to depend on the client to supply the information to locate the record. This won't be of much interest to your and Glenn's cited examples of people working in audit or fulfillment where they won't really care about the actual data (e.g. this order needs 10 widgets, this account belongs to a John Smith, etc. Not interested in those data). However, this would also, I think, apply to customer service where it is unlikely that the customer representative will have all the information and is dependent on the client to supply the information but if client isn't exactly rigorous in their record-keeping, it tends to come out as "bad customer service" if the representative told customers that they can't do anything because the customer did not write down the invoice number even if that was the only invoice that customer made with the company in the entire year. So even though you may know the website of UtterAccess or IP of your personal computer, it doesn't mean that your cousin knows the same thing.

I would also suggest that by having a encoded ID that contains some of metadata, we can help simplify the search scope. To illustrate quickly, on the form we can have two cascading comboboxes, one listing like:

and the other cascading comboboxes would then list all dates of invoices filed by "Banana Republic". It's much more probable that the customer in question know that he's a representative of Banana Republic (if he didn't know, we have a MUCH bigger problem!!! , and only need to be approximate with the dates. This is at least in my mind more efficient (from a human user perspective, not necessarily in terms of query time) than a freeform search where we need to search on different columns. Of course we have to keep in mind the can of worms I spoke about.



I absolutely agree that we have to flexible and adapt to the specific scenarios. I do think we also have to remember that once we've trained a group of users does not necessarily guarantee confusion won't arise in future when the turnover has replaced most of those employees and the "corporate knowledge" becomes lost. If we can architect a structure that does not assume pre-existing knowledge (e.g. knowing that autonumber aren't meant to be contiguous), I think we'll be better for it.

EDIT:

I should clarify the last statement - when I talk about corporate knowledge, it's very clear that a new hire must be trained in nuances of company's processes so that component of knowledge cannot be afforded to be lost (else, the company goes out of business summarily), so it's very reasonable to expect that user adhere to the company's specific process and understand the logic behind it. Therefore, when I was talking about losing knowledge, I was referring more to the "extraneous knowledge" that is not strictly required as part of user's job (e.g. knowing about autonumber not being contiguous). If the application requires its own training apart from the mandatory training required to perform the job for company X, then I do not feel that the application is well-designed. To make this clear - nobody is required to actually read a car manual in order to drive it - one should be able to go from one car to other car and drive it just equally well without any additional training. If a car company designed a car that required different way of driving it (and it's not a 18 wheeler or anything non-car-like) then nobody will want to buy that car. I feel that same principle ought apply to custom application we author for other people.) I hope that helps clarify things....

For inventory items, yes, there's generally a pre-set number that represents something, but that's about it. Certifications, Test Reports, Purchase Orders (coming and going), Quotes, Invoices, Packing Slips, etc etc etc, all ID'd by a number which is not related to anything else.

Don't forget to add check digits to prevent data-entry errors. Also if encoding schema are to be used it would be good to consider what conventions are already establish for the industry whether they be ISO (international), national, or discipline specific.

Ok ... I chuckled with 'BANREP' ... Where's that hammer! ... Made me think of when I had to write code that prevented writing values that appeared as 'BAD' or 'DEAD' when viewed with a Hex editor.

This is an interesting debate. With my accounting system I have to copy the purchase orders and invoices supplied by the company I'm dealing with. One Company has this 657890899 as their purchase order number. So I cant have an auto-number sequence without modifying it. So I have to have to be able to duplicate that. With this date idea how would it work with this type of numbering because I would Imagine that the whole line in each identifier would be too long. Just a curious question

Cheers
Mars

Mars,

For what it's worth, yours is a situation where I would definitely use an autonumber as the record identifier (used internally by the database to manage the data) and a separate field for the invoice number (however it is formed) as the external information identifier.

That said, I haven't answered your question about the 'date idea' and I am not sure what you mean by 'the whole line in each identifier'.

Glenn

I ran into a problem when I changed the data in the module to what is in mydb. When I test the function by running ?getautonum(ERNum) I get compile error "Variable not defined" and dbtext is selected. The Enum portion of the module is highlighted in yellow. What am I missing?

Did you add the ERNum as a member of the Enum as described in the instructions?

hth

Hi, Jack
After placing my information in the fields indicated, I compiled and and saved my database. When testing the function, I get compile error "Variable not defined". dbText is selected and the getautonum portion of the module is highlighted in yellow. What am I missing?

Yes. I replaced dsAutoNumQuote = 1 with ERNum = 1

I'll have to look into it... last time I looked at this module was when I posted it here, so it may take me a day or so to get refreshed with it. I'll be in touch...

Thanks. I appreciate your help.

Couple questions:

What version of Access are you using?

All modules compile correctly after you import them? If so, do you use Option Explicit at the start of your modules?

Do you have DAO selected in references? (for 2003 or before this will be "Microsoft DAO 3.6 Object Library", or 2007/2010 will be "Microsoft Office #.0 Access database engine Object")

I use Acess 2000. The modules compile correctly if I make no changes and test in the database you posted. When I change the data, it no longer works (even in the database you posted).
Although I've used Access for many years, I'm uncertain as to where to go to select DAO in references. Thank you for your help.

Linda

Hi,

The DB I posted would have had the DAO reference - maybe this is why it isn't working in yours?

In the VBA Editor, go to Tools -> References, and you'll see a list. You're looking for "Microsoft DAO 3.6 Object Library". If it's not selected, find it in the list and select it. If it is selected, then it's some other problem.

Let me know,

hth

I am using Access 2007 will this work? And do I copy the information in your zip file to my file or do I just create from your instructions into my db? Thank you!

Hi,

The instructions should tell how to import the module and set up bit of code and object names as required. It's been a while since I wrote them so I don't remember in detail, but if there's problems stop back and someone will give a hand.

This should work on Access 2007 without issue, it is not version specific.

Hope it helps,

Thank you, but I am not entirely sure how to do this because I haven't worked with Access 2007 only 2003 and never had the need to use a number without duplicates and having multi users. I was hoping for something simple. I have a field named Job # that I need to have on my form I want to be able to have a unique number incremented by 1 starting with 0001 at the open of the form, however I do not want the number duplicated or cancelled incase another user is entering info at the same time as someone else. I see alot of people talking about DMAX function but don't even know where to begin to enter this info since it says to find the BeforeInsert event in the Form's Properties but Access 2007 does not have that in the event properties and I had another person recommending to create a table: tblNextSeries (fNextSeries Long) and then he gave me a code but I have no idea where to begin with this either. I can create the table but don't know where to put the code in? Help I am at my wits end. I am pretty good at figuring things out but this is beyond my capacity and is making me bonkers If any one can help me it would be greatly appreciated. Thank you!

Hi,

This would be a question better suited for the general forums than for a Code Archive entry. It's quite a bit more of a generic problem than any particular technicality with this implementation of a numbering system. If you post that question in the Access Q & A forum many people would be able to help.

I would start a thread stating that you want to try to use DMax but are not sure how to locate the BeforeInsert event (it's there, the event hasn't been removed in 2007). The table and code you describe sounds like a similar implementation as this, although maybe a little more simple setup than this. If you were to post an example of that code someone should be able to help further. In either case, I would recommend getting your feet wet with DMax and seeing how it works for you before moving into this. DMax is the easiest implementation, so that'd be the best one to get squared away first.

When you start a new thread, you may want to note that you've looked at this Code Archive entry and was directed to the Q & A Forum instead, lest someone point you here!

Note that the fundementals of Access have not changed between 2003 and 2007. While 2007 adds a few more things in (which most developers don't use anyway), you can continue with Access 2003 practices almost seamlessly in 2007.

Good luck with the project.

Thank you for your help. I will try that!