Drop-down box in Access 2007 parameter

Question

I want the Access parameter request to ask the user for a value (location in this case). When I type [Enter location] in the Criteria field, it works fine: I get a dialog box (Enter Parameter Value) with a text box and text (Enter Location). So far, so good. It works (result too).

But now I need the / combobox dropdown menu (instead of the text box) so that the user can select a location. I made a form and printed Forms![Form1]![CmbLocation] in the "Criteria" field.

Like this: http://office.microsoft.com/en-us/access/HA011170771033.aspx

But I still get the text box (with the link as a text label).

What am I doing wrong? Does anyone have any advice?

Answer 1

In addition to Albert’s suggestion, you may want to do this work inside the request itself so that it is “bootable”. To do this, you will need to write a function that returns the value selected in the form combo box. It will be something like this:

  Public Function ReturnMyCriterion() As Variant DoCmd.OpenForm "dlgGetCriterion", , , , , acDialog With Forms!dlgGetCriterion If .Tag <> "Cancel" Then ReturnMyCriterion = Nz(!cmbMyCombo, "*") End If Else ReturnMyCriterion = "*" End With Close acForm, "dlgGetCriterion" End Function 

(when you open a form with the acDialog switch, the code pauses until the form is open or visible, to get the value from the combo box, you must set the .Visible property to False. Do this in the AfterUpdate event in the combo box or in OK button, you also need the Cancel button, which sets the form .Tag property to “Cancel”, and then sets the form. Vibible property for False: this is all a relatively standard approach to working with dialog forms in Access).

Then you will make a criterion in your query:

  Like ReturnMyCriterion() 

That is, if you want to return all records, if no value is selected in the list box.

Answer 2

If you removed the parameter from your query and then re-typed exprsison into the query builder in the above form, then it should work.

So in the query builder in the criteria section just type

 [forms]![form1]![Combo4] 

Make sure you have the correct form name and list control name.

You do not need to enter anything else into the query builder. As indicated, delete the old parameter request that you previously used in the query builder.

Now open the form, select the combo box and now try to open the request, it should open without any prompts. Please note that this approach means that the form must be open, and the value will be selected in the combo box BEFORE you try to run the request. So, if you base the report on this query, then click the button to run the report in the same form as in the combo box. This pretty much guarantees that the form will be open before you try to run a query or report based on that query.