X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Custom Autonumbers    
post Oct 2 2010, 06:22 AM

UtterAccess Administrator
Posts: 10,570
Joined: 7-December 09
From: St. Augustine, FL

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.
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.
Attached File(s)
Attached File  AutoNum2k.zip ( 30.03K )Number of downloads: 1692
Attached File  Anum_Instr.doc ( 25.5K )Number of downloads: 1335
Go to the top of the page
Start new topic
post Oct 6 2010, 02:23 PM

UtterAccess Editor
Posts: 18,007
Joined: 4-December 03
From: Northern Virginia, USA

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.
Go to the top of the page

Posts in this topic
- jleach   Custom Autonumbers   Oct 2 2010, 06:22 AM
- - argeedblu   Jack, reat addition to the archive. However, I am ...   Oct 2 2010, 08:34 AM
- - datAdrenaline   Hello Glenn (and Jack) The error occurrs because ...   Oct 2 2010, 10:39 PM
- - datAdrenaline   I am going to be a devils advocate to some extent ...   Oct 2 2010, 11:08 PM
- - argeedblu   Thanks Brent, didn't have time to track it do...   Oct 3 2010, 04:17 AM
- - jleach   The original attachment has been replaced with a v...   Oct 3 2010, 05:25 AM
- - BananaRepublic   Personally, the biggest issue for me WRT showing a...   Oct 3 2010, 11:22 AM
- - datAdrenaline   Well .. I debated as to whether to reply as I thin...   Oct 6 2010, 02:23 PM
- - datAdrenaline   Jack, ossible improvement ... Use ONE table to tra...   Oct 6 2010, 02:30 PM
- - BananaRepublic   Actually, no. I said no training is necessary when...   Oct 6 2010, 02:56 PM
- - jleach   Hi Brent. I intentionally set this up on seperate...   Oct 6 2010, 04:05 PM
- - argeedblu   > Hmmm. In the physical world, an accounting syste...   Oct 7 2010, 05:49 AM
- - jleach   Interesting Glenn. I have yet to face a situation...   Oct 7 2010, 05:59 AM
- - BananaRepublic   Great points all around. Regarding the identifier ...   Oct 7 2010, 06:44 AM
- - ace   I had a client that runs a limousine service. They...   Oct 7 2010, 06:58 AM
- - argeedblu   Ace, I'm glad to hear that at least this audit...   Oct 7 2010, 07:06 AM
- - jleach   Interesting that the conversation is turning towar...   Oct 7 2010, 07:09 AM
- - argeedblu   Hi Jack, o my mind, one of the reasons for custom ...   Oct 7 2010, 08:42 AM
- - BananaRepublic   Glenn or anyone else who's familiar with audit...   Oct 7 2010, 10:32 AM
- - argeedblu   ally, I find anything that has leading characters ...   Oct 7 2010, 11:18 AM
- - BananaRepublic   Just to provide a perspective: In Oracle and Postg...   Oct 7 2010, 11:31 AM
- - BananaRepublic   I agree that the specific encoding I suggested wou...   Oct 7 2010, 11:50 AM
- - argeedblu   HI Banana, I'm just going to make a general re...   Oct 7 2010, 12:37 PM
- - datAdrenaline   Wow ... lots of good dialog ... Back to the what s...   Oct 7 2010, 12:47 PM
- - BananaRepublic   Well, I don't know the specifics of your proce...   Oct 7 2010, 01:50 PM
- - jleach   For inventory items, yes, there's generally a ...   Oct 7 2010, 01:58 PM
- - rabroersma   >Don't forget to add check digits to prevent d...   Oct 7 2010, 02:00 PM
- - datAdrenaline   Ok ... I chuckled with 'BANREP' ... Where...   Oct 7 2010, 03:58 PM
- - debmars   This is an interesting debate. With my accounting ...   Oct 23 2010, 02:34 AM
- - argeedblu   Mars, For what it's worth, yours is a situatio...   Oct 23 2010, 04:47 AM
- - adb_seeker   I ran into a problem when I changed the data in th...   Feb 11 2012, 12:04 PM
- - jleach   Did you add the ERNum as a member of the Enum as d...   Feb 11 2012, 12:12 PM
- - adb_seeker   Hi, Jack After placing my information in the field...   Feb 11 2012, 12:38 PM
- - adb_seeker   Yes. I replaced dsAutoNumQuote = 1 with ERNum = 1   Feb 11 2012, 12:39 PM
- - jleach   I'll have to look into it... last time I looke...   Feb 11 2012, 12:51 PM
- - adb_seeker   Thanks. I appreciate your help.   Feb 11 2012, 12:55 PM
- - jleach   Couple questions: That version of Access are you u...   Feb 11 2012, 03:57 PM
- - adb_seeker   I use Acess 2000. The modules compile correctly if...   Feb 13 2012, 10:51 AM
- - jleach   Hi, The DB I posted would have had the DAO referen...   Feb 13 2012, 06:38 PM
- - mefrogs8   I am using Access 2007 will this work? And do I c...   Apr 25 2012, 02:38 PM
- - jleach   Hi, The instructions should tell how to import th...   Apr 25 2012, 02:43 PM
- - mefrogs8   Thank you, but I am not entirely sure how to do th...   Apr 25 2012, 03:14 PM
- - jleach   Hi, This would be a question better suited for the...   Apr 25 2012, 06:05 PM
- - mefrogs8   Thank you for your help. I will try that!   Apr 26 2012, 08:10 AM
- - Amr.arfa   My access 2019 & 2106 I have one table i need ...   Oct 12 2019, 01:47 PM

Custom Search

RSSSearch   Top   Lo-Fi    7th July 2020 - 12:55 AM