A table with Auto number generation field

[Irving Guy]

Hi all,

I’m trying to create a form with 15 fields that generates an auto number in one of those 15 fields. The auto generated number which should display like this – “081-2008-00001” “081-2008-00002” “081-2008-00003” and so on.
The first 3 characters of those numbers are pulled in from one of the field, namely “Building #”. The second set of 4 digit numbers are nothing but the current year which gets pulled from another field namely “Current Date” in the mm/dd/yyyy format (I need to pull only the last 4 digits of the date which is the year).
The Last 5 digits are auto generated numbers assigned by the system.
How do get this logic working for me?
Also, there are several other records which may have different building numbers (for ex. 41, 43, 50, 55, 75, 78, and so on). The last 5 digit auto generated numbers could be consecutive but the first 3 digits should be pulled from the “building #” field and the next 4 from the “current date” field.

Please help!

Thank you,

Irving Guy

[Stewart Ross]

Hi. The most useful way to generate a representation of a compound key (one that combines a number of key fields together to form a unique combined ID) is to create a query with all the fields from the table, then add a calculated field to the query. The calculated field can show the fields you mention combined together in the format you require.

The calculated field would be along the lines of:

CompoundKey: Format([Building#], "000") & "-" & Year([Current Date]) & "-" & Format([autonumber field], "00000")

On your form you then can add the new CompoundKey field, setting its properties to Enabled=No and Locked=Yes to prevent users from trying to change its (non-changeable) value.

I am assuming that you already have an autonumber field in your table. If you don't, you can create a numeric field of type Long and increment that, setting its default value as:

Nz(DMax("[long field]", "[your table name]"), 0) + 1

Autonumber fields have some properties that are considered unsuitable for use where the number is supposed to mean something to those who might see it. For example, autonumbers end up not entirely consecutive (if, for instance, a record addition is cancelled the autonumber is still incremented and the number that was generated is skipped).

-Stewart

[Irving Guy]

Stewart,

I'll try it out and let you know if that works! Thanks for your time and help!

Irving Guy