Auto numbering

[Ptrif]

I have been reading posts about using autonumber. It has been said that using them to track say order numbers or customer numbers is a bad idea. If this is true then how can you create something so that when you create say a new customoer they will automatically be assigned the next number in line?

Any help is appreciated! thanks!

Paul

 

[SgtJarrow]

see MichaelRed's faq700-184

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JeremyNYC]

Michael and I really disagree on this subject. We've pretty much exhausted ourselves on arguing about it, so I don't want to start any wars, but here's my take...

Autonumbers are great, as long as you know your way around them. (OK, just to be clear, I'm not saying Michael doesn't know his way around them, the "are great" part is my opinion. My point here is that many newbies get involved with autonumbers without having a very strong understanding of them.)

Except when using an outdated version of Jet (the data engine behind Access), I have never, in eight years of professional Access programming, seen autonumbers do anything they are not supposed to do, including generating duplicate numbers.

Autonumbers are fine for the two uses the OP asked about--I have done both of these tasks with autonumbers reliably for years. The problem is in assigning some significance to the value of the autonumber. Autonumbers are unique. They are not sequential. And they are not meant to be.

Many developers hold that users should never see an autonumber. I think this comes from the notion that users are unable to understand the ways of the autonumber. I have more faith in users.

If I am going to show an autonumber to users, I spend some time with the cient explaining what an autonumber is, and why it will not be sequential--why there will be gaps. Many developrs shy away for this, though I'm not sure why.

If you do not want to show the autonumber to the user, you can use code like Michael's or that in the Access [Version Number] Developer's Handbook to generate your own numbers. If you do this you can have letters, parts of the customer's name, whatever you want in there, as long as you tweak the code to your own liking.

But the basics of autonumbers are quite simple: they are unique numbers. Even the ones generated sequentially will not be sequential in your table because eventually a record will be deleted or the creation of a record will be abandoned. If you're doing something like formating your autonumber, you should probably just generate your own unique number instead of using an autonumber.

That's my three cents, whatever it's worth.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Darrylles]

Hi,

Perfect Jeremy. (And I've argued with you AND Michael about this - ages ago. Not as succintly nor as eloquently).

My quick points are - and some duplication here:

Autonumbers have no place in a 'correctly' normalised database where 'correctly' refers to an 'elitist' notion of by-gone days when storage space was at a premium.

Autonumbers DO add 'unnecessary data' to a database table ('unnecessary' meaning - not part of the 'meaningful information' that will be output from the database).

Autonumbers DO add additional size to a database file - without adding ANY information from themselves, but their usage ensures massive gains in design and development speeds far outwaying this pitifully small size ratio in even tiny databases.

Autonumbers CAN and ARE in fact added to the 'meaningful data' via concatenating them with other codes. This Is of course additional info. and they themselves are still 'non-data'.

The very word 'normalisation' has taken on a meaning whereby it cannot be interpreted - it MUST BE ADHERED TO - rubbish; it can be adhered to - and THEN add an autonumber.

I do wish that I had the enthusiasm for 'pure' relational database table keys, but I simply don't have the logical belief in them. (Just like with 5NF lol (is it 5nf?)).

I do have the enthusiasm for good relational database design - with some companies trying to sack me on the basis that I am 'elitist' - when I have refused to cut corners on application and database design.

Maybe we have something in common - just at different levels.

From me PTrif - use autonumbers.

Kind regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Neowynds]

I have been told to stay away from them as well, however I still think they will be the best for my use, The only problem I am having is I want to make an alphanumeric autonumber (format AB1234) Where AB is dependant upon a combo box in my form. When I select the job site from the drop down box I need the database to generate a number for the job. The two letters are an abbreviation for the job site the numbers I would prefer to have sequential, however due to the whole autonumbers having gaps wen records are put in and removed or partially entered and removed, people have discouraged me from using them, without helping me find a viable alternative(I cant seem to get anything else that has been suggested to work). I would prefer to have the gaps as that will tell me if anyone else has accessed my database and been messing with it. So if someone could help me get an alphanumeric autonumber based on a combo box I would greatly apprieciate it.

Neowynds

 

[ZmrAbdulla]

Hi,
The AlphaNumeric AutoNumber what I am using is formatted like below

"PR-"00000
Data will be
PR-0000
PR-0001
PR-0002 Like...

Go to the design table design view >
Select the Field where You want the AutoNumber,set the field to AutoNumberand do the format "AB"0000

Where the Alpha Part will be constant and Numeric part will be incrementing.

AB0001
AB0002
AB0003 Like...

I never used this for a large database having thousands of records. But it works well.

I really welcome programmers to comment on this

Regards



Zameer Abdulla

http://www.mtkhoory.com

http://www.aikah.com

 

[JeremyNYC]

Neowynds,

I think you're still not seeing it fully. You want the gaps because you think that will tell you when someone's been messing with your database. The point is that the gaps will be there even if no one "messes" with your database. Sooner or later, there will be some reason for you to delete a record, or abandon the creation of a record. That will cause a gap. Are you going to stick a post-it on your monitor for every such instance, so you don't go hunt down a co-worker for that particular gap? What you want is EXACTLY the kind of thing for which an autonumber should _not_ be used.

There is code in the Access [Version Number] Developer's Handbook that generates sequential numbers reliably. Michael Red has a FAQ with code that does so (though, last I remember, it didn't lock the table when generating the next number--sorry if that's an incorrect memory or if that's been changed since).

The Alpha part of your code also means something, which implies that you'll have to write custom code for that--no one else has the same database as you, so you can't expect canned code to work there. If you're really looking for a solution, I would suggest you try one of the solutions above, try combining it with some code to grab the alpha part, and then create your own thread detailing what you're tring to do, what you've attempted so far, and what the results have been.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Neowynds]

Thanks for the quick response Happy Turkey Day!

Possibly the problem is I am not communicating the difficulties I am having clearly. What I am looking for is a way to have a true alphanumeric sequence. The format trick works on the form but not in the actual data in the table. Also the two characters (i.e. ab0001) depend upon which jobsite is selected from the location combo box. I have spoken to a couple of friends of mine and they were saying that I could use an autonumber sequence, however before it could be put into a field on my table with the alpha characters it would have to be converted to a string then it could be placed into a field with the alpha characters. This makes sense to me in theory, but it is in the application I lose it, I am still a relative newbie to SQL and VB programming, I know just enough to be dangerous to finishing my projects in the timeline provided by my manager. Also Michael's FAQ was possibly an option but for my uses it would require HEAVY modification to work with the combo box, and no one seems to be able to help me there.

Thanks for the help maybe I can find someone willing to work with me on the modifications that would be neccessary for Michaels FAQ code to be feasible.

I appreciate all the time you guys spend helping newbies like myself. Keep up the good work.

Neo