Newbie question about AutoNumbers 1

[BotCow]

Okay it's actually two questions hehe I lied.
1) I've been messing around with MS Access and I noticed that if you add a record with an AutoNumber primary key, then it gets assigned the next number like it should. But when you delete it and add a new record, it gets assigned the number after the record deleted. eg. (add record "whoot&quot it gets assigned number '7'. (delete record "whoot&quot okay (add record "d'oh&quot it gets assinged '8'... How do you assign it the next number and not the number after?

2) How do you do customized AutoNumbers. Like for some reason you wanted the AutoNumber to start at 5 and go to 60 then skip 61-65 and go straight to 66-1100. Or, have it always have the prefex 'OG' and then a few numbers. eg. Records go at OG0001, OG0002, OG0003, etc. Thanks for your time.

 

[fneily]

You discovered the "problem" with Access autonumber. It will always keep incrementing regardless of how many records you delete. You don't even have to save the record. If you start a record and then cancel, you have in effect used that next autonumber. To get around this you can do the following which anwers your other question:

Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.

Neil

 

[MichaelRed]

AutoNumber is a common topic in most Ms. A. disscussion arenas, including these fora (Tek-tips), and YOUR specic subtopics are WELL covered here. Additionally, your first issue is documented in the ubiquitious and under-utilized used {F1} and it's siblings.

So, I have three 'references' for you:


A.[tab]The afore mentioned ubiquitious function key

B.[tab]'Advanced' Search in these fora (keyword = Autonumber"]

C.[tab]Same as B, above, except search the FAQ'a instead of the fora.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RiverGuy]

If you want your autonumber to have meaningful value, then I would suggest creating your own. If you want it to "skip" certain ranges, then the point of the autonumber is not realised. It shouldn't matter the value of it as long as it is unique. If you need your own numbering system for business rules, then implement that yourself on the client side.