Use Drop Down menu in a Query?

[klix]

Hello, I'm trying to create a query parameter which provides a drop down list rather than a blank form to manually enter the parameter. The main reason is to avoid mistakes in typing since I will not be the only person using this db.

I've searched and searched the net for a solution but just can't find one that works. I tried to enter '[Forms]![formname]![control]' in the Criteria to no avail. I'm guessing creating a list requires something more complicated than that.

I'd appreciate any help!

 

[pbaldy]

That should work (without the quotes), presuming the form is open. When you say it doesn't work, what happens?

 

[klix]

First, I didn't know the form has to be open for this method to work. I'm pretty sure the form was open anyway when I was testing this but just to be sure I just retried it. What happens is nothing. Whenever I run the query I get no results except one blank record. No pop up window - no text box or combo box - pops up before the results are displayed either.

To give you some more information about the form (in case it helps) I used the wizard to create it. It uses values from a table called 'Accounts'. The table 'General Journal' (which is where the problematic query pulls results from) has a field which also takes lookup values from the 'Accounts' table. When I created the form I titled it 'Account' and named the label for the drop down list 'Account' also. So my criteria syntax in the query is: [Forms]![Account]![Account]. However, looking at the properties this morning, I realize the combo box doesn't have a control source listed.

That's where I am now. By the way, great quote below your screen name! It's becoming closer to reality around here.

 

[pbaldy]

Two thoughts come to mind. First, make sure you've moved focus off the combo box before running the query. If you just type something in and then run the query, the value won't have been updated yet.

Second, if the combo has more than one field in the rowsource, check that the bound column is the value the query will be looking for. If you use table level lookup fields, this can trip you up. The combo does not need a control source for this to work.

Regarding John Galt; sad but true, isn't it?

 

[klix]

Absolutely sad, more and more true. What ever happened to the hard work that built this country?...

I'm not sure what you mean by 'moved focus off the combo box'. I've tried to run it with the form open; form closed; saved, closed, and reopened the db and tried again. Nothing. Never a drop down list

The combo box doesn't have more than one field, just the one. I've tried to make the form both bound (to that field) and unbound. As you confirmed, I didn't think it needed a control source. In either case when I've run the query no drop down list appears for me to select which account I want to display.

 

[pbaldy]

Can you post the db? When you say "no drop down list appears", it appears you may have misunderstood my poorly worded initial response. The query will not open the form. You make a selection on the form, then run the query. The way it generally would work, presuming this query is the source for a report, is that you'd have your combo box and a button on the form. The user would make a selection from the combo, then click the button to open the report. The query would use the selection made in the combo to filter the report.

 

[klix]

Your response wasn't necessarily poorly worded - more my understanding of this process was misguided. Your explanation makes perfect sense but I still wouldn't know how to design the process. I'm attaching it for you. (I hope I did it right. If my zipping doesn't work let me know and I'll post it on my website.) Thanks.

 

[pbaldy]

You have the name of the combo wrong. Its label is "Account", but the name of the combo itself is Combo0. Try this as the criteria:

[Forms]![Account]![combo0]

 

[klix]

I thought I'd tried that earlier but to be sure I changed the criteria as you indicated above. Again no result. Like I said before though, I'm not sure how to make the form use the query to find the data and display it on a report. I'll try to tinker around but I have limited knowledge, so if you could help me with that syntax/process I'd appreciate it.

 

[klix]

Ok I tinkered. I was able to create a command button and link things up so that the account I selected on the form was filtered by the query and displayed in the report.

However I just want to confirm that there's no way I can get a drop down list in my query parameter rather than an empty text box. Or even just get a drop down list when I open the report (that displays an empty text box too when I try to open it by itself).

 

[pbaldy]

Confirmed; the only way you can get a drop down list is with a form you create, such as in your sample.

 

[klix]

Ok then. Since I was able to get the result I was (ultimately) looking for, I have to thank you for the help.