Create Specific Unique Serial Number

[malteselemon]

Using Access 2003, I need to create an automatic Serial Number.
Example "7235-E001"

"7" is current year
"235" is day
"-E" is static
"001" is sequencial, restarting at "001" each day

I set a table field with "=right(Format( Now(),"yy") & Format(Format(N ow(),"y"),"000" ),4)" to create the year/day number.----I should be able to add &"-E"& to add the static "-E"----I tried to add this all together with the "autonumber ", but that obviously failed since the autonumber will not reset each day. Also, I lost the placeholders on the sequencial number "001".

I have little to no knowledge of VB, and if possilbe, would like to keep all expressions regulated to queries and tables and forms. If not possible, I will see what I can understand.

Please keep in mind, I am a complete noob, so speak to me like I am an idiot. Thank you.

[ADezii]

Using Access 2003, I need to create an automatic Serial Number.
Example "7235-E001"

"7" is current year
"235" is day
"-E" is static
"001" is sequencial, restarting at "001" each day

I set a table field with "=right(Format( Now(),"yy") & Format(Format(N ow(),"y"),"000" ),4)" to create the year/day number.----I should be able to add &"-E"& to add the static "-E"----I tried to add this all together with the "autonumber ", but that obviously failed since the autonumber will not reset each day. Also, I lost the placeholders on the sequencial number "001".

I have little to no knowledge of VB, and if possilbe, would like to keep all expressions regulated to queries and tables and forms. If not possible, I will see what I can understand.

Please keep in mind, I am a complete noob, so speak to me like I am an idiot. Thank you.

I could only spend a very short time on this, but this Function should properly generate the next Serial Number per your specifications. There are only a couple of simple assumptions:

1. Your Table Name is tblTest, make the appropriate substitution if necessary.
2. The [SerialNo] Field within this Table is called [SerialNo].
3. The Day component you refer to in the Serial Number is a Julian Date (number of Days from the first of the year).
4. Any questions, feel free to ask.
   [CODE=vb]Public Function fGenerateNextSe rialNumber()
   Dim strCurrentYear As String
   Dim strCurrentDay As String
   Dim strStaticValue As String
   Dim strSequentialNo As String
   Dim strLastSerialNo As String
   Dim strLastSequenti alNo As String
   Dim strNextSequenti alNo As String
   
   strCurrentYear = Right(Year(Now( )), 1)
   strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1
   strStaticValue = "-E"
   
   'get ready to extract the Sequential Number
   strLastSerialNo = DLast("[SerialNo]", "tblTest") 'produces 7235-E001
   strLastSequenti alNo = Right(strLastSe rialNo, 3) 'produces 001
   
   'Generate the Next Sequential Number
   strNextSequenti alNo = Format(Val(strL astSequentialNo ) + 1, "000") 'produces "002"
   
   'Generate the next, Unique, Serial #
   fGenerateNextSe rialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequenti alNo
   End Function[/CODE]

[malteselemon]

This is kind of what I was afraid of. I have no idea where I would put the VB in the database.

[ADezii]

This is kind of what I was afraid of. I have no idea where I would put the VB in the database.

Place =fGenerateNextS erialNumber() in the Default Value Property of the [Serial Number] Field. Anytime you add a New Record, the results of this Function will be placed in this Field.

[malteselemon]

Roger, I will have access to the database tomorrow morning, I will try at that time, thank you. I'll reply back whether I manage or not.

[ADezii]

Roger, I will have access to the database tomorrow morning, I will try at that time, thank you. I'll reply back whether I manage or not.

Just make sure to substitute your Table name for tblTest and your Field Name for SerialNo or else the code will not work.

[malteselemon]

To see if I could get this to work, I created a new table. I named the
table "tblTest. I entered 3 fields. Field 1 - ID (autonumber), Field 2
-
SerialNo (no format), Field 3 - data (no format).

I then went to the VB (Tools/Macro/Visual Basic Editor). I entered the
code as typed. When I opened the VB, it had autopopulated "option
compare database" on the top line, so I left that information in, and
placed the new code below it.

When I added =fGenerateNextS erialNumber into the default data for the
"SerialNo" field, the =fGenerateNextS erialNumber changed to read
="fGenerateNext SerialNumber".

I then changed the table to "datasheet view" and added data to the
"data" field, but the "SerialNo" field populated with
"fGenerateNextS erialNumber".

I went back to VB and deleted "option compare database", but the
"SerialNo" field still populates with "fGenerateNextS erialNumber" when
I add data to the "data" field.

Am I putting the code in the right place, or should I have formated the
"SerialNo" field somehow?
Hopefully I explained well enough.


Thank you.

[ADezii]

1. In Form Design View.
2. Right Click on the [SerialNo] Field.
3. Select Properties.
4. Select the Data Tab.
5. In Default Value, enter =fGenerateNextS erialNumber()

[malteselemon]

My apologies for not getting back to you in a timely manner, things have been hectic. I made the corrections that you pointed out. Now the database is giving me an error on the

strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), , Now()) + 1

line. When opening the serial number table, I receive "Compile error: Arguement not optional: warning, and it highlights the above mentioned script line.

[ADezii]

My apologies for not getting back to you in a timely manner, things have been hectic. I made the corrections that you pointed out. Now the database is giving me an error on the

strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), , Now()) + 1

line. When opening the serial number table, I receive "Compile error: Arguement not optional: warning, and it highlights the above mentioned script line.

It seems as though you inadvertantly added another comma, thus the Error. The correct syntax is:
[CODE=vb]strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1[/CODE]

[malteselemon]

Roger, I will fix the error when I get backto the office on Monday. Thank you.

[ADezii]

Roger, I will fix the error when I get backto the office on Monday. Thank you.

You are quite welcome.

[malteselemon]

Sorry about taking so long to respond.

Thank you for your assistance, and the scripting!

[HAMIZ]

unknown function

Dear ADezii,
When I enter =fGenerateNextS erialNumber(), in default value field property of tblTest.SerialN o and save, I receive notification stating "unknown function '=fGenerateNext SerialNumber()' ". Further more I have added the code in class module of VB window. Can you please help on this issue?

[ADezii]

Dear ADezii,
When I enter =fGenerateNextS erialNumber(), in default value field property of tblTest.SerialN o and save, I receive notification stating "unknown function '=fGenerateNext SerialNumber()' ". Further more I have added the code in class module of VB window. Can you please help on this issue?

Make sure that the Function in in a Standard Code Module and not a Form's Module.