Setting ID field format

[sminspiron]

I'm building a simple database at work, I'm stuck on setting the ID field. The ID field needs to be 4 numerical digits dot 2 numerical digits dot 2 numerical digits (####.##.##). The last two digits are for the year, the two before are the month and the four at the beginning need to start at 0113 and increment by one (0114, 0115 etc) with every new record.

I have managed to set the input mask and the field size. But i don't want to enter the ID number manually for every record. I want MS Access to automatically complete and fill in the digits (inc. the present month and year i.e. at the time of entering the record). So a record entered today would be 0113.06.12.

Any help would be appreciated. Thank you in advance.

 

[MarkK]

Hello there, welcome to the forum.

I recommend that you use an AutoNumber field as your primary key. It is simpler and forms a faster link between tables, and provides faster search and sort.

Also, when you store data it is easiest to store it in it's simplest form, then if consumers need it formatted, then format the data at retrieval time. So in addition to an AutoNumber as your primary key, add a 'Created' field which defaults to the current date, storing your month and year, and then you simply calculate your code 0113, 0114, in a separate field, and based on the highest code in the table, + 1.

Then, when you consume that data, construct your human readable code at that time.

SELECT t.Code & "." & Format(Month(t.Created), '00') & "." & Year(t.Created) As HumanCode
FROM tblYourData As t

With this scheme your user doesn't have to enter any data, so you don't need an input mask, and you have your data stored in its most raw, and therefore most efficient, form.

Does that make sense?
Mark

 

[sminspiron]

Yes. Makes sense. Thank you very much.

 

[sminspiron]

Sorry for the trouble, but, I forgot to ask how I can set the first record ID to start at 0113. Also, I didn't understand the part where you mentioned adding 1.

 

[Beetle]

Lagbolt's suggestion was to use an Autonumber field as a surrogate Primary Key for the table for efficiency purposes. This Autonumber field does not replace the field where you are storing 0113, 0114 etc., it is in addition to that field.

So to be clear, the suggestion is you have an Autonumber PK field, plus a text field where you will store the 0113, 0114 values, plus a Date/time field which will default to the current date, plus any other fields you may need. The Autonumber field would never be displayed to a user, it is only for efficient record identification, relationships and indexing. The field where you are storing 0113, 0114 (Let's call it OrderID for argument's sake) would typically be incremented using DMax in the Before Update event of your form, i.e;

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!OrderID = Format(DMax("OrderID", "YourTable") + 1, "0000")

End Sub

Then when you want to display the complete value of 0113.06.12 to the user you would format it from the two separate fields like Lagbolt's example in the previous post.

 

[MarkK]

Thanks Sean.
Sminspiron, did you get this working?
Mark

 

[sminspiron]

Nope. Sorry. Doesn't work. Nothing seems to appear in the field.

 

[MarkK]

OK. If you want more help with it, please post questions and code, if any, for instance, did you implement the table as per the details in beetle's post? Which field does nothing appear in? And so on...
Cheers,