Incremental Invoice Number using VBA - Access

Question

Part of my access application does invoicing. I have an invoice number table that wholes one field "InvoiceNum". On my invoice report I have the following code:

Private Sub Report_Open(Cancel As Integer)
    'lookup invoice number when invoice opens 
    intInvoiceNum = Nz(DLookup("InvoiceNum", "tblInvoiceNum"), 0)
End Sub`

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    'Incrementally add one (1) to the invoice number for every page of in the report
    Me.txtInvoiceNum = intInvoiceNum
    intInvoiceNum = intInvoiceNum + 1
End Sub`

Private Sub Report_Close()
    'Update tblInvoiceNum with the last invoice number in the report
    db.Execute ("UPDATE InvoiceNum SET InvoiceNum = " & intInvoiceNum) 
End_Sub`

Problem: My report open with groupby ClientID and ProjectNum. Multiple invoices print on the same report. If any invoice spills over to second page the Format Code runs again inserting an incremental invoice number to the second page. How can I prevent this from happening?

This is one of the unfortunate realities of doing things on a whim. Is there a better approach you can take to getting the 1 appended to the Invoice Number? Maybe in the query add a field that does something like what you would need (I'm not sure how to group them).. There are also properties where you can select "Keep on one page", but I'm not sure if that would work. — Mark C.
Apply invoice numbers to your invoice records prior to printing. — Gustav
Your increment is in the page header. Try adding it to the report header instead, or onload event. — Gene
Gene - If i do this, subsequent pages wont have an invoice number, I print multiple invoices in one single report. Gustav - your idea has some merit but again the problem is that i print multiple iterations of different ClientID & ProjectNum per report. You idea would work if i was generating invoices from a bound form. — Jdba
It doesn't seem you're storing an invoice number anywhere. So even if this worked, when you run the same report twice for the same parameters you'll get two invoice numbers. — Nick.McDermaid

Parfait Parfait · Accepted Answer · 2015-06-09T01:31:18

Consider using a Running Sum control with the InvoiceNum in the report's Page Header section. No VBA required.

First, add an invisible textbox for running count over all report's records with following:

Control Source: =1
Running Sum: Over All
Name: RunningCount
Visible: No

Second, add another invisible textbox for DLookUp of InvoiceNum:

Control Source: =DLookup("InvoiceNum", "tblInvoiceNum")
Running Sum: No
Name: intInvoiceNum
Visible: No

Finally, add a visible textbox for sum of prior two controls serving as the incremental Invoice Number:

Control Source: =[intInvoiceNum] + [RunningCount]
Running Sum: No
Name: IncrementalInvoiceNum
Visible: Yes

You may be wondering, why the first two invisible controls? Reason being is using Running Sum: Over All will include both the counts and invoice number for each record of the report (e.g., 1 + InvoiceNum - 1st record; 1 + 1 + InvoiceNum + InvoiceNum - 2nd record...)

Incremental Invoice Number using VBA - Access

1 Answers