Auto Increment Numbers when opening a form

[dpofarre]

Question from an Access Newbie here:

I have an entry field for one of my forms in the database. It is a specialty number, which I would like Access to fill in when a new record is to be entered. The format is as follows:

Number = MMYSSS where:

MM = two digit month (10 = October)
Y = equal to year (1 = 2001 or 2011 or so on based on the actual year)
SSS = three digit sequence beginning at 001 (this resets with each
new year)

I want Access to, based on the current date, and the sequence number it is on, to create this number, but also allow it to be edited if the user needs to. I have no idea how to build this event. Furthermore, or if no one can answer the first part, should I set this event to "On Got Focus" or on "On Enter"? I have no idea what to do to even get started, and I only know simple Access database design. Thank you for your help,

Dustin O'Farrell

 

[SydneyGeek]

Hi Dustin,

This snazzy number of yours -- do you want it to be unique? And will you be using this number to connect with data in other tables (in other words, is it going to be a key field?). If so, letting users edit the number is not going to be a smart idea once the record is created.
I have had a to do similar things iwth text strings before, and I acieved it by creating a special form that puts the key field together for you, letting you alter the value until you are happy with it. Once the record is created, however, the user cannot change it.
Another comment -- the resulting field is best kept as a Text field, not a Number field, because you are unlikely to be performing calculations with the field value.

An idea for putting together the key from 3 bits of information:

[MyField]=format(Date(),"mm") & format(Date(),"yy") & format([IncrementField],"000")

This will give you MMYYSSS in your notation -- I don't know a clean way to produce a single-digit year without getting messy.

If you can give a bit more info on what the field value will be used for, I should be able to give you a bit more help.

Denis

 

[SydneyGeek]

Just had another thought...

[MyField]=format(Date(),"mm") & format(Date(),"yy") & format([IncrementField],"000")

If you are already on an existing record you could change the above to

[MyField]=format(Date(),"mm") & format(Date(),"yy") & format(right([MyField],3)+1,"000")

You could trigger this with a button (Place the code into the OnClick event):

Dim MyNum as Integer
MyNum=Right([MyField],3)*1 'this should convert the text to a number.

DoCmd.GoToRecord,acNewRecord 'create the new record

[MyField]=format(Date(),"mm") & format(Date(),"yy") & format(MyNum+1,"000") 'place the new code into the latest record

HTH

Denis

 

[bat17]

I would store these as seperate fields and combine them for display. you could then use the Dmax function to find the higest serial number used that year and add 1.
you will not be able to use this as a unique identifier as the numbers will start repeating after 10 years!