Coding a Command Button to open specific reports

[Francis Cunningham, Jr.]

I have two controls, among others, on a particular form. One control
‘ContractNumber’ represents the specific customer (their phone number) and
the ‘ContractType’ which represents the specific report. I have 5 reports,
none of the reports are the same. Based on the ‘ContractNumber’ I would like
to have a command button open the ‘ContractType’ for the particular customer.
I realize I can have command buttons, one for each report and have a
parameter query use the ‘ContractNumber’ to call up the specific report. I
want one button to open different reports, say ‘Click to view customers
Contract’. The one command button would pick the correct customer and their
specific contract.
The reports are named: Gas, Electric, Oil, HeatPump and Cooling.
The ContractNumber is actually the customers phone number.
The ContractType’s are: Gas, Electric, Oil, HeatPump and Cooling.
Can anyone help. I have been laboring for months with this.

 

[Ken Warthen]

Francis,

Have you considered displaying the reports in a list box that is populated
based on the selection of a contract number? Then the user can either double
click on the report name in the list box, or select the report in the list
box and click a command button to preview the report.

Ken Warthen
(e-mail address removed)

 

[Dale Fye]

It would seem to me that you might have multiple contracts with the same
user, in which case, I would agree with Ken about presenting them with a list
or combo box of the various contracts. Assuming that each customer only has
one contract type, you might have code similar to the following in your
command buttons click event:

Private sub cmd_OpenReport_Click

Dim strReport as string
Dim strCriteria as string

strCriteria = "[ContractNumber] = " & me.txt_ContractNumber
SELECT CASE DLOOKUP("ContractType", "tbl_Contracts", strCriteria)
CASE "Gas"
strReport = "rpt_Cust_Gas"
CASE "Electric"
strReport = "rpt_Cust_Electric"
CASE "Oil"
strReport = "rpt_Cust_Oil"
CASE "HeatPump"
strReport = "rpt_Cust_HeatPump"
CASE "Cooling"
strReport = "rpt_Cust_Cooling"
Case Else
msgbox "Unknown customer!"
Exit Sub
End Select

docmd.openReport strReport, acViewNormal,,strCriteria, acWindowNormal

End Sub

You might even want to have an option on your form for opening the report in
preview mode rather than acViewNormal.

HTH
Dale