Drop down box of saved queries?

[nkakouros]

Dear all,

Please forgive me if this is an obvious query (pun intended), but I'm
an access newbie and I've googled extensively with no success.

My query is:

I have a Microsoft Access Database.
I have created a number of saved queries.

Is it possible to place on a form a drop down combo box (or similar)
that dynamically lists the available saved queries (ie so that if more
saved queries are created, these will appear as well) and make it
possible to select one of these queries and run it?

I've just about managed to show names of Queries, but now stuck...

Dim db As Database, qdf As QueryDef
Set db = CurrentDb
For Each qdf In db.QueryDefs
If Not InStr(qdf.Name, "~sq") > 0 Then ' have no idea why things with
~sq come up, but this filters them!
MsgBox qdf.Name ' seems to list query names on screen.. now what?
End If
Next
Set db = Nothing

So.. how can I get the Query names into a combo box and make it
possible to run a query when selected from this box?

Thanks alot.

nick

 

[Allen Browne]

Try this in the RowSource of your combo:

SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

 

[Nikos]

Brilliant.
Thank you very much Allen.

I then added this to actually start the query:
If Me!cmbQuery <> "" Then
DoCmd.OpenQuery Me!cmbQuery
Else
MsgBox "Please select saved Query to run", vbOKOnly, "Select Query"
GoTo Exit_Cmdgoquery_Click
End If

Seems to work great!

Thanks again

Nick

Try this in the RowSource of your combo:

SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Dear all,

Please forgive me if this is an obvious query (pun intended), but I'm
an access newbie and I've googled extensively with no success.

My query is:

I have a Microsoft Access Database.
I have created a number of saved queries.

Is it possible to place on a form a drop down combo box (or similar)
that dynamically lists the available saved queries (ie so that if more
saved queries are created, these will appear as well) and make it
possible to select one of these queries and run it?

I've just about managed to show names of Queries, but now stuck...

Dim db As Database, qdf As QueryDef
Set db = CurrentDb
For Each qdf In db.QueryDefs
If Not InStr(qdf.Name, "~sq") > 0 Then ' have no idea why things with
~sq come up, but this filters them!
MsgBox qdf.Name ' seems to list query names on screen.. now what?
End If
Next
Set db = Nothing

So.. how can I get the Query names into a combo box and make it
possible to run a query when selected from this box?

Thanks alot.

nick- Hide quoted text -- Show quoted text -

 

[Nikos]

Silly me...
LOL
Just added cmbQuery.ItemData(0) to the combo's default value.. saves
the if.. check ;-)

;-)

Thanks

Brilliant.
Thank you very much Allen.

I then added this to actually start the query:
If Me!cmbQuery <> "" Then
DoCmd.OpenQuery Me!cmbQuery
Else
MsgBox "Please select saved Query to run", vbOKOnly, "Select Query"
GoTo Exit_Cmdgoquery_Click
End If

Seems to work great!

Thanks again

Nick

Try this in the RowSource of your combo:

SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Dear all,

Please forgive me if this is an obvious query (pun intended), but I'm
an access newbie and I've googled extensively with no success.

My query is:

I have a Microsoft Access Database.
I have created a number of saved queries.

Is it possible to place on a form a drop down combo box (or similar)
that dynamically lists the available saved queries (ie so that if more
saved queries are created, these will appear as well) and make it
possible to select one of these queries and run it?

I've just about managed to show names of Queries, but now stuck...

Dim db As Database, qdf As QueryDef
Set db = CurrentDb
For Each qdf In db.QueryDefs
If Not InStr(qdf.Name, "~sq") > 0 Then ' have no idea why things with
~sq come up, but this filters them!
MsgBox qdf.Name ' seems to list query names on screen.. now what?
End If
Next
Set db = Nothing

So.. how can I get the Query names into a combo box and make it
possible to run a query when selected from this box?

Thanks alot.

nick- Hide quoted text -- Show quoted text -

 

[Allen Browne]

Okay, you have it working.

The previous suggestion would not have been enough:
needed IsNull() instaed of <> "".

There's quite a difference between Null and a zero-length string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Silly me...
LOL
Just added cmbQuery.ItemData(0) to the combo's default value.. saves
the if.. check ;-)

;-)

Thanks

Brilliant.
Thank you very much Allen.

I then added this to actually start the query:
If Me!cmbQuery <> "" Then
DoCmd.OpenQuery Me!cmbQuery
Else
MsgBox "Please select saved Query to run", vbOKOnly, "Select Query"
GoTo Exit_Cmdgoquery_Click
End If

Seems to work great!

Thanks again

Nick

Try this in the RowSource of your combo:

SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message


Dear all,

Please forgive me if this is an obvious query (pun intended), but I'm
an access newbie and I've googled extensively with no success.

My query is:

I have a Microsoft Access Database.
I have created a number of saved queries.

Is it possible to place on a form a drop down combo box (or similar)
that dynamically lists the available saved queries (ie so that if
more
saved queries are created, these will appear as well) and make it
possible to select one of these queries and run it?

I've just about managed to show names of Queries, but now stuck...

Dim db As Database, qdf As QueryDef
Set db = CurrentDb
For Each qdf In db.QueryDefs
If Not InStr(qdf.Name, "~sq") > 0 Then ' have no idea why things
with
~sq come up, but this filters them!
MsgBox qdf.Name ' seems to list query names on screen.. now what?
End If
Next
Set db = Nothing

So.. how can I get the Query names into a combo box and make it
possible to run a query when selected from this box?

Thanks alot.

nick- Hide quoted text -- Show quoted text -