Create a serial number which automatically resets every month (Access 2003) (1 Viewer)

vicsar

https://about.me/vicsar
Local time
Today, 07:24
Joined
Jul 25, 2012
Messages
35

I am at lost here (Access 2003).

This is what I am trying to accomplish I need to be able to create a serial number which automatically resets every month so I may use it to easily know how many records were created per month, without running a report.
This number will be saved in the database under a field named Month_Production_Counter (or something like that) and later used to name the backup documentation associated to the record in question.

F
or instance, if in January we created 4 records and I am at record number 4 then the file name for the backup documentation should be named (manually) January - 4, regardless of unique identifier assigned by access. When February arrives the counter will go back to 0 and start counting every new record processed in February. This means that the number 4 will eventually repeat but it will be OK because it is the 4th record for February.

Thank you all for your feedback, ideas, solutions et al.

 

jdraw

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Jan 23, 2006
Messages
15,362
That really is not the purpose of a serial number.
If you have a parts table, you can count the number pf parts manufactured between dates in a query. It has nothing to do with serial number necessarily.

I think you may wish to step back from what you currently have and list the various facts that your database should support. Then design your tables accordingly.

I'm not saying you can't do what have requested, I'm just saying there are many options to determine "production between dates" that don't require a custom serial number.
 

vicsar

https://about.me/vicsar
Local time
Today, 07:24
Joined
Jul 25, 2012
Messages
35
Hello jdraw :),

Please find my comments to your reply below, in green.

That really is not the purpose of a serial number.
-I know, but that is what my company requires. It is the way they have this setup

If you have a parts table, you can count the number pf parts manufactured between dates in a query. It has nothing to do with serial number necessarily.
-I know, but I must assign and ordered, unchangeable, unique, number to each record per month so my co-workers copy it to the backup documentation file name and so the managers are able to easily see how many records were created during the month without accessing the database, just by going to the shared folder and looking at the file names.

I think you may wish to step back from what you currently have and list the various facts that your database should support. Then design your tables accordingly.
-I tried that approach already but the managers declined most of my proposals related to this particular field (can't give specific examples due to business regulations)

I'm not saying you can't do what have requested, I'm just saying there are many options to determine "production between dates" that don't require a custom serial number.
-Correct, there are simpler ways but all my ideas were rejected, they insist in having this part of the process setup in the way I explained in the original post.

Please could you share some of your ideas in how to go about that? The key here is that the serial (or counter) must be reset every month. I am attaching an excel file to you may the the idea behind this, that is how the final report/table should look like (columns A to D) and finally, in column F you can see what the final file name normally looks like. Notice that the fill name is just joining columns B, C, and D. I am having trouble creating column C ... Could the solution involve VBA? If so what routines do you recommend I use.

Thanks looking forward to your input ,



 

Attachments

  • final-product-sample-lines.xls
    16 KB · Views: 302
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Jan 23, 2006
Messages
15,362
My first idea is why not stick with Excel, if that's your product of default.

I see in your xls the AccessAutoNumber is that really the autonumber ID field from your database?

I did some google searches and found this general approach
http://www.baldyweb.com/CustomAutonumber.htm

Good luck
 

vicsar

https://about.me/vicsar
Local time
Today, 07:24
Joined
Jul 25, 2012
Messages
35
Yes, Excel... that's another dimension to the problem, see Excel is wonderful when you need to calculates stuff but it falls short when it comes to relate information :) . We are moving stuff to Microsoft Access to be able to get the information quickly, currently we have over 200 Excel files scatter thru different servers so getting historical data, to name one, is nearly impossible or a nightmare at best.

Let me check on link you referenced and then I'll get back to you. In the mean time if you or someone else comes up with something, am all ears.

Thanks.

P.s.:
We already have a macro setup in Excel to create this counter but I am finding very hard to adapt it to Access, if your suggestion above does not solve the problem I will get back to you with a modified version of the Excel macro (one that I can make public) so you may take a look at it.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Jan 23, 2006
Messages
15,362
I was looking at the issue and created a function to create the custom serial number. I adjusted this a little because you didn't mention Year, only Month.

I have set it up to produce numbers like

YYYYMM-serialNo 201301-5

See the attached jpgs for the table set up.

It does require a table that I called tblMyAdminData with fields
' CurrentYearMonth Text
' LatestSerialNo Number

You have to create the table.

'There is only one record in the table.
'Preset the table with the starting YearMonth and SerialNo. I used 2/2/2000, and adjusted the date in the test procedure.
'The LatestSerialNo is updated/incremented when a new SerialNo is generated.
'
'However, if the month has changed since last run,
'the Year and Month are updated and
'the LatestSerialNo is reset to 0
'
The following code is for the function and a test routine.

The function is listed below:
Code:
'-----------------------------------------------------------------------------
' Procedure : CustomSerialNo
' Author    : Jack
' Date      : 21/01/2013
' Purpose   : A function to generate a custom serial number made up of
'      Year and Month in YYYYMM format
'      -
'      a sequential number that resets on the change in Month
'
' Parameter:  The date of production/manufacturing or whatever.
'
'***********************************************************
'
[B][COLOR="Green"]' This function uses a table named  --tblMyAdminData--, with fields
' CurrentYearMonth  Text
' LatestSerialNo    Number
'
'There is only one record in the table.
'Preset the table with the starting YearMonth and SerialNo.
'The LatestSerialNo is updated/incremented when a new SerialNo is generated.
'
'However, if the month has changed since last run,
'the Year and Month are updated and
'the LatestSerialNo is reset to 0[/COLOR][/B]
'
'---------------------------------------------------------------------------------------
'
Function CustomSerialNo(MyDate As Date) As String
    If Len(MyDate) = 0 Then Exit Function
    If Not IsDate(MyDate) Then
        MsgBox "Invalid Date supplied"
        Exit Function
    End If
    If Year(MyDate) = 1900 Then 'if only a day is supplied, Access may assume 1900???
        MsgBox "Bad Date supplied"
        Exit Function
    End If
    Dim mySer As String
    Dim CurYearMonth As String    'CurrentYearMonth from tblMyAdminData
    Dim HiSer As Long    'LatestSerialNo from tblMyAdminData
    Dim SQL1 As String    'Update for New Month
    Dim SQL2 As String    'update of LatestSerialNo

    On Error GoTo CustomSerialNo_Error



    'get the LatestSerialNo and yearMonth fromtblMyAdminData

    HiSer = DLookup("LatestSerialNo", "tblMyAdminData")
    CurYearMonth = DLookup("CurrentYearMonth", "tblMyAdminData")    'format is YYYYMM
    'Debug.Print CurYearMonth 'for debugging
    'Debug.Print HiSer        'for debugging

    'CHECK if the MONTH has Changed***********
    If Year(MyDate) & Format(Month(MyDate), "00") <> CurYearMonth Then
        'when the month changes
        '  update the tblMyAdminData with
        '  the proper Year and Month
        '  reset the LatestSerialNo to 0

        SQL1 = " Update tblMyAdminData " _
             & " SET CurrentYearMonth = '" & Year(MyDate) & Format(Month(MyDate), "00") & "', " _
             & " LatestSerialNo = 0"
        '   Debug.Print SQL 'for debugging
        CurrentDb.Execute SQL1, dbFailOnError
    End If

    'Now get the values reflective of the Changed Month if there has been an update

    HiSer = DLookup("LatestSerialNo", "tblMyAdminData")
    CurYearMonth = DLookup("CurrentYearMonth", "tblMyAdminData")    'format is YYYYMM

    HiSer = HiSer + 1  'increment the number
    SQL2 = "Update tblMyAdminData SET LatestSerialNo = " & HiSer
    'Debug.Print SQL2  'for debugging

    CurrentDb.Execute SQL2, dbFailOnError    'update the tblMyAdminData LatestSerialNo
    mySer = CurYearMonth & "-" & HiSer
    'Debug.Print mySer  'for debugging

    CustomSerialNo = mySer
    'MySer is the value you would assign to the new product

    On Error GoTo 0
    Exit Function

CustomSerialNo_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CustomSerialNo of Module Module1"
End Function

The test procedure
Code:
Sub testCustomSer()
' use this to test the customSerialNo function
' you can change the mdate and you can run this test proc repeatedly to see the serial numbers generated.
    Dim mdate As String
    Dim DDate As Date
   On Error GoTo testCustomSer_Error

    mdate = "11/3/2011"  'mm/dd/yy
    If Left(mdate, 2) > 12 Then
        MsgBox "Bad Month supplied " & Left(mdate, 2)
        Exit Sub
    End If
    DDate = CDate(mdate)
    
    ' this check is put in here because access will assume current year if no year is supplied
   If Len(mdate) < 7 Then
   MsgBox "Date is not full date"
   End If
    Debug.Print CustomSerialNo(DDate)

   On Error GoTo 0
   Exit Sub

testCustomSer_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testCustomSer of Module Module1"
End Sub

Good luck with your project.
 

Attachments

  • tblMyAdminData_design.jpg
    tblMyAdminData_design.jpg
    17.7 KB · Views: 583
  • tblMyAdminData_data.jpg
    tblMyAdminData_data.jpg
    10.9 KB · Views: 501
Last edited:

DavidAtWork

Registered User.
Local time
Today, 13:24
Joined
Oct 25, 2011
Messages
699
If you're still looking for a solution, can you not modify your table to include a year field and each time a new record needs to be added, you first check to see if any records exist for that month in that year using DCount method and if > 0 do a DMax using the same criteria and add one for your Month_Prod_Counter value, else the Month_Prod_Counter value will be 1. The backup filename is just a concatenation of field values.
David
 

vicsar

https://about.me/vicsar
Local time
Today, 07:24
Joined
Jul 25, 2012
Messages
35
DavidAtWork
Thank you very much for your suggestion, I saw it after I had already began implementing the previous one although I will not use it now I will definitely archive this for future reference.

jdraw
Seen your work and creative approach was inspiring I took your code and suggestions and made several changes to it to fit my application and now it works like a charm.

Thanks for taking the time to help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Jan 23, 2006
Messages
15,362
Glad you have it working -- happy to help.
 

Agodf007

New member
Local time
Today, 09:24
Joined
Feb 25, 2017
Messages
7
How would I use dmax and count to make a product id and, in this way, would multiple users be able to use the same ID?
 

Users who are viewing this thread

Top Bottom