Using VBA to create an automated ID number

[Kemidan2014]

OP-EDIT - i apologize to the community for posting, as soon as i hit send i stumbled upon my answer in an older piece of code i had forgotten about. i edited my correction to hope someone can learn from this

I believe i touched base on this before but that was for ANOTHER form, the current form i have is utilizing some code i found through internet research to create a serial number each time a new record is created Format is "YY-###" problem is when i got to 23-011 it is going back to 23-001 which a record exists, so far we are exiting before any data is overwritten but i would like to try and correct the code. i understand HOW its getting that number based on the debug.print lines in the immediate window i just cant figure out why its doing it. I am not sure if its related to the Function "getnum" below or how i am calling and using "getnum"

Below is my code and the Debug print values.

Private Sub Form_Current()
If Me.NewRecord = True Then
 Dim strprevid As String
 Dim lngcurnum As Long
 Dim lngnextnum As Long
 Dim strnextnum As String
 Dim strnewid As String
 
    strprevid = DMax("[TrackingNum]", "[NONCTab]")
    Debug.Print strprevid 'immediate window result: "23-011"  this is correct value
   
    lngcurnum = getnum(strprevid) 'getnum is a function, i shall post it after this code  'OPEDIT Changed to Right(strprevid, 3) i created 15 new test records and it cycled the correct amount of digits. 
    Debug.Print lngcurnum 'immediate window result: "1"  this is where i think the problem is occurring
   
    lngnextnum = lngcurnum + 1
    Debug.Print lngnextnum 'immediate window result "2" 'doing the right thing based on the above string
   
    strnextnum = String(3 - Len(CStr(lngnextnum)), "0") & CStr(lngnextnum)
    Debug.Print strnextnum 'immediate window result "002" again i believe its doing the correct thing
   
    strnewid = Right(year(Date), 2) & "-" & strnextnum
    Debug.Print strnewid 'immediate result "23-002"  already exists
   
    Me.TrackingNum = strnewid
End If


End Sub

Function getnum(s As String) As String
'getting variabls
Dim retval As String
Dim i As Integer
'start with empty string
 retval = ""
For i = 1 To Len(s)
 If Right(Mid(s, i, 1), 3) >= "0" And Right(Mid(s, i, 1), 3) <= "9" Then
  retval = Right(Mid(s, i, 1), 3)
 End If
Next
'then return the new value

 getnum = retval
 

End Function

 

[rlv01]

What Function getnum is doing does not make sense to me. What is it you WANT it to be doing?

At a guess it looks like maybe you want an input of "23-011" to produce an output of 11, in which case something like this might work.

Function getnum(s As String) As Long
    'getting variabls
    Dim retval As String
    Dim SA As Variant
    
    SA = Split(s, "-")
    retval = SA(UBound(SA))
    getnum = Val(retval)
End Function

but it's only a guess.

 

[Micron]

This sort of data should be parsed into fields whereupon you concatenate in forms and reports.
Then generating a new value should be as simple as NewNum = DMax(myField)+1