Select parameters directly from Combo Box.

[Frank Martin]

I have a Statement Report based on a query which contain two parameters
"mmmyy" and "AddressesID".

I want to open this Report in preview, by selecting a customer from a combo
box (the row source contains a list of the relevant parameters) on my
invoice form.

I have to code this combo box to select the two parameters automatically,
with or without a command button.

If I use a command button then the Preview Report code (via the wizard) is:

**

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

Dim stDocName As String

stDocName = "RptSTATEMENT"
DoCmd.OpenReport stDocName, acPreview

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

**

Can someone help me with the best code to accomplish this?
Please help, Frank

 

[John Vinson]

I have a Statement Report based on a query which contain two parameters
"mmmyy" and "AddressesID".

I want to open this Report in preview, by selecting a customer from a combo
box (the row source contains a list of the relevant parameters) on my
invoice form.

I have to code this combo box to select the two parameters automatically,
with or without a command button.

If I use a command button then the Preview Report code (via the wizard) is:

Base the Report on a Query using

=Forms![NameOfYourForm]![NameOfYourComboBox]

as criteria on the relevant fields. Your command button code will work
just fine - when the report opens, its query will retrieve its
parameters from the selected values on the form's combos.


John W. Vinson[MVP]

 

[Frank Martin]

I have a Statement Report based on a query which contain two parameters
"mmmyy" and "AddressesID".

I want to open this Report in preview, by selecting a customer from a
combo
box (the row source contains a list of the relevant parameters) on my
invoice form.

I have to code this combo box to select the two parameters automatically,
with or without a command button.

If I use a command button then the Preview Report code (via the wizard)
is:

Base the Report on a Query using

=Forms![NameOfYourForm]![NameOfYourComboBox]

as criteria on the relevant fields. Your command button code will work
just fine - when the report opens, its query will retrieve its
parameters from the selected values on the form's combos.


John W. Vinson[MVP]

Thanks, I tried this but I get a blank form only.
Do I have to tell the Combo box which columns in the report's query design
grid are the ones with the parameters?
The 'bound column' property only accepts one number.
Does the ComboBox's query have to be the same one as that of the reports?

 

[John Vinson]

Base the Report on a Query using

=Forms![NameOfYourForm]![NameOfYourComboBox]

as criteria on the relevant fields. Your command button code will work
just fine - when the report opens, its query will retrieve its
parameters from the selected values on the form's combos.


John W. Vinson[MVP]

Thanks, I tried this but I get a blank form only.
Do I have to tell the Combo box which columns in the report's query design
grid are the ones with the parameters?
The 'bound column' property only accepts one number.
Does the ComboBox's query have to be the same one as that of the reports?

Sorry! I was speaking in very compressed shorthand; should have
explained myself better.

The Form that you use to launch the Report should be UNBOUND - at the
very least, the combo boxes you're using as criteria should be
unbound. They should have NOTHING in their Control Source property.

Each combo box should be based on a Query which returns one each of
all of the valid criteria for the field. It seems you have a field
containing the date in mmmyy format (Jan05? What's the range of valid
dates? Is the table field you're searching Date or Text type?), and an
AddressID field. The latter is easy - you would have a table of
Addresses with AddressID as its Primary Key; base the Addresses combo
box on a Query with the AddressID as the bound column, and some
user-meaningful address as the first (or only) visible column. The
date combo should have the list of valid mmmyy dates, in the same
datatype as your table field.

The Query is simply using the user's choice from these combo boxes as
a criterion. Rather than creating a Query with

318

on the Criteria line under AddressID, and

Jan05

on the criteria line under the address field, you would use the
Forms!nameoftheform!nameofthecombo syntax to pass whatever value the
user has selected from the combo box on as a criterion. The Query
would itself contain all of the fields that you want to see on the
report; if the Report is based on a single table, just create a Query
selecting all of the fields in that table, and add the two criteria
references.


John W. Vinson[MVP]