Search code examples
sqlvbams-accessautonumber

Custom user autonumber


I have been searching for a solution to this autonumber problem that I am having. There seems to be no definite answer anywhere.

I have a form which has a text field.

I want this form to display the next number from a field in a table.

Example: the table contains 3 records with the values D001, D002, D003

The form is used to enter new records (new data). So next time I enter data I want D004 to automatically show up on the text field for data code in the form.

How can this be done?


Solution

  • You can use the BeforeInsert event of the form:

    Me!AutoNumber.Value = Format(Val(Nz(Right(DMax("[AutoNumber]", "[YourTable]"), 3), 0)) + 1, "\D000")