Combo Boxes in parameter queries

[staternj]

I have a parameter query in Access 2000. The user identifies a start date and end date and enters an organization name in order to run the query. Is there a way that I can have the user select the organization name from a pull down list or combo box while he/she is responding to the Enter Parameter prompts instead of having to type it in?

Thanks,
Peter

 

[ghubbell]

Hi Peter,
Make a single simple form, unbound- not attached to any table, set its pop up property to Yes, sizeable borders with a controlbox and min/max buttons to min only. With the wizard on (wand & stars depressed) place a combo box on your form and choose "I want the combo to look up data from a table". Next, select the table that your query is based off of. Name your new combobox "CboSelect".

Take a moment and look at its "limit to list" property. To get there, double-click on the combobox and a properties dialog box will pop up. Select the Data tab. This must be set to Yes. You should also set a default value to one of the "organization names".

You could do exactly the same for your start dates and end dates however base them off of a query that shows only the unique dates (query design, doubleclick on the query screen to show the queries properties and set Unique Values to yes)
Name the combos CboStart and CboEnd . A default value for both would be wise.

Again using the wizard on your form create a command button: (under miscellaneous-Run query, and choose your query name.
Close and save your form.

In your query in your criteria row under "organization name", type:
=Forms![EnterTheNameOfTheFormYouJustMade]![CboSelect]

In your query in your criteria row under "TheNameOfYourDateField", type:
Between Forms![EnterTheNameOfTheFormYouJustMade]![CboStart] And Forms![EnterTheNameOfTheFormYouJustMade]![CboEnd]

Close and save your query.
Open your form and select your choices. Click your command button and your query should run! Gord
ghubbell@total.net

 

[staternj]

Gord,

Thanks for the reply... I'm dangerously close to getting this to work but if I may, let me run something past you. I developed the form as you described with the combo box and the command button. I can get the pull down values to select from which I was seeking and I made the appropriate entry in the criteria box of my query as you also described. However, when I hit the command button, I enter the date range (I'm still doing this manually...), the query runs, but it doesn't return any records although I know there are about 10 that meet the criteria in the database.

When I run the query directly from the query tab, I get prompted to enter the date ranges and then to enter an organization name (the prompt actually shows the criteria: [Forms]![Query Runner]![CboSelect1]. However, when I choose an org name here it runs properly and extracts the expected records.

Am I missing something elementary here?

Thanks for your time.

P.S. I'm not a programmer, just a user. Would the solution you provided be in the skill set of a competent user or does it come from your programming background/development experience?

Peter

 

[ghubbell]

Hi Peter, you are most welcome and, a very competent user! I just ran a few tests here and have found no signifigant errors. I suspect the combobox wiz is hiding a key column on you therefore probably not reading the correct column in your combo "CboSelect1"
In each Combo, click on the row source property and you'll see an ellipse "..." button. click this to open the query. In each case you only need 1 column, the real value, no ID numbers. If you find a query with say, an ID number to the left of your organization name, delete that ID column from the query, then return to the combo, check that the column count is set to 1, the column width are 2.5cm (1&quot and not like 0;2.5cm (0 is hiding a column and that would be a two column combo). The Bound column in all should be 1. Give it a go! I'll check in tomorrow. Oh and, my pleasure!
Gord
ghubbell@total.net