Originally posted in the General Access area but thought it might be more related to the Programming section.
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.