Hi, I was trying to code an autonumber for the PK of a Table that only ID field and a text field.
I came with this:
It works mostly fine except that when it counts to ten, it stops counting, and I don't get it why.
Could someone give me some advice how to fix that?
Here is an image of how it looks.
Thanks
I came with this:
Code:
Option Compare Database
Option Explicit
Public Function Nextus() As String
Dim pref As String
Dim lastCase As String
Dim csNum As Integer
Dim nxtCase As Integer
Dim anno As String
anno = Format(DatePart("yyyy", Date), "0000")
'create prefix
pref = "VAL-NA-" & anno & "-"
If IsNull(lastCase = DMax("[ID]", "Table1", Left("ID", (Len(pref))))) Then
lastCase = 0
Else
lastCase = Right(DMax("[ID]", "Table1"), 1)
End If
csNum = Left(lastCase, Len(pref) + 2)
nxtCase = csNum + 1
Nextus = pref & nxtCase
End Function
It works mostly fine except that when it counts to ten, it stops counting, and I don't get it why.
Could someone give me some advice how to fix that?
Here is an image of how it looks.
Thanks