Creating a drop-down list to select and run a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to use a drop down list to select the underlying query. Users enter
their parameter in the field above the list, hit enter and the query runs. I
don't know how to set the parameter (to be entered) in the form to equal the
form field. I have the combo box loaded with the queries available but when
I click my search button the parameter dialog box in the querey opens asking
for the parameter instead of running the query off of what was entered in the
'search for' field.

Can anyone help me with this?
 
I want to use a drop down list to select the underlying query. Users enter
their parameter in the field above the list, hit enter and the query runs. I
don't know how to set the parameter (to be entered) in the form to equal the
form field. I have the combo box loaded with the queries available but when
I click my search button the parameter dialog box in the querey opens asking
for the parameter instead of running the query off of what was entered in the
'search for' field.

Can anyone help me with this?
Click to expand...

You seem to have most of this done.
In the query, change the parameter prompt to:

Forms!FormName!ControlNameOnForm

Then open the form, enter the criteria in the control, and select the
query.
Remember to close the form afterwards, or do it in the same code that
opens the query:

DoCmd.OpenQuery Me!ComboName
DoCmd.Close acForm, Me.Name
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Change the queries to "look for" the parameters in the form you are
running the queries from:

JET SQL:

PARAMETERS Forms!form_name!control_name Date;
SELECT ...
FROM ...
WHERE date_column = Forms!form_name!control_name

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQd7QPoechKqOuFEgEQJG6wCfZQdUNCDEjcGQIFdmYMGgt6O52vIAn1NQ
wQa182Y5lbr56ZJElPemtb6D
=cn0C
-----END PGP SIGNATURE-----
 
thank you fred. i will work on this over the weekend. one other thing ...
in the drop down list I use the names of the queries as i have them written
in the database window i.e. 'qryOrderNum'. can these names be modified in
the combo box to look cleaner ? i.e. 'Order Number'. ??
 
i am new to the ms community so forgive me if this is in correct. first of
all thank you MG. i hav one other question ...
in the drop down list I use the names of the queries as i have them written
in the database window i.e. 'qryOrderNum'. can these names be modified in
the combo box to look cleaner ? i.e. 'Order Number'. ??
 
thank you fred. i will work on this over the weekend. one other thing ...
in the drop down list I use the names of the queries as i have them written
in the database window i.e. 'qryOrderNum'. can these names be modified in
the combo box to look cleaner ? i.e. 'Order Number'. ??
Click to expand...

One way would be to create a new table with 2 fields:
Table Name tblQueries
QueryName Text Indexed No Duplicates
UserFriendlyName Text

Set the Combo box RowSourceType to Table/Query
As Rowsource for the combo box:
"Select tblQueries.* from tblQueries Order by UserFriendlyName;"

Make the bound column 1.
Set the column widths to 0";1"
Column Count to 2
AutoExpand to Yes.
LimitToList to Yes.

Code the AfterUpdate event:
DoCmd.OpenQuery Me!ComboName

The user will only see the user friendly name and the correct query
will open.
You'll need to add to the table whenever a new query is added to the
Database.
 
I'm trying to do the same thing and your instructions so far have been very
helpful.

However, when I open the form, select from the unbound box then click the
command button, the query runs, but it is still prompting with a dialog box
that says "Forms![frmQueryforTeamLeaderreport]![Combo4]

frmQueryforTeamLeaderreport is the form with the unbound box and Combo4 is
that box.

Is this enough info to help me know what I'm doing wrong?

thanks
 
I'm trying to do the same thing and your instructions so far have been very
helpful.

However, when I open the form, select from the unbound box then click the
command button, the query runs, but it is still prompting with a dialog box
that says "Forms![frmQueryforTeamLeaderreport]![Combo4]

frmQueryforTeamLeaderreport is the form with the unbound box and Combo4 is
that box.

Is this enough info to help me know what I'm doing wrong?

thanks

:
Click to expand...
*** snipped ***

1) The form must be open when the query is run.
2) Did you correctly spell the table and control names?

If you still have difficulty, post back with the Query's actual SQL
Where clause, as well as the code behind the form's command button
that opens the query.
 
Thank you Fred. That worked very nicely.
In the select query I had to take out the ORDER BY UserFriendlyName. I kept
getting promted for that field. Perhaps I should check my spelling too.
Thanks again though. This is what I was looking for.

Steve
 
Hi Fred ... This is coming along nicely but now I have another problem. First
of all I want users to be able to enter their criteria and then (if
necessary) be able to click a button to clear both fields and start over. The
problem I am having is that once a choice has been made in the 'catagory'
field there is no turning back. Access keeps promting for a search criteria
(and rightly so) because of the parameter query behind it but this is
undesireable.

What can be done to avoid this happening?

Thanks ahead,
Steve
 
Hi Fred ... This is coming along nicely but now I have another problem. First
of all I want users to be able to enter their criteria and then (if
necessary) be able to click a button to clear both fields and start over. The
problem I am having is that once a choice has been made in the 'catagory'
field there is no turning back. Access keeps promting for a search criteria
(and rightly so) because of the parameter query behind it but this is
undesireable.

What can be done to avoid this happening?

Thanks ahead,
Steve
*** snipped ***
Click to expand...

If I understand you correctly, code a command button click event:
Me![ControlName] = Null
 
Thank you Fred. In that case the seperate table that holds the query names
and the user friendly names should have a record to pull up the 'null' field.
Is that correct?

fredg said:
Hi Fred ... This is coming along nicely but now I have another problem. First
of all I want users to be able to enter their criteria and then (if
necessary) be able to click a button to clear both fields and start over. The
problem I am having is that once a choice has been made in the 'catagory'
field there is no turning back. Access keeps promting for a search criteria
(and rightly so) because of the parameter query behind it but this is
undesireable.

What can be done to avoid this happening?

Thanks ahead,
Steve
*** snipped ***
Click to expand...

If I understand you correctly, code a command button click event:
Me![ControlName] = Null
Click to expand...
 
Fred this is coming along very nicely. My next hurdle (or mystery) is that
when I attempt a wildcard search the query brings back no results. Very
frustrating. What do think is the problem?

Thanks,
Steve
 
Back
Top