Multiple Reports to use only 1 Query

[Andy]

I am working on Financial Portfolio Reports.

I have 3 reports. All Idendtical except for "Sorting and Grouping". Just
different names to the report. Each is sorted differetly. ( by Par, by
Ticker, and by Pct Owned) They all use the same query which has a
"parameter" to choose the portfolio ID. The singular reports are selected on
a switchboard menu with an event click code.

I would like to use the query only once and print the 3 reports with one
event click and not have it request the parameter over again.

My code is included:

Private Sub cmdSummaryTck_Click()

On Error GoTo Err_cmdSummaryTck_Click



Dim stDocName As String



stDocName = "rptSummaryFindTck"

DoCmd.Maximize

DoCmd.OpenReport stDocName, acPreview



Exit_cmdSummaryTck_Click:

Exit Sub



Err_cmdSummaryTck_Click:

MsgBox Err.Description

Resume Exit_cmdSummaryTck_Click



End Sub

Private Sub cmdSummaryPAR_Click()

On Error GoTo Err_cmdSummaryPAR_Click



Dim stDocName As String



stDocName = "rptSummaryFindPAR"

DoCmd.Maximize

DoCmd.OpenReport stDocName, acPreview



Exit_cmdSummaryPAR_Click:

Exit Sub



Err_cmdSummaryPAR_Click:

MsgBox Err.Description

Resume Exit_cmdSummaryPAR_Click



End Sub

Private Sub cmdSummaryPct_Click()

On Error GoTo Err_cmdSummaryPct_Click



Dim stDocName As String



stDocName = "rptSummaryFindPct"

DoCmd.Maximize

DoCmd.OpenReport stDocName, acPreview



Exit_cmdSummaryPct_Click:

Exit Sub



Err_cmdSummaryPct_Click:

MsgBox Err.Description

Resume Exit_cmdSummaryPct_Click



End Sub



'

'New Combined Print of 3 reports

'





Private Sub cmdSummaryAll_Click()

On Error GoTo Err_cmdSummaryAll_Click



Dim stDocName As String



stDocName = "rptSummaryFindPar"

DoCmd.OpenReport stDocName, acNormal

stDocName = "rptSummaryFindPct"

DoCmd.OpenReport stDocName, acNormal

stDocName = "rptSummaryFindTck"

DoCmd.OpenReport stDocName, acNormal

Exit_cmdSummaryAll_Click:

Exit Sub



Err_cmdSummaryAll_Click:

MsgBox Err.Description

Resume Exit_cmdSummaryAll_Click

End Sub


The singular reports work fine. The last subroutine cmdSummaryAll_Click is
what I need help with. I used the Help and found nothing I could supress the
query prompts again. Do you need a test table, the code, or printed report
in PDF form?

Thank you,
Andy Pagorek

 

[John Spencer]

Instead of using a parameter prompt, add a control to your form where the
value you enter is stored. Then you can reference that control in the
underlying query.
Just replace {Your Current Parameter prompt] with [Forms]![Name of Your
Form]![Name of the Control] .

Another way to handle this is remove the parameter prompt from the query and
pass in the criteria when you call the report

Dim strWhere as String
strWhere = "[Portfolio Id]= '" & Me.NameOfControl & "'"
DoCmd.OpenReport stDocName, acPreview,, strWhere

The above assumes that Portfolio id is a text value. IF Portfolio id is
number field that drop the apostrophes
strWhere = "[Portfolio Id]= '" & Me.NameOfControl & "'"

You could avoid the control and use

StrWhere = InputBox("Which portfolio?","Portfolio Id")
If Len(strWhere>0 then)
strWhere = "[Portfolio Id]= '" & StrWhere & "'"
stDocName = "rptSummaryFindPar"
DoCmd.OpenReport stDocName, acNormal,, strWhere
stDocName = "rptSummaryFindPct"
DoCmd.OpenReport stDocName, acNormal,, strWhere
stDocName = "rptSummaryFindTck"
DoCmd.OpenReport stDocName, acNormal,, strWhere
END if

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