Help with counter condition!

Giganews Newsgroups
Subject: Help with counter condition!
Posted by:  brad.goldbe…@gmail.com
Date: 14 Aug 2006

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

Replies