Pull down Combo to run report ???

[vistor]

Hi,

Currently I have a like query set up which the report is based on. User hits run report button, they are asked to enter company name and report runs. Very standard.

I've been asked if there is a way to have a pull down which lists the choices, just 14, and the report would run based on the choice.

I have done a pull down combo box on a data entry form to update a table, but can't think how/if this can be done for a report. Any guidance would be great.

 

[sko]

All you need is a form with an unbound combo box who's row source is your company table and a button to run the report. Then modify your query so it looks in your combo box for your criteria.

In your query replace [Enter Company Name] with
Forms![YourFormName].[YourComboboxName]

Post back if you need details

HTH

 

[vistor]

HTH,

I've done as you suggested. Form has a cobmo boxed whose row source is the company table. The button on the form runs a Macro which opens the report, based on the query.

When I hit the button to run the report I get a dialog box asking to Enter Parameter Value. The criteria from the query displays on the dialog box,
Forms![frmCompanyPulldown].[cmbCompanyPulldown]

FYI: the query is based on an Orders table. The Company field on this query is from the Orders table, not the Company Table. This field on the Orders table is from a combox box on the data entry form, which gets it's value From the Company table but is Stored on the Orders table.

So my query is like this:
Field: Company
Table: Orders
Criteria: [Forms]![frmCompanyPulldown].[cmbCompanyPulldown]

the query itself is putting the brackets around the word Forms, could this be the problem?

 

[sko]

Ok, I've probably made too many assumptions here.

Does your company table include a company id as well as the name?

Is the company name or company id being saved in your orders table?

If we are working with the id then we need to make sure the id is the bound column in your combo box. So...

Open your combo box properties.
In the row source, rather than selecting the company table, hit the ... button which brings up the query builder. Bring in company ID and company name. Sort in ascending order by company name and hit ok.

Back to combo box properties
1. Set column count 2
2. Set column width 0";1"
3. Set bound column 1

We are saying the combo box has 2 columns CompanyID and CompanyName. We want to hide the companyID because it would be meaningless to the user. Instead we will show the company name. But, our query needs to use the companyID so that's why we bind column 1.

Are we getting closer?

 

[sko]

May also be a matter of a typo.
Right-click on your criteria and choose build.
Open forms> find frmCompanyPullDown > cmbCompanyCompanyPullDown > Value, then paste.

 

[eradtke]

SELECT Orders.CustomerID
FROM Orders
GROUP BY Orders.CustomerID;

query will be something liek this for the combo box correct?

Example:
your other query will be like this.
SELECT [Orders].[CompanyID]
FROM Orders
GROUP BY [Orders].[CompanyID];


the query to run for the command_click event would be somehting like this:

SELECT Orders.*
FROM Orders
WHERE (((Orders.CompanyID)=[Forms]![Form1]![CompanyID_Label]));

 

[eradtke]

ignore last post I totaly screwed it up

 

[vistor]

SKO,

Tried yor query builder. Still getting the same thing when I hit the button to run the report:

When I hit the button to run the report I get a dialog box asking to Enter Parameter Value. The criteria from the query displays on the dialog box,
Forms![frmCompanyPulldown].[cmbCompanyPulldown]

FYI: the company table only has the company name, not an ID

 

[vistor]

SKO,

I think I have it now. I created a test db with the tables, query, report, macro and form and it is working. I must have some tweaking to do on my real db.

Thank you so much for your help.

CDWD