Queries Drop Down List

[janed]

Ok, I will try to be as clear as possible but this is quite difficult to explain.
I have made a query that needs to be used as a query, i.e. I have it set so that a parameter box appears asking the user to enter a department.

However, I also need this query to be attatched to a report. I have done this and it has worked out very well. When I open the report a pop-up form appears and I am given a drop down list containing all the departments, which there are quite a lot of.

I have got two problems. First of all, because of the form being attatched to the query for the report, if I just run the query, the parameter box reads "Forms!Records: Deparment" instead of "Please enter department". This is not what i want. To avoid this problem I made two seperate queries instead of just one so that when I run the query it now says "Please enter department". This is what I want but I do not feel that it is very professional.

The second problem is that there are a lot of departments and it is not very efficient that the user has to type in the exact word to get any returns on the query. I would like the user to be able to type say the first three letters and for the query to read this, eg. user enters "bus", query returns "business". If this is not possible, i would be willing to settle for a drop down box in the parameter box.

Please Help me!!

 

[ColinEssex]

I think I can help you with the second problem. In the query criteria type this:
Like [Enter first 3 chrs of dept] & "*"
What will happen is that the query will match the letters up with the table. The only problem is that if you have 2 depts with the same first 3 letters (like Maintanence and Main Office), then it will select both.

Hope this helps you
Col

 

[David R]

If you can always run your query via the popup form you built, you will solve both your problems, because you will always have that nice dropdown list you coded.

You're trying to run a report right? So have your switchboard open the popup form, then a button on your popup form run the report, which is based on the query which gets its data from the popup form which is already open...make sense? The button on the switchboard can say "Run Department Report" or whatever, your users don't have to know it's actually opening a form first.

Oh, and in your command button for running the report from the popup form, put this as the last line:
DoCmd.Close "Popup Form Name"

O.K. I think I reread your question and see what the trouble is. You want to be able to run it as a straight query, as well as running a report based on the query?
Very similar solution. Just put two buttons on your popup form: "Run Query" and "Run Report" (and "Cancel"). You're still using your popup form for the handy combo box.

Good luck,
David R


[This message has been edited by David R (edited 05-01-2002).]

 

[janed]

Thank you both. I think my problem is now solved. It was so simple, I just couldn't see what to do.
Thanks again!