Adding a drop down list to the criteria in a query

[Monstermechanic]

I am attempting to generate a report. When building the query I was hoping to add a drop down list to the criteria window. Is this possible?

 

[vbaInet]

Use a form and add a combo box (from the toolbox) to it.

 

[dkinley]

Pretty much when you click the comman button for the report - have it open a form. On that form you have your combo box and any other parameters you want to use.

Change the query criteria to reference the form (Forms!frmFormName!ctlControl).

Then use a command button to open the report.

HTH,
-dk

 

[rexmorgan]

I am having trouble with this very same thing. I have a query called QrycboCounty. I have a form called frmReports. I have a combo box on frmReports called cboCounty. I have a command button on the frmReports from also. I am attempting to pull all relevant data about a particular person based on the county they live in. For example I want to be able to select the county from the combo box and click the command button to run the query.

In design view I have added all of the pertinent fields that I wish to see in the query ie FirstName, LastName, Conty, etc. In the criteria section for the County field I have typed in [Forms]![frmReports]![cboCounty]. I have the command button linked to run the query when clicked. However, when the query is run it comes up empty. Any ideas what I am doing wrong here. Thanks in advance.

 

[vbaInet]

1. Create your report as normal. Don't apply any filters to the report or criteria to it's underlying record source so that it shows ALL records.
2. Use the WHERE argument of the OpenForm method to filter the report on loading.

Here's a link on how to open a form filtered by an ID.
http://baldyweb.com/wherecondition.htm

It's the same concept with opening filtered reports. Just change OpenForm to OpenReport.

If each County has an ID, you would use the ID to filter the report based on that ID as long as you included the ID in the underlying record source of the report. So it could look like:

Docmd.openreport "ReportName", , ,"[CountyID]= " & cboCounty.Column(0)

Or

Docmd.openreport "ReportName", , ,"[CountyID]= " & cboCounty.value

 

[rexmorgan]

Not exactly sure what I was doing wrong. I started over with a new form and applied the combo boxes and everything worked. I must have inadvertently left something out the first time. Although for the life of me I cannot figure out what it was.

 

[vbaInet]

You probably missed out a quote or something.