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(Now(),"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(Now(),"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.
   

   Public Function fGenerateNextSerialNumber()
   Dim strCurrentYear As String
   Dim strCurrentDay As String
   Dim strStaticValue As String
   Dim strSequentialNo As String
   Dim strLastSerialNo As String
   Dim strLastSequentialNo As String
   Dim strNextSequentialNo 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
   strLastSequentialNo = Right(strLastSerialNo, 3)         'produces 001
    
   'Generate the Next Sequential Number
   strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "000")   'produces "002"
    
     'Generate the next, Unique, Serial #
     fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequentialNo
   End Function

[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 =fGenerateNextSerialNumber() 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 =fGenerateNextSerialNumber into the default data for the
"SerialNo" field, the =fGenerateNextSerialNumber changed to read
="fGenerateNextSerialNumber".

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

I went back to VB and deleted "option compare database", but the
"SerialNo" field still populates with "fGenerateNextSerialNumber" 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 =fGenerateNextSerialNumber()

[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:

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

[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]

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

[ADezii]

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

[HAMIZ]

Yes, It is in Standard Code Module.
it works fine in "LOCALS" VB window and gives correct result.

Along with my last statement, It also gives error for strLastSerialNo stating "Run Time error '94' Invalid use of NULL" if there is no any record in the said table, i.e. we have write 1st value in [SerialNo] manually.

[viveikb]

Hello,

I would like to generate a unique Serial number for each record set.

The Format is:
Year-String-Counter ----- Format = YYYY-PPAP-0000

Where the table Name = tbl_Detail_PPAP
the field = SequenceNumber

This is my code:

Public Function fGenerateNextSerialNumber()

Dim SequenceNumber As String
Dim strCurrentYear As String
Dim strStaticValue As String
Dim strSequentialNo As String
Dim strLastSerialNo As String
Dim strLastSequentialNo As String
Dim strNextSequentialNo As String



strCurrentYear = Right(Year(Now()), 4)
strStaticValue = "-PPAP"

'get ready to extract the Sequential Number

'If SequenceNumber = 0 Then

strLastSerialNo = DLast("[SequenceNumber]", "tbl_Detail_PPAP") 'produces 2016-PPAP-0001

strLastSequentialNo = Right(strLastSerialNo, 4) 'produces 0001

'Generate the Next Sequential Number
strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "0000") 'produces "0002"

'Generate the next, Unique, Serial #

fGenerateNextSerialNumber = strCurrentYear & strStaticValue & strNextSequentialNo

DoCmd.RunCommand acCmdSaveRecord

'End If

End Function

I have followed the exact procedures as suggested above. The code is in the Standard module. When I run the code, I get the following error "Invalid use of Null (Error 94)" on the code line: strLastSerialNo = DLast("[SequenceNumber]", "tbl_Detail_PPAP")

Please help me..