Generate primary key with actual date & increment number

[sanjass80]

Hi i have a field called "wosubID" which is the work order issued for subcontractors which is the primary key.

i want to have the code generated in the following format, mmyy-increment numbers.

In the form i've created i have created an event on this field which is as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)
 
    Me.wosubID = Format(Now(), "mmyy")
 
Exit_Here:
    Exit Sub
 
End Sub
 

how to i add the part for the numbers which should look something like

0316-001
0316-002
and so on

can anyone help

[PhilOfWalton]

Try

Function FunnyFormat() As String
 
    Dim MyDb As Database
    Dim Rst As Recordset
    Dim Num As Integer
    Dim LargestNum As Integer
 
    Set MyDb = CurrentDb
    Set Rst = MyDb.OpenRecordset("SELECT WoSubIDs.* From WoSubIDs;")
 
    With Rst
        Do Until .EOF
            Num = CInt(right(!WoSubID, 3))
            If Num > LargestNum Then
                Num = LargestNum
            End If
            .MoveNext
        Loop
        .Close
        Set Rst = Nothing
    End With
 
    LargestNum = LargestNum + 1
   FunnyFormat = Format(Date, "mmyy") & "-" & Format(LargestNum, "000")
 
End Function

Phil

[sanjass80]

thanks Phil
the previous code i wrote was based on the event before insert in my form.
for the above code where should i use it?

[PhilOfWalton]

Sorry, had a bit wrong in the code. It should have been LargestNum = Num, not the other way round.
Anyway just use it in the BeforeInsert

Private Sub Form_BeforeInsert(Cancel As Integer)
 
    Dim MyDb As Database
    Dim Rst As Recordset
    Dim Num As Integer
    Dim LargestNum As Integer
 
    Set MyDb = CurrentDb
    Set Rst = MyDb.OpenRecordset("SELECT WoSubIDs.* From WoSubIDs;")
 
    With Rst
        Do Until .EOF
            Num = CInt(right(!WoSubID, 3))
            If Num > LargestNum Then
                 LargestNum = Num
            End If
            .MoveNext
        Loop
        .Close
        Set Rst = Nothing
    End With
 
    LargestNum = LargestNum + 1
   WoSubID = Format(Date, "mmyy") & "-" & Format(LargestNum, "000")
 
End Sub

[jforbes]

Are you expecting to increment the numeric part indefinitely, or are you expecting to reset it every year back to 001?