Public Function GetNewKeyVal(ByVal vDate As Variant) As String
'Function returns new key value in format "YY-MM-series number" = "18-12-0000002" etc
'... The field [YourKeyField] is text - size = 14
'--------------------------------------------------------------------------
Dim iYearNo$, s$ ' all as string
Dim sFieldName$, sTableName$
Dim v As Variant
'?GetNewKeyVal(#12/12/2018#)
'--------------------------------------------------------------------------
sFieldName = "YourKeyField" ' If there are no spaces in field name , or = "[Your Key Field]"
sTableName = "DataTest" ' If there are no spaces in table name, or = "[Data For Test]"
On Error GoTo GetNewKeyVal_Err
If vDate = Null Then vDate = Date
iYearNo = CInt(Format(vDate, "YY"))
'Find a hew value of key field by date
s = "CLng(Mid(" & sFieldName & ", 1, 2)) = " & iYearNo
v = DMax("CLng(Mid(" & sFieldName & ",7))", sTableName, s)
v = Nz(v, 0) + 1 'New no
GetNewKeyVal = Format(vDate, "YY\-MM") & "-" & Format(v, "0000000")
GetData_Bye:
On Error Resume Next
'... if some cases
Exit Function
GetNewKeyVal_End:
Exit Function
GetNewKeyVal_Err:
'MsgBox "Error: " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
"in Function: GetNewKeyVal in module: 00ModuleForTests", vbCritical, "Error in Application"
Err.Clear
Resume GetNewKeyVal_End