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?
You can use the BeforeInsert event of the form:
Me!AutoNumber.Value = Format(Val(Nz(Right(DMax("[AutoNumber]", "[YourTable]"), 3), 0)) + 1, "\D000")