425,461 Members | 2,184 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

# Sequential numbers

 P: n/a 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 Nov 12 '05 #1