Drop down lists for parameter queries... 3

[mikeinla]

I use interactive Access quite a bit (i.e. built in tools without programming). I frequently use drop down lists in my forms, etc. Recently I wanted to create a parameter query with a drop down list of possible choices. On the surface I thought it would be easy but have gone through my books and reference material and am stumped. Am I missing something obvious or is this harder than I thought?<br>Thanks so much

 

[DougP]

I would use a Combo boxes<br>In design view of your form make sure the wizard is depressed on the &quot;toolbar&quot; (wand with fairy dust)<br>Fisrt of all I would always have a table that has the information you want in your combo box.<br>Don't key in values. Its easier to change or add items later than open a form and edit the values in design view.<br>Also it can't be done if its an .MDE<br>If you Add a combo box it will ask questions <br>1st Where to get the values (choose the top radio button)<br>&quot;I want to get them from table or query<br>Click Next button at bottom.<br>2nd (choose your table or query)<br>Click Next<br>3rd Pick the fields you want (always Include the Key field or a unique ID in your choices. This make its faster for Access to pinpoint the specific record later. Also this field can be hidden from user)<br>Click Next<br>4th Size your columns to a sample of records<br>Click Next<br>5th Choose either option depending on your needs<br>Click Next<br>6th Choose a fieldname for a label of your combobox<br><br>You are done<br><br>Still in design view<br>Open properties of the combobox and you will see waht Access built for you<br>Look at the &quot;Row Source&quot; <br>Click it then click the 3 dots.<br>This will take you to the QBE grid for the combobox<br>You can make changes here just like in a regular query.<br>Sort by a particular column look a t a specific criteria etc.<br>to save your changes &quot;YOU MUST ALWAYS&quot; click the close button in the upper right of the QBE (X) and click &quot;Yes&quot;<br><br>OK <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Cornerstone]

If I understand your intention...<br><br>Add the combo box to your form. Then you will have a drop down list of choices from some table in your database. The last question of the Combo Box wizrd will ask you what you want to do with the selection. Choose to &quot;remember for later use&quot;<br><br>Then in your query set the criteria of the field you want the parameter for to: forms!yourformname!yourcomboboxname<br><br>Then just add a button to your form that opens the query. The query will get its criteria from whatever selection is currently showing in the combo box.

 

[jendendav]

I realize that my ability to use Access is severly inhibited by my lack of programming. However, I wanted to do exactly what mikeinla said they wanted to do. A parameter query isn't a form and a form is based on queries or tables right? The function of what I'm looking for is the same as Filter by Form but I prefer to use the parameter query. Please be patient with me. Why do I need a form to execute the query and what is the form based on?

Am I basing the form on the parameter query? Does opening the form cause it to ask me for the parameter. Now, for the hard part. The field my parameter is based on has data that is constantly changing. Doesn't that rule out list boxes and combo boxes? Filter by form doesn't care how ofted the data changes.

Is it possible to do a parameter query that functions like the filter by form?

Thanks. Jen [sig][/sig]

 

[LarryDeLaruelle]

When I first started using this method I also found it somewhat confusing. It's almost circular logic.

Create an interface form (where the user selects options) where you place text boxes or combo's to get the user supplied query parameters. You also put a command button on the form to open the report.

In the query on which the report is based and in the criteria cell where you want to place the parameter, right click and select &quot;Build&quot; which will take you to the Expression Builder. Double click on Forms, Double click on All Forms and then Double click on the interface form you created. This will give you a list (in the center box) of all the controls on the interface form.

In the expression builder window create the expression as you would normally (>, <, =, etc) but use the controls from the form rather than literals; by double clicking on the control it will be placed immediately to the right of the cursor posistion in the builder window and will contain the correct systax. For example, if you set text box control to prompt for start and end dates enter >= in the expression box and then double click on the control to set it as the value. What you will end up with is something that looks like this:

>=[Forms]![frmUserForm]![txtStart] And <=[Forms]![frmUserForm]![txtEnd]

Here's how it works. The command button on the user form calls the report. The report calls the underlying query. The underlying query looks at the user form to get the parameters. Sounds strange but it works.

You can reference combo boxes, text boxes, etc.

Hope this helps.
[sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[Quehay]

More kibitzing: The Access query building view allows you to create queries without knowing beans about SQL, the underlying query language. A parameter query prompts you to supply a value for a statement such as

Select CustomerName from tbl_Customers
Where CustomerName = [parameter]

You limit the results by suppling a particular value for last name rather than bringing up every record.

The Forms!frmCustomerRpts!cboCustName syntax tells the query to look for the parameter in the selected text of the combo box.

(In this instance) The query view is a dataset produced by the query. The form is merely a vehicle for getting a value to use in the query. [sig][/sig]

 

[jendendav]

Here’s the deal. I made the form and put just the field I search in a combo box. I also built a report which I hadn’t done because I just printed the data sheet view for this query. So I put a command button to open the report. All of that works just fine. But in order to get it to work I had to change the parameter query back to a select query. Otherwise, opening the form just brings up the parameter question. Then I discovered that while the combo box allowed me to select one value, the report is showing all values. Now I feel like I’ve been walking the circular logic track backwards. If you don’t mind offering a little extra help I believe I am close. [sig][/sig]

 

[jothy]

Hi:
Came in here by chance. Please look at Inside Microsoft Access Volume 8, Number 11, November 2000 issue or go to

http://www.elementkjournals.com/ima

or

http://www.peterssoftware.com/cr.htm.

I think the answer is there but it costs $35. I bought it as I have a need for this type solution to many of my applications.
Jothy [sig][/sig]

 

[PaulF]

Jen
A couple of thoughts

The form must remain open when the report runs whether you are sending a command with a Where Condition to open the report, or sending the results of the combobox to the query that is the RecordSource for the report.

Command to open a report (ReportName) based on the Last Name Field (LastName) selected in the combobox (cboName) on form FormName:


DoCmd.OpenReport &quot;ReportName&quot;, acViewPreview, , &quot;[LastName] = '&quot; & Forms!FormName.cboName & &quot;'&quot;


Criteria for the LastName Field in the Query:

= [Forms]![FormName]![cboName]

[sig][/sig]

 

[jendendav]

For those who have been following this thread, I got some really excellent help from Larry. My parameter query now works just the way I always hoped it would. I found by working through some of his ideas and samples that I was taking certain things for granted based on my lack of experience. I learned about a great deal more than just parameter queries.

If any of you folks posting to this thread were interested, I really think a number of newbies like me would benefit from a thread that discusses naming objects and controls because I learned things that I really didn't pick up from the books. [sig][/sig]