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

[pietlinden]

so use DCount and Year(Date) as part of your formula, so you the code
resets the counter every year.

[Bob Quintal]

br***********@gmail.com wrote in
news:11**********************@75g2000cwc.googlegro ups.com:

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

In your code, you need to modify
DMax("displayedRunNumber","fields")
to something that filters for the year. For this you will nead a
date field in the table that you can check for the year. and the
year from the computer's clock.

say that field is called RunDate, so your expression becomes.
DMax("displayedRunNumber","fields","year(rundate)= year(now()")

The rest of the code stays exactly the same.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com