Drop down or list of queries to select and run in MS Access 2007 form

Gav · #1 (**permalink**) 06-15-11, 07:10

Hi, I am tyring to work out how I can add a drop down menu to a form which lists all or some of the queries in the db.
Once the the desired query is selected from the drop down then I would like to use a command button to run the query.
Any help would be greatly appreciated.
Thanks,
Gav

christyxo · #2 (**permalink**) 06-15-11, 12:24

Put this SQL behind the Row Source of your Drop Down List;

Code:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Flags)<>3))
ORDER BY MSysObjects.Name;

Object Type refers to the following

-32764 = Reports
-32768 = Forms
5 = Queries
6 = Tables

You would then have this code behind your button

Code:

If IsNull(Me.List1.Value) = True Then
MsgBox "Please Select a Query from the table!"
Else
DoCmd.OpenQuery Me.List1.Value, , acReadOnly
End If

Gav · #3 (**permalink**) 06-15-11, 12:53

Thanks Christy,
That works great.
Gav

Gav · #4 (**permalink**) 06-15-11, 13:08

Hi,
I have a follow up question for this. Is it possible to manually enter only some of the queries into the drop down box rather than call all of them as per Christy's answer above.
I would like to separate the queries into groups across a few different drop downs on the form.
Thanks again,
Gav

Internet Services

Web Development

Digital Marketing

Consumer Tech