Generating unique ID using year/month

[Integrate]

Hi, I have used the codes (found on forums) below to generate job numbers. The job number is to be yyyymm/#


A job is booked for August, generated ID = 20158/1, then one is booked for September = 20159/1
But if a job is booked for another date in August after the September booking the ID reset to one. Making the second booking 20158/1 as well.

Is there anyway that I can make sure that all bookings in August continue sequentially instead of resetting if a booking is made in another month?

Private Sub Generate_Click()' 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 = Nz(Me.JobDate) 'mm/dd/yy
    '"[DelDate] = #" & Me.[DelDate] & "#"
    If DatePart("m", mdate) > 12 Then
        MsgBox "Bad Month supplied " & DatePart("m", mdate)
        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
    Me.JobNumber = CustomSerialNo(DDate)


   On Error GoTo 0
   Exit Sub


testCustomSer_Error:


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

Option Compare Database

' 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
'
'---------------------------------------------------------------------------------------
'
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) & Month(MyDate) <> 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) & Month(MyDate) & "', " _
             & " 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

[orange]

Why did you decide that a unique code should have this format? yyyymm/#

Relational database works very well with: one fact one field.
My suggestion - save yourself a lot of unnecessary codification. One fact in one field. If you must concatenate some fields to make something meaningful to you and others, then do so.

You can have a JobNo based on Max(JobNo) +1, and ProposedDate, ActualDate fields etc. Keeps things quite simple.

I am not saying you can't do it your way. You can. But most experienced developers wouldn't.

I would recommend getting all of the business facts identified; create a data model; test the model(adjust as necessary) with some sample data. Once the model supports the business- build the database.

Working out an algorithm for custom codification of JobNo would not be a priority.

Also: I like the way you have commented and formatted your code. Your naming seems very consistent and easy to follow. You may want to acquire these free utilities that have proven very handy. MZTools for VBA and Smart Indenter

Good luck with your project however you decide to proceed.

[June7]

In spite of conventional wisdom, I also use code to generate a custom unique identifier. Review https://www.accessforums.net/forms/a...ing-23329.html

Your code will have to do a lookup to find the latest identifier for a given year/month and then parse the value and increment.

[Integrate]

Thank you for your advice orange. This is not the way I would like to set up the numbering but the client is using an historical (paper based) numbering system of the month and then an incremental number. They don't want to change. This was fine when they were entering the job number manually but now they want the database to generate the job number. I managed to get them to add the year to avoid duplicate numbering each year.

I tried the dlookup option suggested by June7 but got completely lost so I did some fiddling with the function and got it to add a new CurrentYearMonth string into tblMyAdminData if one doesn't exist, which works but I am struggling on what is probably a simple issue with the Update of LastestSerialNo. It updates all the LastestSerialNo. I have tried adding a WHERE statement SQL2 but this has proved my undoing.

 SQL2 = "Update tblMyAdminData SET LatestSerialNo = " & HiSer & " WHERE (CurrentYearMonth = 'CurYearMonth')"

Debug shows: Update tblMyAdminData SET LatestSerialNo = 9 WHERE (CurrentYearMonth = 'CurYearMonth')

Am I on the right track, sort of...?

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) & Month(MyDate) <> CurYearMonth Then
        'when the month changes
        '  update the tblMyAdminData with
        '  the proper Year and Month
        '  reset the LatestSerialNo to 0

        'SQL1 = " Undate tblMyAdminData " _
         '    & " SET CurrentYearMonth = '" & Year(MyDate) & Month(MyDate) & "', " _
          '   & " LatestSerialNo = 0"
        SQL1 = " Insert INTO tblMyAdminData (CurrentYearMonth, LatestSerialNo) Values ('" & Year(MyDate) & Month(MyDate) & "', " _
             & "0)"
         Debug.Print SQL1 '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 & " WHERE (CurrentYearMonth = 'CurYearMonth')"
    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

[June7]

Your DLookup is not looking for the latest LatestSerNo for a given year/month. Actually, what you probably need is DMax()

HiSer = Nz(DMax("LatestSerialNo", "tblMyAdminData", "Left(LatestSerNo, 5) = '" & Me.tbxYear & Me.tbxMonth & "'"), "")

Exactly what record needs to be updated with HiSer? Is it the record just inserted with a value of 0 for LatestSerNo? Why set with 0?

[Integrate]

I got this code online, I guess it is set with 0 because the rest of the code adds 1.
I want HiSer to only update the LatestSerialNo for the year/month of the entered job date. Which it looks like your Dmax code does, but, stupid question sorry, how do I get tbxyear and tbxmonth?

example of what I need: 15 jobs entered for August and 10 entered for September. After entering a september job, they enter a job for August. This job number should then be 20158/16
If a job is entered for september after that the job number should be 20159/11
tblMyAdminData should then show like this:

CurrentMonthYear LatestSerialNo
20158 16
20159 11

Does that makes sense?!

[orange]

Rarely is finding some code online the best strategy to solve your problem.

A little soap box - please bear with me.

When you are starting out with Access and database generally, it is not like Word or Excel. There are underlying concepts - Normalization, naming convention ..... to be considered. It is much like building a house or a shopping center. Having someone drop off some steel, wood and concrete is not a good first step --but that is the analogy when given Access software and nothing more. What exactly are you going to build? A house? Bungalow, ranch, duplex? A shopping center??? Do you have a plan? Have you thought about infrastructure --water, electric, gas, sewers...? These are not after thoughts. Similarly, you don't become a surgeon because you buy a scalpel.

Back to database -Start with a concise description of your issue/opportunity in plain English; follow up with a picture of the things involved; identify what the relation of these things is one to another; confirm your picture with some sample data.
If you are confused or have come to a roadblock, you are more likely to get help if you tell readers what you are trying to do and show them your plain English description. Readers do not want to help you debug code you found on the internet especially if you have no plan and have no idea what the code does. And readers are not clairvoyant nor all-seeing so can not guess at what you mean. Much better to establish real communications as early as possible in posts.

[June7]

tbxYear and tbxMonth are textboxes on form where user inputs the year and month for the booking. Alternatives:

1. extract year and month from the current date

2. extract year and month from a full date value entered as the booking date

Use Year() and Month() functions for extracting those date parts.