How to use autonumber properly

[treehstn]

Hi,



I have a db that I use to make inventory tags for selling items in my antique booth. I've used it for years but have had to manually key every item number into the db. I'd like it to sequentially autonumber my inventory. Right now I'm using a field I've named "InventoryNumber". I have nearly 2000 rows in my main table and a few dozen in another table. It's not a lot of data. Is there a way that I can either change the InventoryNumber field from ShortText to autonumber and have the existing numbers stay the same? Or should I add a field to the db that is the autonumber?

The above table is using straight numeric digits for the inventory number. But in my jewelry table I'm using J-1140 as the inventory number. I would like to do the same in this table with the autonumbering...

Thank you for your help!

[pbaldy]

In a brief test, I'm not allowed to change an existing field to autonumber. You can certainly add a new field.

For the table that has J-1140, you can use an autonumber field for the numeric part and a text field for the letter, and concatenate them for the user.

Note that some people will have an issue with you using an autonumber field for a user-visible value. I don't, as long as you understand its limitations. For example, in certain situations it can skip a number.

[treehstn]

I tried adding a new field and it lets me do that but the autonumber starts at 1. From what I'm reading if I need it to start at my first existing inventory number, which is 1064, then I have to run some kind of a query to get it to do that. I don't know how to do queries or VB. I'm still fumbling around trying to figure it out....

[isladogs]

See this article by Allen Browne Microsoft Access tips: Set AutoNumbers to start from ... (allenbrowne.com)

[CJ_London]

Create a new empty table based on you existing table. Add the auto number field.

Then create an insert query to populate the new table from the old table, assigning your inventory number field to the auto number field

[ArviLaanemets]

For start, let's set the terminology straight!
Autonumber in access (and in other relational databases) is specific numeric field with unique values, which database fills automatically (without any input from user, or through code) whenever a new record is created.
What you want, is an unique identifier for entry in your table. Generally such identifier is created in BeforeUpdate event of form. (It is possible also to have a hidden control linked to this identifier on form, which is filled automatically whenever a new entry in form is created, and saved when the record is saved, but this approach may cause problems when several users are adding new records at nearly same time.). NB! You can define an unique index based on this field - additional to Primary Key of table.

From post#1 follows, you want an identifier in format "C-####", where "C" is (single?) character, and "####" is a number.
Obviously character may have other values than "J" in your example (otherwise you could use a numeric identifier or autonumber instead). So you have to determine the character value somehow (based on some fields in entry following some set of rules, or by manual entry, or on whatever);
The next step is to find latest identifier with same character part in your table. To make this possible, you must have the numeric part of fixed width (i.e. your identifiers must be like "J-0001", J-0001", etc., up to "J-9999". NB! You must determine the width of numeric part wide enough so you don't need to redesign the field anytime later, or you'll get into world of hurt's!). When the numeric part is of fixed width, the latest identifier can be easily found using a query like "SELECT TOP 1 YourIdentifier FROM YourTable WHERE Left(YourIdentifier,1) = 'J' ORDER BY YourIdentifier DESC". From found value, you remove character part and "-", and convert remaining part to integer;
The last step is to add 1 to found integer, composite new identifier from character part and new number, and use the value you got as identifier field value of entry to be saved.

[ssanfu]

There used to be a page in "UtterAccess" about Autonumbers, but they redesigned their site and it disappeared. However, I did find a copy I saved on my computer.


Autonumbers--What they are NOT and What They Are


It is 4 pages long and rather than paste it in the reply, I'm going to attach it. (it is RTF format)

[Micron]

Steve, want to borrow my link?
http://access.mvps.org/access/general/gen0025.htm

[ssanfu]

@Micron, Thanks......I've got that one.
The UtterAccess link had much more about Autonumbers:

A Small excerpt ....

What they are NOT:
1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
.
.
.
.



Actually, I have 4 links about Autonumbers: (well, 3 now that the UtterAccess link is broken )

Autonumbers--What they are NOT and What They Are........................(link broken)
Microsoft Access Tables: Primary Key Tips and Techniques........... http://www.fmsinc.com/free/newtips/primarykey.asp
Use Autonumbers properly.................................................. ......... http://access.mvps.org/access/general/gen0025.htm
Relational Database Primary Keys................................................. http://www.databasedev.co.uk/primary_keys.html