How to use Autonumber & dates to create unique ID

[Miranda Truehill]

I need to create a custom field that uses the month and year (0810) in combination with an auto number system which resets each month. So each time a record is created within a month the numbers will reflect this: 01-810, 02-810, 03-810; 01-0910, 02-0910 etc.

I saw a post that addressed this with programming language but I'm new at this and wonder if there's another way of doing it?

Thanks!!

[Anton Melamed]

Hi Miranda,

Create a text box, highlight it and in properties in Data tab in Control Source type:

=Format(Date(),"mmyy") & Format([ID],"000")

Where "000" is a number of digits used for unique ID added after the date, so if you need a bigger number just use more 0's

[Steven Kogan]

You'll need to use some sort of programming to do this.

If you are not comfortable with programming in VBA you may want to consider using a standard autonumber field that does not reset every month, and storing the date of the record in a separate date field. Then you can create reasonably simple queries to answer questions such as how many records were created in a particular month as well as how many records were created in the current month. Even if you are comfortable with programming, I would still consider this as a good alternative.

Otherwise, can you provide a link to the solution you already found? It may be a good starting point.

The solution would involve vba code that uses SQL to find the maximum number for the current month and add one to it. If there is none, then 1 would be assigned. The formatting of the key field would be as Anton described. The code would probably be placed in the before update event of a form.

If it is a multi-user database you'd have to handle the possibility that two users can generate the same ID if they are creating a record at the same time.

You should consider creating a key that can be easily sorted. For example, using the format yymm-#### would produce a key that sorts properly.

[NeoPa]

Miranda, your posted data is inconsistent. I'll assume for now that the August items, like the September ones, will contain four digits rather than three. It is critical (much easier) for the data to be consistent if you want to sequence your data within each month.

You don't specify, but I'm guessing you want this value to be created for you on a form for when entering a new record?

PS. Actually Steven's post handles pretty well everything well. Key layout would be much better as he suggests. I would consider using the Form_BeforeInsert event though. It is actually not quite correct to show the value before it is created for two reasons :

1. Logically, the value has not yet been assigned. It is only a best-guess at this stage, and makes little sense to show this to the operator.
2. It is also possible that the record addition will be cancelled or delayed. Until the record is saved there is a very real potential for the same value to be assigned to another record (which would not be good).

If it is important for the operator to make a note of the value assigned then a MsgBox() with the information could be displayed within the Form_AfterInsert event procedure, always assuming that's even necessary. Newly added records are often (not always) visible on the same form.

[Miranda Truehill]

Anton - I tried to enter the code but obviously didn't do it right. It gives me an error.

NeoPa - I do want the value to be created on a form when entering a new record. What I'm confused about (apologies for being slow) is where to enter the proper code and at what point in the code should Form_BeforeInsert go?

[NeoPa]

Miranda Truehill: NeoPa - I do want the value to be created on a form when entering a new record. What I'm confused about (apologies for being slow) is where to enter the proper code and at what point in the code should Form_BeforeInsert go?

Technically, it doesn't matter where in the code the new procedure goes. If you go into Design View of the form you will see, in the Properties pane, a Before Insert property. Go there and select [Event procedure] from the drop-down and click on the elipsis button (...). This will open the code window with the stub of the procedure prepared for you.

It should be easy from there. Shout if you have any trouble :)

[Miranda Truehill]

So I decided to split the fields. The second has the date in the format I need. The first will have the auto-numbering which will start from 0 at the beginning of every month. Can someone give me the exact code for that so I can just paste into the field?

Thanks guys!!!!

[NeoPa]

We can't really write copy/paste code without any meta-data Miranda. If you can describe what your fields are named and what the controls on the form are named then we can see what we can do for you.