Passing Parameters from Forms to reports

[fiona.innes]

Hi,

I currently have a query called qryDetails, and form called
frmWhatDates and a report rptDetails.
The form frmWhatDates has 2 txt boxes StartDate and EndDate and also a
combo box cmbCatergory. This allows users to select the criteria for
the report to be based upon. Only problem is Iam unsure of the code to
put in the report, query and form for this to work as the code i got
form the microsoft website doesnt seem to work.

Fiona

 

[fredg]

Hi,

I currently have a query called qryDetails, and form called
frmWhatDates and a report rptDetails.
The form frmWhatDates has 2 txt boxes StartDate and EndDate and also a
combo box cmbCatergory. This allows users to select the criteria for
the report to be based upon. Only problem is Iam unsure of the code to
put in the report, query and form for this to work as the code i got
form the microsoft website doesnt seem to work.

Fiona

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a CategoryID number you need as criteria.

Next, make a new unbound form.
Add a combo box that will show the CategoryID field as well as the
Category Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CategoryID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CategoryID field.
Name this Combo Box "cboFindCategory".

Add 2 text controls to the form.
Set their Format property to any valid date format.
Name one control "StartDate". Name the other control "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CategoryID field
criteria line write:
forms!ParamForm!cboFindCategory

As criteria on the query's Date field, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Open the Report in design view.
Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the Category Name in the combo box.
Enter the Starting date and the Ending dates wanted.
Click the command button.

The Report will display just those records selected within the time
span entered.
When the Report closes it will close the form.

 

[alemanp]

Hi,

I currently have a query called qryDetails, and form called
frmWhatDates and a report rptDetails.
The form frmWhatDates has 2 txt boxes StartDate and EndDate and also a
combo box cmbCatergory. This allows users to select the criteria for
the report to be based upon. Only problem is Iam unsure of the code to
put in the report, query and form for this to work as the code i got
form the microsoft website doesnt seem to work.

Fiona

Fiona,
I have a few apps that use the form to drive the criteria on reports.
The following method works for me.
First of all, lets limit the code to residing in the form and a bas
module.

I suggest you place a "Print" and "Preview" button on your
frmWhatDates. In the Click event of each button, there should be
something like this:
sub Preview_Click()

subSetDetailsReportSource Me.StartDate, Me.EndDate, me.cmbCatergory
Docmd.OpenReport "rptDetails", acPreview

End Sub

In the basReport module:

Public Sub subSetDetailsReportSource (dStartDate as DateTime, dEndDate
as DateTime, strCatergory as String)
Dim qdf as QueryDef
Dim strSQL as String


strSQL = "SELECT tblOrderHeaders.CustomerName,
tblOrderHeaders.CustomerContact, " & _
tblOrderDetails.OrderDate, tblOrderDetails.ItemNo,
tblOrderDetails.ItemPrice " & _
"FROM tblOrderHeaders INNER JOIN tblOrderDetails On
tblOrderHeaders.CustomerNumber = tblOrderDetails.CustomerNumber " & _
"WHERE (((tblOrderDetails.OrderDate) Between #" & dStartDate &
"# And #" & dEndDate & "#) " & _
"And tblOrderDetails.Catergory = '" & strCatergory & "');"
Set qdf = CurrentDb.QueryDefs("qryDetails")
qdf.SQL = strSQL

End Sub

I'm just making an example with the tables and such. There should be
enough to surmise how it applies to your tables. Let me know this
code differs from the Microsoft website.

HTH

Pete Aleman
Vancouver WA USA

 

[Klatuu]

The easiest way to filter a report is to NOT filter by the query or table.
The OpenReport method has a Where argument you can use for filtering. It is
just like creating a Where clause for the query without the word where. You
use the name of the field in the report's record source and the value of the
control you want to pass as the filter. For example.

Dim strWhere As String

strWhere = "[Category] = """ & Me.cmbCatergory & " AND [SomeDateField]
BETWEEN #" & Me.StartDAte & "# AND #" & Me.EndDate & "#"

Docmd.OpenReport "MyReportName", , , strWhere