Queries in a Dropdown

message from =?Utf-8?B?TWF0dA==?= on 2 Jun 2004

Hey,

I was wondering if there is a way to have the titles of the queries appear in a dropdown box, and then run a command button/report from the selection of the query. I tried the following code but it gives me every report, query, table, etc etc in my dropdown box. Is there a way to pick which queries I want in my dropdown? Thank you very much.

Set the combo box Row Source Type property to Table/Query.
Set the Rowsource to:

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],1))<>"~") AND ((MSysObjects.Type)=5)) ORDER BY
MSysObjects.Name;

Set the bound column to 1.
Set the ColumnWidths property to 1"
Set the LimitTo List to Yes.
Set the AutoExpand to Yes.

Then, if you wanted to run any one particular query, based upon it
being selected in this combo box, code the AfteUpdate event:
DoCmd.OpenQuery Me!ComboName

Graham R Seach replied to =?Utf-8?B?TWF0dA==?= on 3 Jun 2004

Matt,

There's no way the query you used can return every report, query and table.
Specifying Type=5 filters it to return only query names. Check again!

SELECT [Name] FROM MSysObjects
WHERE [Type] = 5
ORDER BY [Name]

As for running the selected query, this code will do it:
Private Sub cboMyCombo_AfterUpdate()
If Not IsNull(Me!cboMyCombo) Then
CurrentDb.Execute Me!cboMyCombo.Value, dbFailOnError
End If
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

"Matt" <anonymous@discussions.microsoft.com> wrote in message
news:0FEECA9A-94C6-45F8-A1E5-D40C7468D935@microsoft.com...
queries appear in a dropdown box, and then run a command button/report from
the selection of the query. I tried the following code but it gives me
every report, query, table, etc etc in my dropdown box. Is there a way to
pick which queries I want in my dropdown? Thank you very much.

=?Utf-8?B?TWF0dA==?= replied to Graham R Seach on 3 Jun 2004

Thanks Graham, it came up with all the queries now. I was wondering if there is a way to be able to choose which queries I want in the drop down box instead of all of them. Thanks again.

-Matt

Graham R Seach replied to =?Utf-8?B?TWF0dA==?= on 4 Jun 2004

Matt,

SELECT [Name] FROM MSysObjects
WHERE [Type] = 5 AND Left([Name], 3) = "qry"
ORDER BY [Name]

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

"Matt" <anonymous@discussions.microsoft.com> wrote in message
news:41E6E67C-6E3D-4913-9CF3-77C43062A878@microsoft.com...
there is a way to be able to choose which queries I want in the drop down
box instead of all of them. Thanks again.
button/report from

Archived message: Queries in a Dropdown (Microsoft Access)