UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Custom Autonumbers    
 
   
jleach
post Oct 2 2010, 06:22 AM
Post#1


UtterAccess Administrator
Posts: 10,565
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.
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.
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
Replies
mefrogs8
post Apr 26 2012, 08:10 AM
Post#2



Posts: 11
Joined: 29-March 12



Thank you for your help. I will try that!
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    4th July 2020 - 01:34 PM