By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
2 Replies


P: n/a
"Tony Williams" <to***********@thecapitalpartnership.co.uk> wrote in message
news:bl**********@sparta.btinternet.com...
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. [snip] Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub


Unless you allow them and set them as the default it is unlikely that the value
of CommDocNbrtxt on a new record is "". It is probably Null. This test will
catch either...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub
Nov 12 '05 #2

P: n/a
Thanks Rick I'll try that
Tony
"Rick Brandt" <rv********@sbcglobal.net> wrote in message
news:bl************@ID-98015.news.uni-berlin.de...
"Tony Williams" <to***********@thecapitalpartnership.co.uk> wrote in message news:bl**********@sparta.btinternet.com...
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. [snip]
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CommDocNbrtxt.Value = "" Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub


Unless you allow them and set them as the default it is unlikely that the

value of CommDocNbrtxt on a new record is "". It is probably Null. This test will catch either...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.CommDocNbrtxt, "")) = 0 Then
Me.CommDocNbrtxt.Value = GetDocIndex
End If

End Sub

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.