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 7 2010, 05:49 AM

Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada

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.
aps 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.
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 - 02:03 AM