Sequential numbers

[Tony Williams]

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 [tblDocGroupList s] " & _
"WHERE (Right([CommDocNbrtxt], 2) = '" & _
Right(CStr(Year (Date)), 2) & "');"
Set rsDocs = CurrentDb.OpenR ecordset(strSQL )
With rsDocs
If Not .RecordCount = 0 Then
strLastIdx = .Fields("LastDo cIdx").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_BeforeUpda te(Cancel As Integer)
If Me.CommDocNbrtx t.Value = "" Then
Me.CommDocNbrtx t.Value = GetDocIndex
End If

End Sub


TIA
Tony

[Rick Brandt]

Re: Sequential numbers

"Tony Williams" <tony.williams@ thecapitalpartn ership.co.uk> wrote in message
news:bl3rbr$n8m $1@sparta.btint ernet.com...[color=blue]
> 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.[/color]
[snip][color=blue]
> Private Sub Form_BeforeUpda te(Cancel As Integer)
> If Me.CommDocNbrtx t.Value = "" Then
> Me.CommDocNbrtx t.Value = GetDocIndex
> End If
>
> End Sub[/color]

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_BeforeUpda te(Cancel As Integer)

If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
Me.CommDocNbrtx t.Value = GetDocIndex
End If

End Sub

[Tony Williams]

Re: Sequential numbers

Thanks Rick I'll try that
Tony
"Rick Brandt" <rvtjbrandt@sbc global.net> wrote in message
news:bl43kb$81m 3k$1@ID-98015.news.uni-berlin.de...[color=blue]
> "Tony Williams" <tony.williams@ thecapitalpartn ership.co.uk> wrote in[/color]
message[color=blue]
> news:bl3rbr$n8m $1@sparta.btint ernet.com...[color=green]
> > 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.[/color]
> [snip][color=green]
> > Private Sub Form_BeforeUpda te(Cancel As Integer)
> > If Me.CommDocNbrtx t.Value = "" Then
> > Me.CommDocNbrtx t.Value = GetDocIndex
> > End If
> >
> > End Sub[/color]
>
> Unless you allow them and set them as the default it is unlikely that the[/color]
value[color=blue]
> of CommDocNbrtxt on a new record is "". It is probably Null. This test[/color]
will[color=blue]
> catch either...
>
> Private Sub Form_BeforeUpda te(Cancel As Integer)
>
> If Len(Nz(Me.CommD ocNbrtxt, "")) = 0 Then
> Me.CommDocNbrtx t.Value = GetDocIndex
> End If
>
> End Sub
>
>[/color]