Auto Numbering Help

[whjwilson]

Access newbie needs alot of help.
I need help trying to get the Autonumbering feature to work right for what I need it to do. I don't know if it is not working because I have letters included in the numbering or what. But this is what I am trying to do, I want the field to automatically go up one with each new entry starting with FY06-001 is this possible even if the field contains letters?

 

[Iashia06]

No. Autonumber fields can only contain numbers. Access increments the number based on the maximum value on file. You need to create the field as Text, and program the incremental values behind the scenes.

 

[PHV]

Have a look at the DMax function and the BeforeInsert event of the Form object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[formerTexan]

Hi Folks,

I apologize for interjected and that said I will carry on since it is sort of On Topic.

PHV,

Is there a particular reason you have a preference for using DMAX as opposed to "SELECT TOP 1... ORDER BY .." for retrieving the last value?

Cheers,
Bill

 

[stix4t2]

My two cents is that the DMAX being a inline function would have better performance with bigger data sets. Plus DMAX will return a value and not a recordset.

Though DMAX is a good solution, you will need to parse out the number part of the string field, increment the value and build the new string

if( me.newrecord)then
str = dmax(fld, tbl)
val = cint(right(str,3))
val = val + 1
fld = left( str, len(str)-3) & format(val, "000")
end if

 

[sdk]

Hi

In the format field of the attribute, put in "FY06-"0000

BUT NOTE
[ul]
[li]FY06- will always appear first (or whatever value you put in). It won't store this as part of the field, so you can edit it later if you need to. As an alternative, split the field into two, one with the default value of say [green]"FY" & DatePart("yy",Now()) & "-"[/green], and the other with the autonumber. This way the values will be stored and auotmatically update for the current (calander) year. If that's what you want.[/li]
[li]If you are using this as a primary key and it means something to the user, you should not use autonumbers, as they often create dramas when they are out of sequence (ie numbers are missing). This would need some coding as partially described above[/li]
[/ul]


Cheers

 

[fneily]

Here are the main ways to do autonumbering. You would want the last.

Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.