Can Combo or List box list all your queries?

[Sky]

Hi All,

Is it possible to create a Combo or List box that allows
you to select queries from your query folder. What I
would like to do is have a List or Combo box on a form
that will let you run existing queries from a list.
I tried doing it, but only one query shows up in the list.

-Sky

 

[Bruce M. Thompson]

Is it possible to create a Combo or List box that allows

you to select queries from your query folder. What I
would like to do is have a List or Combo box on a form
that will let you run existing queries from a list.
I tried doing it, but only one query shows up in the list.

Generate a query using the following sql (copy and paste it into the SQL view of
a new query in the Query Builder):

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Type)=5) AND ((Left([name],1)="~")=False))
ORDER BY MsysObjects.Name;

Does that provide you with what you want?

 

[Bruce M. Thompson]

Well, I pasted it in and saved the query in a file named

Query List. Next, I created a blank form and dropped a
Combo box in it and selected the query with your code in
it, but I got an error message that says: "No valid
fields can be found in Query List". What now?

What version of Access are you using?

 

[Bruce M. Thompson]

Well, I pasted it in and saved the query in a file named

What version of Access are you using?

Actually, I guess that makes little difference. This works for me in Access
versions 2.0 through 2002, so there must be something else at work here. What
happens when you try to open the query in datasheet view? If you get the same
error message, try opening the query in design view and tell me what you see in
the query designer grid (also, check to make sure that the SQL that you pasted
hasn't changed).

 

[Bruce M. Thompson]

Well, I pasted it in and saved the query in a file named

Actually, I guess that makes little difference. This works for me in Access
versions 2.0 through 2002, so there must be something else at work here. What
happens when you try to open the query in datasheet view? If you get the same
error message, try opening the query in design view and tell me what you see in
the query designer grid (also, check to make sure that the SQL that you pasted
hasn't changed).

And see the following MS Knowledge Base article in case it describes what you
are experiencing:

ACC2002: Combo Box Wizard Unexpectedly Reports No Valid Fields
http://support.microsoft.com/default.aspx?scid=kb;en-us;295265

 

[Sky]

Bruce,

When I open the query in Design View, I can see all the
query files listed. What I want though is to be able to
click on any query in the list and have it actually
execute.

I don't know what to type in Expression Builder to make
the queries execute though.

-Sky

 

[Sky]

Bruce,

I started the Form Wizard and from the Tables/Queries tab
selected the Query that has your code in it. But in
the "Available Fields:" section of the Form Wizard, the
field called "Name" does not show up, it's just
completely blank.

Could this be the problem?

 

[Bruce M. Thompson]

I started the Form Wizard and from the Tables/Queries tab

selected the Query that has your code in it. But in
the "Available Fields:" section of the Form Wizard, the
field called "Name" does not show up, it's just
completely blank.

It is apparently the Wizard that is the problem. The article I referred you to
contains an explanation of the problem and a resolution. All you really need to
do is place your combo box on the form and, under the "Data" tab in the
properties sheet, set the "Row Source" property to the name of your query.

 

[Sky]

Hi Bruce,


OK, I followed your last instructions and added to code
to AfterUpdate section and saved the form with the
name "ThisForm" but I get this error when selecting items
from the Combo Box.

Compile Error:
Method or data member not found.

The code in the debugger window looks like this:

Private Sub Combo0_AfterUpdate()
DoCmd.OpenQuery Me.ThisForm.Value
End Sub

 

[Sky]

Bruce,


My bad, I put in the wrong name. I should have put
in "Combo0". Everything works perfectly now. Thanks dude
a million times over for all your help. BRUCE ROCKS!!!

-Sky

 

[Bruce M. Thompson]

My bad, I put in the wrong name. I should have put

in "Combo0". Everything works perfectly now. Thanks dude
a million times over for all your help. BRUCE ROCKS!!!

<chuckle>

Glad to help.

 

[Dirk Goldgar]

It is apparently the Wizard that is the problem. The article I
referred you to contains an explanation of the problem and a
resolution. All you really need to do is place your combo box on the
form and, under the "Data" tab in the properties sheet, set the "Row
Source" property to the name of your query.

Bruce -

I just ran into this in a different thread, and it appears the wizard
has a problem when the base table of the rowsource is not visible. If
the user has checked the option to View System Objects, the problem
doesn't occur. I agree that the simplest thing is just to set the Row
Source manually.

 

[Bruce M. Thompson]

I just ran into this in a different thread, and it appears the wizard
has a problem when the base table of the rowsource is not visible. If
the user has checked the option to View System Objects, the problem
doesn't occur. I agree that the simplest thing is just to set the Row
Source manually.

Thanks, Dirk!