automatic serial number which is based on current date

[dkaru]

I am looking to automatically generate serial numbers for my manufactured equipment which is based on the current date I currently generate these manually based on yymmdd-001 can someone point me in the right direction to have this generate automatically. I need to make equipment labels with this data so I deisgned a form which once it is populated we print the label

[colintis]

Have you tried out any attempts with VBA environment? Such as on the event you start the new equipment record, it will generate the serial number automatically.

I'll answer more once I get the answer from you, otherwise it'll be difficult to continue on.

[dkaru]

I have not done much in the VBA environment so I am not usre what you mean. My knowledge on this end is limited but I pick things up really quick. I saw some other posts on doing this but they are all slightly different and I seem to be missing something in the table when I try it.

[colintis]

ok, for the VBA environment, you can learn more in here.

In your VBA code, you can set up something like this:

tbSerial.Value = Format(Date(), "yymmdd") & "-" & <001>

The <001> in the code line is the auto number which checks back any other items been added on date. I'll get back to you with this part later, as there's more for this part, but this line of code is the one you'll need to get the automatic serial number generation.

[colintis]

For the <001> I mentioned earlier, it'll be as simply as putting another few lines of codes below including the VBA code I posted earlier.

Dim SerialNum As Integer
 
SerialNum = DCount("<serial number field>", _
                   "<Table name>", _
                   "LEFT(<serial number field>,6) = FORMAT(Date(),"yymmdd"))"
 
tbSerial.Value = Format(Date(), "yymmdd") & "-" & SerialNum+1

The DCount function is to count the number of new equipments made on that date, and plus one for the new serial number.

You can put this part of code into the On Load event of your form, or put it into a button's On Clicked event on your desire.