Automatically selecting one of two Reports

[notoca]

Hi

A user enters details on a form that will be used to create one of two invoice reports. I would like to set up a button for "Print Report" and have a bit of code that looks at the invoice total and decides which invoice report to print. There are two invoice reports: one for invoices with a value of $50 or less, the other for invoices with a value of $51 or greater.

Thanks for any help - I've just started using access and I been given this invoice program to set up.

Russell

 

[Anne Troy]

What is the diff between your two reports?

I feel that it would be easier to have your report change based on the InvoiceValue

 

[notoca]

Hi

There are significant differences between the structure of the two reports to treat them as two different reports. Also, one report will have fields not in the other report. For example, in the "Invoice Details" table there are fields for the account details. These are only required in one of the reports.

Russell

 

[Gates Is Antichrist]

I believe you want to use dsum() and react in code. See if the Help example for dsum is enough to guide you. Then you can DoCmd.OpenReport - see Help for OpenReport Method. All this would be done in your button code.

 

[SydneyGeek]

Hi, a quick and dirty solution is to open one of the reports, check the value, and if OK, leave it open for printing, otherwise shut it and open the second report. The user will see a brief flash if they go to the second report, but shouldn't be a huge issue.
Assuming Report1 (<=$50) and Report2 (>=$51), with a txtInvTotal field in each report that gives you the invoice total, you could try this:

Sub CmdPrint_Click()
  DoCmd.OpenReport "Report1"
  If Reports![Report1].[invTotal]>=51 then
    DoCmd.Close acReport, "Report1"
    DoCmd.OpenReport "Report2"
  End If
End Sub

Don't have Access on this machine, so untested...
Denis

 

[Gates Is Antichrist]

I agree with the Geek here. I read the original post too quickly and misidentified that the total of all invoices was what the report choice was based on. Denis's approach looks like the correct route - not mine. G'job, mate.

Yet just setting up a different report filter may be the simple way out too.

 

[notoca]

Hi

This is the code that i've setup but I get this error message: The report named rpt_Invoice_Proforma_For_A_Invoice is misspelled or refers to a report that isn't open or doesn't exist.

It's not mispelled and it does exist and it should be opening as there is a command line that opens the file. Any ideas?? Thanks

Private Sub Preview_Invoice_Invoice_Request_Click()
On Error GoTo Err_Preview_Invoice_Invoice_Request_Click


DoCmd.OpenReport "rpt_Invoice_Proforma_For_A_Invoice"
If Reports![rpt_Invoice_Proforma_For_A_Invoice].[Invoice_Total_GST_Incl] > 50 Then
DoCmd.Close acReport, "rpt_Invoice_Proforma_For_A_Invoice"
DoCmd.OpenReport "rpt_Invoice_Request_Proforma_For_A_Invoice"
End If

Exit_Preview_Invoice_Invoice_Request_Click:
Exit Sub

Err_Preview_Invoice_Invoice_Request_Click:
MsgBox Err.Description
Resume Exit_Preview_Invoice_Invoice_Request_Click

End Sub