Auto Increment Letter Number Combinations

[Penpoint]

Hi all,

In MS Access 2007 I've developed a form from a database. I have a unique identifier P000001 which I would like to auto-increment to P000002 and so forth. I've had a look around the forums and it seems there are no real straightforward answers.

If that's not possible, I would at least like to be able to use a Macro function that would search the text string and return the highest number in the unique identifier field, so that I do not have to click backwards or forwards between records to locate what the previous unique identifier was.

I assume there's a function which can search the string, separate the integers from the string, find the highest number, remember the number, and insert this value automatically into the field once a new blank record is created. The script will run whenever a new record is created.

How hard would that be?

 

[Galaxiom]

If your intention is to use the "unique identifier" as a primary key I strongly advise against it. Strings make terrible keys.

If all your values start with "P" you should not store it but instead store an Long Integer and use the Format property of any control where it is displayed. It might as well be autonumber.

Enter this in the Format property of the control:
P000000

 

[Penpoint]

I assumed from all the posts already that a letter number combination Primary Key would be in the too difficult basket.

In relation to the second portion of the question though, is there much scope to being able to auto-populate the highest number in a new field (similar to auto-increment but only to display the last recorded highest number).

It would make my work much easier if I could parse the P000001 text value and return the last value, which probably could be done if it first searches for the auto-increment ID inherent in all Access database then returns the P.ID into a blank field.

A query.

 

[Galaxiom]

Yes. There can be good reasons not to use an autonumber. This is especially so if the number means something to the user. The ability to contol the number and not leave the gaps that occur in autonumber sequence can be important such as invoice numbering.

The most common way is to use a DMax function. It can return the largest value in a number sequence from a field. Then just increment it.

However this is vulnerable in a multiuser environment. Two users can potentially request the next number in the time it takes to run DMax. The duplicate will cause it to spit the dummy in an Indexed = Yes (No Duplicates) field.

Consequently it is important to immediately save the record so the new value is added to the table when the number is allocated and then return to edit the other fields.

However the risk of the problem grows as the database grows because the time for the DMax increases with more records. Databases that start out OK can develop problems later. I encourage developers to forget DMax and use best practice because databases have a habit of growing way beyond their humble beginnings and the DMax strategy is a timebomb.

A better way is to store the next number as one record in one field in a table. When a new number is required the user opens a recordset on the table with a lock to shut out other users while it is adopted and incremented by the first user. This is faster than the DMax and never, ever slows down.

The function must allow for the subsequent users to retry until the next value becomes available.

I would still implore you to remove the P from your unique field, storing it as an integer and use the format property as I described above.

Add another field with Number (Long Integer) datatype to your table. Use the Mid or Right function to extract the numeric section and update this new field. Then delete the original and rename the new field to the original name.

 

[Penpoint]

Thanks for your effort it's appreciated.

In relation to gaps, that's not a problem because it's not for an invoice. The only condition is that it has to be unique so that I can reference it in other documentation, which when I do a search function across a series of indexed folders on Windows O/S it can find all associated files. So gaps are no issue.

And also there is only me who will access this database so there are no multiuser issues currently.

The reason I need letter number combinations is because I want the unique identifier to help me, the user, to distinguish between multiple objects. Some will be parameters so P000001 is code for a parameter item, while an attribute or object could display as ATT000001 OB000001 respectively.

I should probably split all these separate properties of the object and add extra fields into the database to keep some kind of integrity... but still, to manually go back one record, copy the record across and modify the number can become tedious after a while.

Hopefully that makes sense in how I am attempting to develop this database.

 

[Galaxiom]

Separate the prefix into another field and just leave the number in the original field.

Presumably you select the prefix as the first step to enter the record.
In the AfterUpdate event of the control that displays the prefix:

Me.numbercontrolname = DMax("numberfield", "tablename", "prefixfield='" & Me.prefixcontrolname & "'") + 1

You can display the whole unique code on the form or reports with the control source:

 = [prefixfield] & Format([numberfield], "000000")

You should also take steps to prevent it being edited except in the new record. The best way to do this is to not even have the control on the form but write directly to the recordset field. Don't include the bound control for the numberfield on the form and access will find the recordset field by that name.

It is trickier if the record is in a subform. Get your head around the DMax first.