Custom user autonumber

Question

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?

Answer 1

That is 1 way to do this is create a function to handle the autonumber problem u had

create function NextAutoNumber() 
returns char(4) 
as 
begin 
    declare @lastval char(4) 
    set @lastval = (select max(autoNumber) from table) 
    if @lastval is null set @lastval = 'D001' 

    declare @i int set @i = right(@lastval,3) + 1 return 'C' + right('00' + convert(varchar(10),@i),3) 
end

like this you can just call the function anytime and insert the auto number you need for the record

Answer 2

You can use the BeforeInsert event of the form:

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