4

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?

waanders
  • 8,907
  • 22
  • 70
  • 102

2 Answers2

3

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.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • The same place you put the parameter that you're trying to replace. – David-W-Fenton May 20 '10 at 23:18
  • I misunderstood the question, I think. You have to put the code in a standalone module. – David-W-Fenton May 25 '10 at 17:48
  • @David-W-Fenton, could you take a look at this question I posted yesterday as it is closely related, thanks: http://stackoverflow.com/questions/20934066/access-call-macro-from-query-opening-a-form-run-time-error-2486-you-cant-c – Chopo87 Jan 06 '14 at 12:58
2

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

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Thanks, opening the form before running the query makes the difference. But I have to make my own form, what I really, really what is the standard MS-Access "Enter Parameter Value" dialog to show a combo-box instead of a text field. Is that possible? – waanders May 20 '10 at 10:49
  • You can accomplish this if you flip the question around. That parameter is likely for a report. So, simply launch a form with the combo box and then have the form launch the report. Eg: docmd.OpenReport "report name",acViewPreview,,"some id = " & me.MyComboBox So it is much better to remove the parameter from the query else it will huge control your life here. You not be able to use that query anywhere else with that hard coded parameter. Removing all parameters from the query vastly increases flexibility here and allows you to supply any parameter in your code as above shows. – Albert D. Kallal May 20 '10 at 14:16
  • I don't like hardwiring a form to open a single report, either, but it's easy enough to open a form as a dialog from some other location, and collect the criteria you need to open the report. That way the form doesn't need to know anything about where it's called from or what its criteria are used for. – David-W-Fenton May 20 '10 at 23:19