Function to keep numeric value 4 digits

[steve21nj]

Hello,

I have the below function that works correctly. However, I would like the "Last_Nbr_Assigned" value to start at 0000 and increment by 1. So the very first number assigned would be 0001, then 0002, then 0003, and so on.

This number is automatically assigned so I haven't had luck with the validation properties. And if manually enter 0001 for the "Last_Nbr_Assigned", it will automatically cut off the 3 zeros, leaving me with 2 instead of the desired 0002.

Any suggestions?

 Function NewRF() As String
 Dim startRange_Var As Long, lastNum_Var As Long
     On Error GoTo Err_Execute
 
    'Retrieve last number assigned for RF
    groupname_Var = Nz(DLookup("Group_Name", "Codes"), 0)
    codeDesc_Var = Nz(DLookup("Code_Desc", "Codes"), 0)
    lastNum_Var = Nz(DLookup("Last_Nbr_Assigned", "Codes"), 0)
    ycodeDesc_Var = Nz(DLookup("YearValue", "Codes"), 0)
    
    Set db = CurrentDb()
    Set rstsource = db.OpenRecordset("Codes", dbOpenDynaset)
  'If no records were found, create a new RF in the Codes table
    'and set initial value to 1
    If lastNum_Var = -1 Then
        groupname_Var = Nz(DLookup("Group_Name", "Codes"), 0)
        codeDesc_Var = Nz(DLookup("Code_Desc", "Codes"), 0)
        lastNum_Var = Nz(DLookup("Last_Nbr_Assigned", "Codes"), 0)
        ycodeDesc_Var = Nz(DLookup("YearValue", "Codes"), 0)
        
        Set db = CurrentDb()
        Set rstsource = db.OpenRecordset("Codes", dbOpenDynaset)
     'New RF is formatted as "RF-1", if starting from scratch
        LNewRF = codeDesc_Var & "-" & lastNum_Var
    Else
        'Determine new RF
        'New RF is formatted as "RF-1"
        LNewRF = groupname_Var & "-" & codeDesc_Var & "-" & lastNum_Var & "-" & ycodeDesc_Var
                
         'Debug.Print CLng(lastNum_Var))
        'Increment counter in Codes table by 1
        LUpdate = "UPDATE Codes SET Last_Nbr_Assigned = " & lastNum_Var + 1
 
        CurrentDb.Execute LUpdate, dbFailOnError
    End If
  
    NewRF = LNewRF
 
    Exit Function
 
Err_Execute:
    'An error occurred, return blank string
    NewRF = ""
    Call MsgBox("An error occurred while trying to determine the next RF to assign." & Chr(10) & Err.Description, vbCritical)
End Function

 

[pbaldy]

I would leave it as is and use the Format() function to display it to users:

Format(FieldName, "0000")

 

[steve21nj]

Thanks. I thought about that prior to posting, thought there might be an easy fix to something I was over looking.

I'll stick with your solution.

 

[pbaldy]

No problem. A numeric field will not hold leading zeros, so your alternative is changing the data type of your field to text.

 

[steve21nj]

It currently is a text field.

The one problem I am running into with the Format () option comes from how the field is displayed "RF-ABC-1-FY15", makes it a little challenging.

 

[pbaldy]

Really? And entering 0001 manually drops the zeros? That doesn't sound right; if you can post the db I'll take a look.

You'd use it like:

groupname_Var & "-" & codeDesc_Var & "-" & Format(lastNum_Var, "0000") & "-" & ycodeDesc_Var

 

[steve21nj]

Ah, that was it. Thank you.

 

[pbaldy]

Happy to help!