AutoIncrement

[colmtourque]

I have a form based on the following table: [Id Number],[fname], name] etc...
[id Number] is based off an employees id number and can have any of several different prefixes. This table also holds recruits whose prefix is r and a number (r1,r2,r3,r4 etc...)
On the form I would like to have an autoincrement whenever someone advances to data entry based on what the last r# is. I have seen how Dmax can do this, but I am confused as to what the proper usage. I have seen several messages on the subject but none where the field can have any of a number of different prefixes.
Thanks in advance.

 

[Stew]

Basically, DMax will find the largest value in a number field in a table or query. Your format for creating an id will cause problems because it is obviously text. You will need to break down the value to apply DMax. I use a similar method to create a family id in a table (format f1234).

varnum = Nz(DMax("Val(Mid(family_id, 2, 4))", "family"), 0) + 1
Me.family_id.Value = "F" & Format(varnum)

The Mid function takes the family_id field, starts with the 2 digit and moves four digits (captures the 1234). The Val function converts this value to a number. DMax finds the largest of these numbers. At the end, 1 is added to it. This gives you the next number to use.

The second line places the f in front of the new number to create the next id that will be used (f1235). You may have to use some code to determine the letter to use if it will change.

Paul

 

[colmtourque]

Thanks that makes sense, but how do I use this as a macro or module attached to a button "add record" is there a way I can do it automatically if the form is advanced to a new record?

 

[Stew]

I usually set a form's allowadditions property to no because I want certain code to happen when a new record is added. I place a button on the form (Add Record button) with code similar to the following:

Form.AllowAdditions = True 'allows records to be added
DoCmd.GoToRecord , , acNewRec 'moves to new record
Me!txtnum.DefaultValue = Nz(DMax("Val(Mid(family_id, 2, 4))", "family"), 0) + 1
Me.txt_two.Value = "F" & Format(Me.txtnum)

The code will move to a new record then calculate the next id number and place it in a field. Of course, there are many ways to execute the code including using an If statement:

If me.newrecord then
'place code to find new id number here
end if

Hope this helps

Paul