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.
|
|
|
| |||
| |||
|
I am running a query in Access. In the criteria section I added a prompt for a dialog box in square brackets [like this- [enter name]]. I want to change this dialog box to one that will allow the user to select the name by clicking on a drop down list. How can this be done? Greatly appreciate your help. Newtoaccess |
|
| |||
| |||
|
What you need to do is to create a form with the combo box on it that selects the name, then either use a button on the form to run the query, or trigger the query from the combo box's after update event. I don't think you can use a combo box from a query parameter prompt. But it won't make any difference to the user anyway. Just have the button that launches the query launch the form with the combo box instead. Then replace the criteria in the query with [Forms]![frmNewForm].[cboYourComboBox] HTH mmcdonal |
cassandra.flowers
Hi, I was wondering if it is possible [Using access] to have a query parameter as a drop down box rather than a text box? e.g. typing [Enter a name] as criteria for a query produces a box with a text box for you to enter the parameter. Can any body enlighten me on how to turn this text box into a drop down box? And whether it is possible? Kind regards, Cassandra
3 35878
David B
no bother at all !! With your form in design view make sure the wizard is turned on and select combo box from the tool bar. The wizard will guide you through the set up In your query criteria put something like -- [Forms]![yourformname]![yourcomboname] The query will look at the first column in the combo some make sure that is doing what you want HTH David B cassandra.flowers wrote in message
news:c5**********@sparta.btinternet.com...
Hi, I was wondering if it is possible [Using access] to have a query parameter as a drop down box rather than a text box? e.g. typing [Enter a name] as criteria for a query produces a box with a text box for you to enter the parameter. Can any body enlighten me on how to turn this text box into a drop down box? And whether it is possible? Kind regards, Cassandra
Peter Russell
You have to design a form to ask for the parameter and then set the criteria to the value of combo box on the form. If the form is called myform and the combo box mycombo then the criteria [for equality] is: Forms!myform!mycombo You use a button on the form to run the query. Peter Russell cassandra.flowers previously wrote:
I was wondering if it is possible [Using access] to have a query parameter as a drop down box rather than a text box? e.g. typing [Enter a name] as criteria for a query produces a box with a text box for you to enter the parameter. Can any body enlighten me on how to turn this text box into a drop down box? And whether it is possible?
Don Leverton
Hi Cassandra, Not that there is anything at all wrong with the suggestions made by David and Peter, but I am in the habit now of taking this one step further... The problems [annoyances, really] with this form / query method are: 1.] It is possible to open the query without having the form open, which causes a message to be displayed prompting for the form reference parameter. 2.] Assuming that I now have the form open, have specified a parameter, and have received the desired results in the query. Now I want to specify a different parameter and see the new results. I have to close the query, return to the form, specify something else, then re-open the query. I recall thinking "There's just gotta be a better way!" ... and there is. What I do now is to use an unbound main form that collects the criteria, and have the results immediately displayed in a datasheet-style subform on the same form! I build an SQL string [the same thing that using a "query" does] using code, then use that SQL as the recordsource for the subform. Changing the subform's recorsource forces a requery of the subform, so the results are nearly instantaneously displayed. You can also [optionally] open a report based on the same SQL string in order to get a "hard copy" of the same data that is displayed in the subform. I call this the "WYSIWYG" [What You See Is What You Get] approach to reporting. If you're interested in this method, have a look at one of my recent posts on this.
//groups.google.ca/groups?q=Don...lgrps13&rnum=3
Don "cassandra.flowers" wrote in messagenews:c5**********@sparta.btinternet.com...
Hi, I was wondering if it is possible [Using access] to have a query parameter as a drop down box rather than a text box? e.g. typing [Enter a name] as criteria for a query produces a box with a text box for you to enter the parameter. Can any body enlighten me on how to turn this text box into a drop down box? And whether it is possible? Kind regards, Cassandra
Replies have been disabled for this discussion.