Autonumber unique increaments

[w0od0o]

hi, i am looking at rather than having 1, 2, 3, 4, ect for my autonumber i want to auto assign a certain alpha/number ID such as ABC0001 i would also need this to start at a number later in the sequence such as ABC0456

all help is appreciated

thanks

w0od0o

 

[Atomic Shrimp]

Autonumbers aren't designed for that sort of functionality - they're designed solely to offer a unique ID for each record in the table.

They're not intended to be able to have meaning - and problems can arise from trying to assign meaning to them.

It's better to use an autonumber as the key field, not revealed to the user, and have additional fields for product codes, or case codes, or order codes, or whatever it is you're trying to do, and generate these codes yourself, in your application on demand.

 

[w0od0o]

i thought as much tbh, was something i left untill the end and when i looked at it changing it i realised it wasnt meant to happen

can i auto increament other fields?

 

[Mike375]

i thought as much tbh, was something i left untill the end and when i looked at it changing it i realised it wasnt meant to happen

can i auto increament other fields?

This thread (and counless others) has something

http://www.access-programmers.co.uk/forums/showthread.php?p=801480#post801480

If need incrementing displays such as ABC0456, ABC0457 etc, the I would probably increment the whole number part, that is, the 456 and 457. You can then join that in a query with another field to give the ABC0456. Using the Len() on the field with the 456 or 26 will allow Access to know how many 0s to place before the whole number and the ABC. All sorts of ways of doing this type of thing. But the above thread will show you how to increment the whole number.

 

[Dennisk]

Several Years ago I was asked to look at a db that had become corrupted.
The corruption was so bad I had to use a 3rd party tool to recover the data. Although all the data was recovered the autonumber field and all indexes where dropped by the tool. So I had to regenerate the autonumbers and foreign keys. Then the users pointed out that the autonumber had been used on patients case notes. All the case notes had to be amended to the new autonumber.

Moral of the story. Never expose an autonumber to a user. Always keep it internal to the database.

 

[w0od0o]

thank you both, i seem to be able to get the above working with some modified tables and forms but as soon as i run my database from my switchboard it wants to restart the number from 1 again, any ideas?

sorry got 2 posts mixed up

heres my code from the link above

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.contractno = 1
Else
Me.contractno = DMax("[contractno]", "contract") + 1
MsgBox "Contract number generated"
End If
End If
End Sub

thanks

 

[Pat Hartman]

Moral of the story. Never expose an autonumber to a user. Always keep it internal to the database

That's a good moral however, you didn't need to regenerate the autonumbers. Access, along with all other RDBMS', will allow you to use append queries to append rows with existing autonumbers and as long as the numbers don't duplicate any existing value, they are accepted. Appending autonumbers will adjust the "next number" if necessary automatically in Access. You may need to do this manually with other RDBMS'.

 

[Dennisk]

Pat,
Don't forget that when the db was recovered the all auto number fields where converted to integer fields, and my understanding is that to reinstate the auto number I had to create a new autonumber PK, and of course because records had been deleted after a while the new autonumbers became out of step with the original numbers.

 

[gemma-the-husky]

have a constants table with "nextrecordnumber"

read and increment this, when you need one, then reformat it to include whatever other characters you need

then you can change this to any start number you need

 

[Atomic Shrimp]

have a constants table with "nextrecordnumber"

read and increment this, when you need one, then reformat it to include whatever other characters you need

then you can change this to any start number you need

Surely you mean a variables table... (only because I love being pedantic - sorry )

 

[gemma-the-husky]

probably, now you mention it

most systems have a constants table - name address tax ref numbers - the point being that there will only be one of this type of record

given that, i find it convenient to use this table to store incrementing reference fields, such as invoice numbers, batch numbers etc

you can get the lastest number without needing to find a specific record with

dlookup("countername","tblconstants")

-------
some books take a similar approach but use a table IN the back end which is not a normally linked table, and open this locked while retrieving and updating - thus preventing any possibllity of simultaneous updates corrupting the numbers.

 

[Pat Hartman]

Pat,
Don't forget that when the db was recovered the all auto number fields where converted to integer fields, and my understanding is that to reinstate the auto number I had to create a new autonumber PK, and of course because records had been deleted after a while the new autonumbers became out of step with the original numbers.

Read what I said again. You need to start with target table that has an autonumber PK and is either empty or does not contain existing pk values in the range of those being appended. you then run an APPEND query to copy the old data to the new table. In the APPEND query you would select the old autonumber and append it to the autonumber column in the new table.

You CANNOT just add an autonumber column to an existing table since that would renumber all rows!