? Looping formula needed

[Leesha]

Hi!
I have a database that bills royalty fees for stores. The single invoices are working fine. My problem is that the user would now like to be able to enter a date range to be able to print past invoices. I’m pretty sure I would need to use some type of looping code but am clueless as to how to write it.

When an individual invoice prints, it has the current fees on it (there are about 10) as well as any credit/balance due from previous invoices. The credit balance due is calculated based on the sum of the invoices prior to the current billing period minus the sum of the payments prior to the current billing period. These figures may change at any time due to adjustments which is why they are recalculated each time an invoice is run. The calculations are generated based on the date range that is on frmPrintInvoices. This date range is always for a month at a time when invoices are being generated.

Now I need to generate the invoices individually for whatever period of time the user enters. So, if they put in a date range of 1/1/2010 – 12/31/2010 there would be 12 different invoices generated. I’m clueless as to how to do this.

I’m attaching a very simplified version of the database in case a visual would help. I didn’t include the payment table and queries simply because it was getting too big to upload. The formulas are totally bogus, so disreguard them. They are only for an example.

If you enter the date range of say 1/1/2010-1/31/2010 you get a one month report which is fine. If you enter 1/1/2010 – 6/30/2010 you still only get one report. I realize this is because of the qryInvoiceSum-BillingReport, however I don’t know how to fix it, or make it loop. If someone could point me in the right direction as to how the code should look I could probably take it from there.

Thanks!
Leesha

[HansV]

Try this:

Code: Select all

Private Sub cmdPrint_Click()
  Dim d1 As Date
  Dim d2 As Date
  d1 = Me.txtStartDate
  d2 = Me.txtEndDate
  ' Suppress confirmation prompts for action queries
  DoCmd.SetWarnings False
  ' Loop through the months
  Do While Me.txtStartDate <= d2
    ' Limit to one month
    Me.txtEndDate = DateAdd("m", 1, Me.txtStartDate) - 1
    ' Run the queries
    DoCmd.OpenQuery "qryDeleteInvoiceBillingSum-Temp"
    DoCmd.OpenQuery "qryAppendInvoiceBillingSum-Temp"
    ' Open the report in dialog mode (otherwise the code would run on without waiting)
    DoCmd.OpenReport "rptInvoice", acPreview, , , acDialog
    ' On to the next month
    Me.txtStartDate = DateAdd("m", 1, Me.txtStartDate)
  Loop
  ' Warnings on again
  DoCmd.SetWarnings True
  ' Restore original date
  Me.txtStartDate = d1
End Sub

[Leesha]

Hi Hans!

I could actually understand the rationale behind the code but not enough to figure out what to do with syntax error that comes up on this line:

Do While Until Me.txtStartDate <= d2

Also, your notes say to open the report in dialogue mode. I've never done this and am not sure where to set this.

I can't wait to see it run!

Thanks
Leesha

[HansV]

... syntax error that comes up on this line:

Code: Select all

  Do While Until Me.txtStartDate <= d2

Aaargh! I changed my mind halfway through. That should be

Code: Select all

  Do While Me.txtStartDate <= d2

Also, your notes say to open the report in dialogue mode. I've never done this and am not sure where to set this.

You don't have to set that yourself - the code does it for you.

[Leesha]

Thanks Hans! That is exactly what I hoped for!!! You're the best!

Leesha

[Leesha]

Back again. When I go to actually print the invoice, the print icon is frozen and won't allow me to print. I don't dare play with the code and as a result mess it up.
Thanks,
Leesha

[HansV]

Would it be OK to print the reports immediately?

[Leesha]

That would be fine. I just tried setting the print option so it would go directly to the printer andi it worked beautifully.

Thanks again Hans.

Leesha