Sorta Random Serial Number Generator

[Cuselco]

Please speak to me as a child since I am a complete tool with access, and know just enough to get by :-)
I have created a basic database with only a couple tables. In the main table which I have created a form to fill some information and some items look up from another table ect. . . . No problem. But, what I would like to do is have a button that automatically generates a serial number on the main form for that record when clicked. It would be nice to have 2 seperate buttons for 2 seperate fields for this serial number generator.

The format that I would like it to follow would be
( for serial generator 1 )

WWYYY-P-88-ZZZ
Example: Where the WW is the current year, 20"10" YYY is the day number of the same year (Julian day?), P and 88 is static and ZZ would be a progressive number that would reset every day beginning at 001.
so, 10208-P-88-001 would be the first product produced on Aug 28 2010, or 10210-P-88-015 would be the 15th products produced on August 30, 2010 and so on. . .

( for serial generator 2 )
The same, except the P-88 statics would be replaced with S-5.



Is this making sense?
Thanks for any and all help in advance!

[weekend00]

assume your table name is "TableName", serial number field name is "SN", textbox1 is the control you want to put the serial number.

use following code in the click event of a botton.

Private Sub Command2_Click()
Dim serialNo As StringFormatEnum
serialNo = Format(Now, "yy") & Format(DatePart("y", Now), "000") & "-P-88-"
serialNo = serialNo & Format(Nz(DMax("SN", "tableName", "SN like '" & serialNo & "*'"), "0"), "000")
textbox1 =serialNo
End Sub

[Cuselco]

Just tried it..... when i click the button (command2) I get the error-
"Compile Error - User Defined type not defined" ??
This was highlighted in the vb code:
serialNo As StringFormatEnum

Something I did wrong?

[weekend00]

remove the red thing. it's a typo.

Private Sub Command2_Click()
Dim serialNo As StringFormatEnum
serialNo = Format(Now, "yy") & Format(DatePart("y", Now), "000") & "-P-88-"
serialNo = serialNo & Format(Nz(DMax("SN", "tableName", "SN like '" & serialNo & "*'"), "0"), "000")
textbox1 =serialNo
End Sub