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.
Search and find records by using person's name, company's name, invoice date or title, IMHO, requires less training and explanation.
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
What this custom autonumber function DOES do that a conventional autonumber CAN'T do is generate an unsigned guaranteed unique integer
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.
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.
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"!
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".....
....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.
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.
Do you know what would be the general response to the idea I mentioned earlier of encoding the invoice with such thing as "BANREP20100905"?
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.
To my mind, one of the reasons for custom numbers is precisely because autonumbers do not guarantee continuity and sequence.
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).