Auto Number - Start over each year

[GaryS]

Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In
the bound form, frmProvider, I would like txtProcessProviderNumber to
increment by 1
on each new record each new record entered during the Year. The year will
be entered manually by the user via combo box in text field. Once the
Process year changes to a new year I would want the numbers to start all over
at 1. How can I do this. Any help would be greatly appreciated.

 

[Marshall Barton]

Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields. In
the bound form, frmProvider, I would like txtProcessProviderNumber to
increment by 1
on each new record each new record entered during the Year. The year will
be entered manually by the user via combo box in text field. Once the
Process year changes to a new year I would want the numbers to start all over
at 1.

The AutoNumber field type does not even guarantee sequential
numbers, much less starting over for any reason.

To do this kind of thing, you must use a form to
view/edit/add records. Then you can put a little code in
the form's BeforeUpdate event to do it:

If Me.NewRecord Then
txtProcessProviderNumber=Nz(DMax("roviderProcessNumber", _
"tblProvider","ProcessYear=" & Me.txtProcessYear),0)+1
End If

 

[GaryS]

Hi Marshall,

I placed the code in the beforeupdate event as you said, but it errors
telling me that I have a Type mismatch in the criteria expression. When I
open the debugger I see that cboProcessYear=2008, but
txtProcessProviderNumber=0.

ProcessYear is a text field, and ProcessProviderNumber is a Number field.
Does the code need some adjustment to accomdate the two data types?

Thanks for your help.

 

[GaryS]

Oops! Here is the code I have so far, only slightly modified.

If Me.NewRecord Then
txtProviderProcessNumber = Nz(DMax("providerProcessNumber", _
"tblProviderStatus", "ProcessYear=" & Me.cboProcessYear), 0) + 1

End If

 

[Marshall Barton]

Oops! Here is the code I have so far, only slightly modified.

If Me.NewRecord Then
txtProviderProcessNumber = Nz(DMax("providerProcessNumber", _
"tblProviderStatus", "ProcessYear=" & Me.cboProcessYear), 0) + 1
End If

Sorry, I assumed that the year field was a number. For a
text field, use:

. . . , "ProcessYear='" & Me.cboProcessYear & "' "), 0) + 1