Can Combo or List box list all your queries?

  • Thread starter Thread starter Sky
  • Start date Start date
S

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
 
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.
Click to expand...

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?
 
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?
Click to expand...

What version of Access are you using?
 
Well, I pasted it in and saved the query in a file named
What version of Access are you using?
Click to expand...

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).
 
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).
Click to expand...

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
 
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
 
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?
 
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.
Click to expand...

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.
 
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
 
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
 
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!!!
Click to expand...

<chuckle>

Glad to help.

:-)
 
Bruce M. Thompson said:
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.
Click to expand...

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.
 
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.
Click to expand...

Thanks, Dirk!

:-)
 
Back
Top