Make a query pick a specific report.

[Francis Cunningham, Jr.]

How do I call up a specific report based on 2 or 3 fields in a query or table?

I have 4 reports, each design somewhat similar, but a paragraph different.
I would like the form fields (CustomerID and ContractID) determine which
report opens to print or preview.
For example:
If Customer ID=1 and ContractID=1 then print Report A.
If Customer ID=1 and ContractID=2 then print Report B.
If Customer ID=2 and ContractID=1 then print Report A and so on.

If anyone can help me with this I am greatly appreciative.
Frank

 

[Ofer Cohen]

One way will be just as you wrote it

If Customer ID=1 and ContractID=1 then Docmd.OpenReport "A"
If Customer ID=1 and ContractID=2 then Docmd.OpenReport "B"
If Customer ID=2 and ContractID=1 then Docmd.OpenReport "A"

Another way will be using Else
If Customer ID=1 and ContractID=1 then
Docmd.OpenReport "A"
Else
If Customer ID=1 and ContractID=2 then
Docmd.OpenReport "B"
Else
If Customer ID=2 and ContractID=1 then
Docmd.OpenReport "A"
End If
End If
End If

But it hard to answer without seeing all the cnditions, from your example
you can join the conditions

If (Customer ID=1 Or Customer ID=2) and ContractID=1 then
Docmd.OpenReport "A"
Else
If Customer ID=1 and ContractID=2 then
Docmd.OpenReport "B"
End If
End If

 

[Ofer Cohen]

I just noticed that, what do you mean from a query or a table?

Create a form with two Combo's (Customer ,ContractID) Where the use select a
value, then add a button to the form with the code provided to open the right
report

 

[Francis Cunningham, Jr.]

For Ofer Cohen:
To explain myself a little better. I already have the form made, and on the
form are the fields CustomerID and ContractID. I would like the create a
command button on this form and when clicked it would open the report based
on the CustomerID and ContractID, there are 4 or 5 report possibilities. The
CustomerID and ContractID are fields on two different tables. Your solution
appears to be exactly what I want to do, I just don’t know how to do it and
where to code should go.
Frank

 

[Ofer Cohen]

Locate the cursor in the OnClick event of the button created, on the right
you'll see a button with three dots, click on it and select code view.

Write the code between the

Declare Sub ()
' The code
End Sub

 

[Francis Cunningham, Jr.]

For Ofer Cohen:
When I click on the Code View for the Command Button the code looks like this:
Compile Error
Variable Not Defined

Private Sub Model3_Click()

If ContractType = Electric Then DoCmd.OpenReport "Electric"
If ContractType = Gas Then DoCmd.OpenReport "Gas"
If ContractType = Oil Then DoCmd.OpenReport "Oil"
If ContractType = HeatPump Then DoCmd.OpenReport "HeatPump"
If ContractType = Cooling Then DoCmd.OpenReport "Cooling"

End Sub

Except I get Private Sub() not Declare Sub().
When I save the code and open the form I receive this error message when the
button is clicked.
Compile Error
Variable Not Defined

I should let you know I am using Access 97.
Frank

 

[John Spencer]

At a minimum I would guess that you need to add quotes around the
contract type value you are checking.

Private Sub Model3_Click()

If ContractType = "Electric" Then DoCmd.OpenReport "Electric"
If ContractType = "Gas" Then DoCmd.OpenReport "Gas"
If ContractType = "Oil" Then DoCmd.OpenReport "Oil"
If ContractType = "HeatPump" Then DoCmd.OpenReport "HeatPump"
If ContractType = "Cooling" Then DoCmd.OpenReport "Cooling"

End Sub


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================