By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,885 Members | 1,499 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,885 IT Pros & Developers. It's quick & easy.

automatic serial number which is based on current date

P: 2
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
Jan 7 '11 #1
Share this Question
Share on Google+
4 Replies


100+
P: 255
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.
Jan 7 '11 #2

P: 2
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.
Jan 7 '11 #3

100+
P: 255
ok, for the VBA environment, you can learn more in here.

In your VBA code, you can set up something like this:
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 7 '11 #4

100+
P: 255
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.
Expand|Select|Wrap|Line Numbers
  1. Dim SerialNum As Integer
  2.  
  3. SerialNum = DCount("<serial number field>", _
  4.                    "<Table name>", _
  5.                    "LEFT(<serial number field>,6) = FORMAT(Date(),"yymmdd"))"
  6.  
  7. 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.
Jan 7 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.