Sleuth. Can't find the code/macro that performs a reset.

[HeadGasket]

Greetings Oh Exhaulted and Divine Wizards of Access!

I have a field that is used to create a unique invoice/event/report number that resets back to '1' at the beginning of the week. For the life of me I cannot find the VB code, macro, expression, whatever, that allows this field to reset each week. I'm trying to duplicate this type of thing in a new database. A guru helped me with this a couple of years ago and I don't remember how we did it. Anyway....

Question is, how might one go about finding what is triggering the weekly reset?

Below is more specific info in case you need it.



Three fields in my table (tbl_NCMain) are:

ReportYear = Text field with Default Value as =Format(Date(),"yy"). Returns two-digit number based on last two digit of current year (yy)

ReportWeek = Text field with Default Value as =Format(Date(),"ww"). Returns the two-digit week based on 52/53 weeks per year.

ReportID = Number field that starts at '1' for the first entry of the week and increments by +1 for each additional entry (up to 99 max). Resets back to '1' at the beginning of the following week.

These three fields are then concatenated in my form and report to create a event/invoice number for printing purposes.

The only VB code I can find that is related is below. I can see how it increments but it doesn't seem to be what is controlling the weekly reset of ReportID back to '1'.

Private Function GetReportNumber()
Dim strCriteria As String
strCriteria = "ReportYear = """ & Me.ReportYear & """ " & "And ReportWeek = """ & Me.ReportWeek & """ "
Me.ReportID.DefaultValue = """" & Nz(DMax("ReportID", "tbl_NCMain", strCriteria), 0) + 1 & """"
End Function

Let me know if you need more info or have it presented differently. Thanks.

[pbaldy]

That is the code that does it. The Nz/Dmax will find the greatest existing number for the given week, and if there is none starts it at 1.

[Robeen]

Your magic is happening in these two lines:

 
strCriteria = "ReportYear = """ & Me.ReportYear & """ " & "And ReportWeek = """ & Me.ReportWeek & """ "
Me.ReportID.DefaultValue = """" & Nz(DMax("ReportID", "tbl_NCMain", strCriteria), 0) + 1 & """"

strCriteria is providing a Year and a Week to Me.ReportID.DefaultValue.

When this:

Me.ReportID.DefaultValue = """" & Nz(DMax("ReportID", "tbl_NCMain", strCriteria), 0) + 1 & """"

is evaluated - if there are no other entries this week [because it is a new week and you haven't entered any yet] - it will return a 0 from the Nz function and then it will increment that to 1 with the + 1 - thereby giving you 1.

Hope this helps.

[Robeen]

Sorry - pbaldy's reply went in while I was typing mine & I didn't see it.

[pbaldy]

No problemo; we were typing at the same time, and you provided more detail, so it took you longer.

[HeadGasket]

Thanks, guys! I thought it was right in front of my eyes!?!

But can you point out what is the 'week' identifier? I don't quite understand what makes it reset weekly instead of monthly, daily, yearly, etc.

I just happened to name my field ReportWeek. Is that it?

[pbaldy]

The DMax() along with the strCriteria line is basically saying "give me the maximum ReportID where the ReportYear and ReportWeek match the ReportYear and ReportWeek on this form". If there is no record matching the criteria the Nz() kicks in and seeds the value at 0. In either case the " + 1" adds one. So your "reset" comes from the Nz() function starting things at 0 when there aren't any existing records.

[HeadGasket]

Thanks for the detailed explanation. I get it now. Mystery solved.

[pbaldy]

We were happy to help. Send up some Cabernet.