Hi, Amit.
How can I run a report displaying the same results as in the listbox by
clicking on a button on the form?
You have two choices:
1.) Open the report with the appropriate WHERE clause (this may be a bit
difficult to write all the code if there are multiple combo boxes that may or
may not be used for criteria selection); or
2.) Alter the report's record source, then open it for the user. (Note
that this alteration needs to be saved in order to avoid prompts to the user,
so this report needs to be "template" type report where you don't worry about
the current record source, because it will be changed every time the report
is run.)
For the first choice, the syntax would be like this:
DoCmd.OpenReport "rptStuff", acViewPreview, , "(State = '" &
Me!cboState.Column(0) & "')"
In this example, rptStuff is the name of the report, State is the name of
one of the fields in the report's record source, cboState is the name of the
combo box, and Column(0) is the first column of the combo box. The WHERE
clause will need to be constructed from the same controls that were used to
determine the list box RowSource Property, so this could get tricky.
For the second choice, the syntax would be like the following:
Private Sub OpenLstBoxRptBtn_Click()
On Error GoTo ErrHandler
Dim rpt As Report
DoCmd.OpenReport "rptStuff", acViewDesign, , , acHidden
Set rpt = Reports("rptStuff")
rpt.RecordSource = Me!lstList.RowSource
DoCmd.Close acReport, "rptStuff", acSaveYes
DoCmd.OpenReport "rptStuff", acViewPreview
CleanUp:
Set rpt = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in OpenLstBoxRptBtn_Click( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub
And yes, for the second choice, it's easier than the first choice, but it
requires exclusive access to the database application, so it wouldn't be
appropriate for a multiuser environment.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
