issue with Autogenerated custom serial number

[Kemidan2014]

I needed to replicate an Excel version of an Alert log into my Access Database for my team to utilize.
i have a Table "qalertTab" the key field is "Alert_number" this is the feild i want to serialize
i am making a Form set for Data Entry so then when User clicks the command button on front page to open this form it is open a new record with the new Serial number already populated. I have ALREADY successfully achieved this with ANOTHER log that i replicated from our old Excel version and i am attempting to use the same function to create the new serial number just changing the feild and table reference. the Format im using is a simple YYYY### format

my current imported Table has just over 1000 entrys previously filled in from before i started working on Access version so this year we have 2023001 to 2023011 filled in qalerttab Alert_number field
the functions do not necessarily fail it just doing the math wrong it keeps generating in a new record 2023005 when the correct new value SHOULD be 2023012.
below is my code what could i have missed or am not considering when trying to use this technique.

1st step in a MODULE i have the following Function created it is the same function i am using in the other form that uses YY-### format

Option Compare Database
Option Explicit

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

2nd step is in the vbacode of the form itself i have

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 = DLast("[Alert_number]", "[Qalerttab]")
    Debug.Print strprevid
    
    lngcurnum = getnum(strprevid)
    Debug.Print lngcurnum
    
    lngnextnum = lngcurnum + 1
    Debug.Print lngnextnum
    
    strnextnum = String(3 - Len(CStr(lngnextnum)), "0") & CStr(lngnextnum)
    Debug.Print strnextnum
    
    strnewid = year(Date) & strnextnum
    Debug.Print strnewid
    
    Me.Alert_number = strnewid
End If
End Sub

iin my immediate window is it showing record 2019044 would be Dlast? but thats in the MIDDLE of the table? so i dont understand why its not working.

 

[Micron]

First, I would not use strings when you're trying to generate values that contain numbers. For one thing, numerics that are text data type sort differently, as in 51 comes before 6. Last and DLast are unreliable when using text. Also, those functions should be used only on ordered sets built by a query. To add to those problems, you're not using criteria with the DLast function so it will return a random value. A table should be likened to a bucket of records whose representation can change from one day to the next. It is largely based on how data is read from disk so the order of records cannot be relied on in cases like this. You should have a numeric field that you concatenate prefixes and suffixes to in order to 'assemble' the id value you need. Also, the number you generate should not be used as a primary key.

the key field is "Alert_number" this is the feild i want to serialize

You should have a numeric field that you concatenate prefixes and suffixes to in order to 'assemble' the id value you need. Also, the number you generate should not be used as a primary key. The suffix/prefix values would be in separate fields.

EDIT - if you generate the number at the beginning of the process and the user cancels the record creation, you could incur gaps in the numbering. If that's a concern there are things you can do to try to prevent that.

 

[Kemidan2014]

Micron, I will take your suggestion to concatenate the values but i also switched Dlast to Dmax which was able to correctly pick the latest record which was definitely part of the problem. since first part of the number is just the year which i already had as another field i was able to use this to assemble as you say the serialized number thank you.

 

[Micron]

Glad I could help & thanks for the recognition.