Help with counter condition!

[brad.goldberg]

Hey All...

I have a form that has a Before Update event of:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

Me!displayedRunNumber = Format(CLng(Nz(DMax("displayedRunNumber",
"fields"), "0")) + 1, "0000")

End If
End Sub


Basically every new record the displayedRunNumber is incremented by 1
and stored. I know some people question why its a Before Update but
thats the way it needs to be.

Heres the problem. I need the number to reset counting to 0001
automatically each new year. For example... the first entry this year
is 0001 and it will keep going up with each new record 0002.... and so
on. When it hits 2007, I need somehow for the first entry of 2007 to
start back at 0001 again and continue counting up.

Help would be greatly appreciated.. I'm kind of new to access so if you
cite code please let me know where it is supposed to go and how it
should look.

Thanks in advance,

BG

 

[Pat Hartman\(MVP\)]

You have a syntax problem with the existing statement. Numbers should NOT
be enclosed in quotes. Only strings are enclosed in quotes so the statement
should be:
Me.displayedRunNumber = Format(CLng(Nz(DMax("displayedRunNumber", "fields"),
0)) + 1, "0000")

Resetting the counter each year will result in duplicate values. You will
need to include a second field in the primary key or in the unique index.
This field can be the date the record was added. Your RunNumber statement
would now be:
Me.displayedRunNumber = Format(CLng(Nz(DMax("displayedRunNumber",
"fields","Year(YourDateField) = Year(Date())"), 0)) + 1, "0000")