Making criteria parameter a combo box

[ehsguy77]

I have a query that has the criteria [Enter route#:]. When my user runs the query from a command button, I would like for the parameter value window to provide a combo box that contains the route #s from which she needs to choose. That way she never has the option to type in a route number that doesn't exist (and gives her a null set of values back).

Does anyone know how to do this? No forms will be built on this query.

 

[scriverb]

If you want a combobox with the legitimate codes for picking then you MUST create a small popup form. This is simple process and I can help you with the process. This form would open upon requesting the query be run. Only and combobox and a Continue button would be on the form. The Combobox would already be dropped down. After the selection and the user sight verifies that it is correct a simple click of the Continue button would then run the query.

Get back with me if you would like assistance doing this.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

After reading your thread again I see that we could put the combobox disgreetly behind the command button that is clicked to run the query. It could become visible at that time and all for the valid selection. After selection the query would continue to run.

This could all be done on the original form.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[ehsguy77]

Sounds great, Bob. Any help you can offer would be greatly appreciated. My associate and I considered putting an unbound combo box on a switchboard from which the user could choose their criteria, and a command button on the side of the combo box that would run a query, and use the data selected in the combo box as the query criteria. Is that possible?

 

[scriverb]

On your form your should have a command button and a combobox. The combobox is set to .visible = false. The RowSource for the combobox would be a SQL string that looks something like this example:

Select A.RouteNumber FROM [i]yourtablename[/i] as A GROUP BY A.RouteNumber ORDER BY A.RouteNumber;

This code would give you one row for every route number in the table and sort by that field. Now you combobox has only valid route numbers for selection.

Make you CmdButton the same width as the combobox and place the command button directly on top of the combobox. The Caption for the command button can be of your choosing.

The OnClick event procedure of the CommandButton would be as follows:

Me![i]comboboxname[/i].visible = True
Me![i]comboboxname[/i].setfocus
Me![i]comboboxname[/i].dropdown
Me![i]commandbuttonname[/i].visible = False

The AfterUpdate event procedure of the Combobox would be as follows:

DoCmd.OpenQuery "[i]qryYourQuerynName/i]"
Me![i]commandbuttonname[/i].visible = True
Me![i]commandbuttonname[/i].setfocus
Me![i]comboboxname[/i].visible = True
Me![i]comboboxname[/i].visible = False

We may have a little problem right at the end here. You see the combobox must remain visible with the value picked for the query to run. We have run the query first and then being the housekeeping chore of closing the combobox and setting to visible = false and moving the focus back to the command button. If the query is not done with the combobox it may cause a problem but can be fixed with a global variable and a function. Give it a try this way and if it errors out we will add the additional small amount of code.

You query should use the reference to the combobox on the form for its critieria.

FORMS![i]formname.comboboxname[/i]

Let me know how this works.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]