I recently posted a message asking for help with
sequential numbers. I want to create an autonnumber
reference number that reverts back to 1 at the start of
each year. GlenAppleton gave me some code and I thought I
had it working but I don't. When I create a new record
nothing happens in the control CommDocNbrtxt where the
number should appear.
Here is my complete code, can anyone help?
If you read this Glen sorry to post again but I wasn't
sure if you would go back to check my last message
Option Compare Database
Function GetDocIndex() As String
Dim rsDocs As DAO.Recordset
Dim intDocIdx As Integer
Dim strLastIdx As String, strNewIdx As String, strSQL
As String
' Get the last index for this year
strSQL = "SELECT Max([CommDocNbrtxt]) As
[LastDocIdx] " & _
"FROM [tblDocGroupLists] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year(Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenRecordset(strSQL)
With rsDocs
If Not .RecordCount = 0 Then
strLastIdx = .Fields("LastDocIdx").Value
.Close
'End With
Set rsDocs = Nothing
' Convert last index to integer or leave as zero
If Not strLastIdx = "" Then intDocIdx = CInt
(strLastIdx)
' Increment the index
intDocIdx = intDocIdx + 1
' Append the 2 digit year as decimal value
strNewIdx = intDocIdx & "." & Right(CStr(Year(Date)),
2)
' Return the new index
GetDocIndex = strNewIdx
End If
End With
End Function
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If
End Sub
TIA
Tony
