Dropdown in Access 2007 parameter query

Question

Hello, I want a Access parameter query to ask an user for a value (a 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 textbox and my text (Enter Location). So far, so good. This works (the result also).

But now I want a dropdown/combobox (instead of a textbox ) for the user to pick a location. I made a form and type Forms![Form1]![CmbLocation] in the Criteria field.

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

But I still get a textbox (with the reference as textlabel).

What am I doing wrong? Has anybody any advice?

Answer 1

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

So, in the query builder, in the criteria section just type in

[forms]![form1]![Combo4]

Make sure you have the right form name and control name of the combo box.

You should not need to type in anything else into the query builder. As mentoned, remove the old parameter prompt you previous had in the query builder.

Now, open the form, select the combo box, and now try opening the query, it should open without any prompts. Note this approach means that the form will have to be open, and the combo box will have be selected a value BEFORE you attempt to launch the query. So, if you basing a report on this query, then palce the button to launch the report on the same form as the one with combo box. This quite much ensures that the form will be open before you attempt to launch a query or report that is based on that query

Answer 2

In addition to Albert's suggestion, you might want to make this work within the query itself, so that it's "bootstrappable." To do that, you'd have to write function that returns the value chosen in the combo box on the form. It would 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 opening a form with the acDialog switch, the code pauses as long as the form is open or visible; to get the value out of the combo box, you have to set the form's .Visible property to False. You could do this in the AfterUpdate event of the combo box, or in the OK button. You'd also want a Cancel button that set's the form's .Tag property to "Cancel" and then sets the form's .Visible property to False; this is all relatively a standard approach to working with dialog forms in Access).

You'd then make the criterion in your query be:

  Like ReturnMyCriterion()

That is, assuming you want to return all records if no value is chosen in the combo box.