Sequential Numbering with VBA code (1 Viewer)

[spike250]

Hi,

I have a text field on my form which I have limited to no duplicates.

I have tried to use an Auto Number format but I would encount problems at the end of the year due to the format of the number

i.e. 2009 = Z,09.00001, Z,09.00002 etc
2010 = Z,10.00001, Z,10.00002 etc

What I am trying to do is the following;

When the users types in a number out of sequence with the previous one a message box pops up advising that the Z number is not in sequence do you wish to proceed? the user would then click no for 2009 but when 2010 comes the user would select yes in the above message box which would change the format to Z,10.''''' etc and the sequence would start again from z,10 onwards.

This would carry on for the following years.

I have been told that it is possible to this using VBA but I am unsure how.

I would appreciate any help on this matter.

Regards

Spike

 

[ezfriend]

Hopefully the function below will do the trick for you. As always, you will need to test it well and consider situation where two users to to add the record at the same time.

Private Function GetNextNumber() As String

    Dim rs As Recordset
    Dim sSQL As String
    
    sSQL = ""
    sSQL = sSQL & "SELECT MAX(MyNumber_PK) FROM tblTABLE_NAME "
    sSQL = sSQL & "WHERE MyNumber_PK LIKE 'Z," & Format(Date, "YY") & ".*"
    
    Set rs = CurrentDb.OpenRecordset(sSQL)
    
    If rs.EOF = False Then
        'add one to the current number and then call the AppdendLeadingZeros function to add the
        'appropriate leading zeros to the number.
        GetNextNumber = "Z," & Format(Date, "YY.") & AppendLeadingZeros(CLng(Right(rs(0), 8)) + 1)
    Else
        'this means that nothing starts with "Z,YY." yet so it is the first record for the year
        GetNextNumber = "Z," & Format(Date, "YY") & ".00000001"
    End If
    
End Function

Private Function AppendLeadingZeros(ByVal sNumber As String) As String
    
    Dim i, j, k As Long
    Dim sZeros As String
    
    i = Len(sNumber) + 1
    
    sZeros = ""
    
    'in this case, i am using 8-digit number
    For j = i To 8
        sZeros = sZeros & "0"
    Next
    
    AppendLeadingZeros = sZeros & sNumber
    
End Function

 

[WayneRyan]

Spike,

What you really need to do is separate the "number" into its true
representation ... 3 fields:

TheLetter (Z)
TheYear (9, 10)
TheSequence

Use the form's BeforeInsert event to:

TheSequence = Nz(DMax("[TheSequence]", _
                      "YourTable", _
                      "[TheLetter] = ' And " & _
                      "[TheYear] = " & TheYear), 0) + 1

You can always present it easily with:

TheLetter & ", " & TheYear & "." & TheSequence

Set the Default values for TheLetter (="Z") and TheYear (=Year(Date())
and you should be good to go.

hth,
Wayne