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?

Which DBMS are you using? Which programming language are you using? — , Feb 23 '16 at 10:34
sorry i did not mention that.... Access 2010... VBA and SQL.... — saranmc, Feb 23 '16 at 10:59

score 0 · Answer 1 · answered Feb 23 '16 at 10:35

0

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

answered Feb 23 '16 at 10:35

Dean

668
12
32

How do you know which DBMS saranmc is using? And for which DBMS is your answer? – Feb 23 '16 at 10:38
yes my mistake... can this be used in Access?? as VBA?? – saranmc Feb 23 '16 at 11:02

Gustav · Accepted Answer · 2016-02-23T16:58:14.853

0

You can use the BeforeInsert event of the form:

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

edited Feb 23 '16 at 16:58

answered Feb 23 '16 at 12:11

Gustav

53,498
7
29
55

thanks Gustav.. works perfectly.... but... only Problem Comes when entering the first value... it Fails when the table is empty... so D001 cannot be entered.... also it did not work with the BeforeInsert Event so i used it in the Form Open Event.... – saranmc Feb 23 '16 at 12:52
OK. Added Nz to take care of an empty table. See edit. – Gustav Feb 23 '16 at 16:58
ahh.. perfect!!! now it works pefectly... just what i wanted... thanks a lot Gustav!!!! – saranmc Feb 24 '16 at 09:40

Custom user autonumber

2 Answers2

Linked