Queries as source for combo box

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.

Here's the deal: I'm in a group that produces tons of reports for
various people. Much of the work is done manually. For instance, I
was given an adhoc report request this morning. This one report
involves running eight separate queries with slightly different
criteria. Then exporting them to Excel. Then extracting a random
sampling of each recordset (different size and spec for each sample).
Finally formatting the resulting 8 page report for delivery to the
requester.

What I want to do is build my own desktop solution to make my job a
little easier. I've already got a randomizer function I built long
ago in Access so I'd like to do everything but the final formatting in
Access. I'd like to just pick a query from a combo box, enter some
criteria in some other text boxes and hit a button to spit out the
Excel files.

So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
 
Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.
Click to expand...
So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
Click to expand...

Ah geez. Right after I post that I find something. Unfortunately,
it's in ADO and I prefer DAO. I hope I can conver it.
 
You'll want to snoop around the MSysObject table. Its the system table
that Access uses to manage the objects in a *.mdb file. Its been awhile
but it is possible to run a query against the table to find the names of
the queries in a database, however it will give you ALL of the queries.
You'll also need to snoop around the Internet to determine the value
Acces uses to differeniate the various objects. As to the second part,
if you're not familar with DAO and running queries via code - look into
it. (You'll also want to snoop around the MSysQueries since that table
will provide you with information that you'll need to provide DAO with
the neccessary parameters to run the query.)

David H
 
Thanks, David.

Sandra gave me some SQL to put in the rowsource that worked like a
charm.

I had already determined that the queries I *didn't* want were
prefaced with a "~" which make them easy to filter out.

Also, I'm very familiar with running sql in code. If you're
interested, I've attached a txt file with some code I was working on a
couple of years ago. This was still in development when I saved this
bit and it's a hodge-podge from different modules (in no particular
order), but it shows things like Unions, various joins and the
creation and deletion of querydefs.

Cheers,
RD
 
HAR! And then I forget to attach. Ooops!


Thanks, David.

Sandra gave me some SQL to put in the rowsource that worked like a
charm.

I had already determined that the queries I *didn't* want were
prefaced with a "~" which make them easy to filter out.

Also, I'm very familiar with running sql in code. If you're
interested, I've attached a txt file with some code I was working on a
couple of years ago. This was still in development when I saved this
bit and it's a hodge-podge from different modules (in no particular
order), but it shows things like Unions, various joins and the
creation and deletion of querydefs.

Cheers,
RD
Click to expand...
 
Sandra gave me some SQL to put in the rowsource that worked like a
charm.
Click to expand...

Just bear in mind that the structure of the system tables is officially
undocumented, and therefore MS is under no obligation to keep them the
layout consistent between versions of Access.

That said, there is good evidence that they are likely to keep them the
same (sorry, can't find the URL just now): but you cannot complain if the
application goes dead in Access 2006...

All the best


Tim F
 
Good point Tim. To be honest, I normally try to avoid doing this since it is
somewhat of a bad or perhaps risky practice. I considered replying with a
Callback function that gets its data from the Querydefs collection but that
seems like overkill when the MSysObjects table is so handy! Regardless, I
should have pointed out the appropriate caveats of this techique.
 
Good point Tim. To be honest, I normally try to avoid doing this since
it is somewhat of a bad or perhaps risky practice. I considered
replying with a Callback function that gets its data from the
Querydefs collection but that seems like overkill when the MSysObjects
table is so handy! Regardless, I should have pointed out the
appropriate caveats of this techique.
Click to expand...

For my money, the absence of any kind of access to the Querydefs
collection is a huge weakness of the ADO libraries. Querydefs are surely
part of the data model, just as Views are in proper DBMSs. I think it
underlines the fact that the most appropriate method of manipulating Jet
data is still DAO.

All the best


Tim F
 
Just bear in mind that the structure of the system tables is officially
undocumented, and therefore MS is under no obligation to keep them the
layout consistent between versions of Access.

That said, there is good evidence that they are likely to keep them the
same (sorry, can't find the URL just now): but you cannot complain if the
application goes dead in Access 2006...

All the best


Tim F
Click to expand...

I'm not too worried on that score. The app I built will be obsolete
in about six months when we move everything over to a new system. I
may still build myself some quickie solutions in Access, but the bulk
of my reporting work will move into BusinessObjects (yuck). I'm
hoping they move me over to more web-app work.


Cheers,
RD
 
For my money, the absence of any kind of access to the Querydefs
collection is a huge weakness of the ADO libraries. Querydefs are surely
part of the data model, just as Views are in proper DBMSs. I think it
underlines the fact that the most appropriate method of manipulating Jet
data is still DAO.

All the best


Tim F
Click to expand...

Which is why I still do most of my coding in DAO.

RD
 
Hi--

I'd like to refine the rowsource even futher. Is there a way to exclude
"action" queries from the list? I'd prefer my users not even see those.

--mikej711

Sandra Daigle said:
http://www.mvps.org/access/queries/qry0002.htm

Here is the SQL for the rowsource of your combo:

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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.

Here's the deal: I'm in a group that produces tons of reports for
various people. Much of the work is done manually. For instance, I
was given an adhoc report request this morning. This one report
involves running eight separate queries with slightly different
criteria. Then exporting them to Excel. Then extracting a random
sampling of each recordset (different size and spec for each sample).
Finally formatting the resulting 8 page report for delivery to the
requester.

What I want to do is build my own desktop solution to make my job a
little easier. I've already got a randomizer function I built long
ago in Access so I'd like to do everything but the final formatting in
Access. I'd like to just pick a query from a combo box, enter some
criteria in some other text boxes and hit a button to spit out the
Excel files.

So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
Click to expand...
Click to expand...
 
Mike,

Try this:

SELECT MSysObjects.Name FROM MSysObjects WHERE
Left([Name],1)<>"~" AND Type=5 AND Flags Not In (32, 48, 64,88)
ORDER BY MSysObjects.Name;

HTH,
Nikos
Hi--

I'd like to refine the rowsource even futher. Is there a way to exclude
"action" queries from the list? I'd prefer my users not even see those.

--mikej711

:

http://www.mvps.org/access/queries/qry0002.htm

Here is the SQL for the rowsource of your combo:

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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.

Here's the deal: I'm in a group that produces tons of reports for
various people. Much of the work is done manually. For instance, I
was given an adhoc report request this morning. This one report
involves running eight separate queries with slightly different
criteria. Then exporting them to Excel. Then extracting a random
sampling of each recordset (different size and spec for each sample).
Finally formatting the resulting 8 page report for delivery to the
requester.

What I want to do is build my own desktop solution to make my job a
little easier. I've already got a randomizer function I built long
ago in Access so I'd like to do everything but the final formatting in
Access. I'd like to just pick a query from a combo box, enter some
criteria in some other text boxes and hit a button to spit out the
Excel files.

So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
Click to expand...
Click to expand...
Click to expand...
 
That worked, Nikos. Thanks.

I wonder, is there a "flags" legend somewhere detailing what the flags are?

Nikos Yannacopoulos said:
Mike,

Try this:

SELECT MSysObjects.Name FROM MSysObjects WHERE
Left([Name],1)<>"~" AND Type=5 AND Flags Not In (32, 48, 64,88)
ORDER BY MSysObjects.Name;

HTH,
Nikos
Hi--

I'd like to refine the rowsource even futher. Is there a way to exclude
"action" queries from the list? I'd prefer my users not even see those.

--mikej711

:

http://www.mvps.org/access/queries/qry0002.htm

Here is the SQL for the rowsource of your combo:

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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


RD wrote:

Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.

Here's the deal: I'm in a group that produces tons of reports for
various people. Much of the work is done manually. For instance, I
was given an adhoc report request this morning. This one report
involves running eight separate queries with slightly different
criteria. Then exporting them to Excel. Then extracting a random
sampling of each recordset (different size and spec for each sample).
Finally formatting the resulting 8 page report for delivery to the
requester.

What I want to do is build my own desktop solution to make my job a
little easier. I've already got a randomizer function I built long
ago in Access so I'd like to do everything but the final formatting in
Access. I'd like to just pick a query from a combo box, enter some
criteria in some other text boxes and hit a button to spit out the
Excel files.

So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
Click to expand...
Click to expand...
Click to expand...
 
Mike,

To the best of my knowledge, flags are not documented by Microsoft - at
least, I have not been able to find anything official. I just "dug up"
this by looking at the table.

Glad it did the job.Regards,
Nikos
That worked, Nikos. Thanks.

I wonder, is there a "flags" legend somewhere detailing what the flags are?

:

Mike,

Try this:

SELECT MSysObjects.Name FROM MSysObjects WHERE
Left([Name],1)<>"~" AND Type=5 AND Flags Not In (32, 48, 64,88)
ORDER BY MSysObjects.Name;

HTH,
Nikos
Hi--

I'd like to refine the rowsource even futher. Is there a way to exclude
"action" queries from the list? I'd prefer my users not even see those.

--mikej711

:



http://www.mvps.org/access/queries/qry0002.htm

Here is the SQL for the rowsource of your combo:

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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


RD wrote:


Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.

Here's the deal: I'm in a group that produces tons of reports for
various people. Much of the work is done manually. For instance, I
was given an adhoc report request this morning. This one report
involves running eight separate queries with slightly different
criteria. Then exporting them to Excel. Then extracting a random
sampling of each recordset (different size and spec for each sample).
Finally formatting the resulting 8 page report for delivery to the
requester.

What I want to do is build my own desktop solution to make my job a
little easier. I've already got a randomizer function I built long
ago in Access so I'd like to do everything but the final formatting in
Access. I'd like to just pick a query from a combo box, enter some
criteria in some other text boxes and hit a button to spit out the
Excel files.

So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
Click to expand...
Click to expand...
Click to expand...
 
Thanks anyway, Nikos. Let me know if there's anything I can do for you.

Nikos Yannacopoulos said:
Mike,

To the best of my knowledge, flags are not documented by Microsoft - at
least, I have not been able to find anything official. I just "dug up"
this by looking at the table.

Glad it did the job.Regards,
Nikos
That worked, Nikos. Thanks.

I wonder, is there a "flags" legend somewhere detailing what the flags are?

:

Mike,

Try this:

SELECT MSysObjects.Name FROM MSysObjects WHERE
Left([Name],1)<>"~" AND Type=5 AND Flags Not In (32, 48, 64,88)
ORDER BY MSysObjects.Name;

HTH,
Nikos

mikej711 wrote:

Hi--

I'd like to refine the rowsource even futher. Is there a way to exclude
"action" queries from the list? I'd prefer my users not even see those.

--mikej711

:



http://www.mvps.org/access/queries/qry0002.htm

Here is the SQL for the rowsource of your combo:

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

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


RD wrote:


Hi. I have a new one.

I'd like to list all the queries in a db in a combo box (or list box,
whichever) so I can choose one and then manipulate it
programmatically.

Here's the deal: I'm in a group that produces tons of reports for
various people. Much of the work is done manually. For instance, I
was given an adhoc report request this morning. This one report
involves running eight separate queries with slightly different
criteria. Then exporting them to Excel. Then extracting a random
sampling of each recordset (different size and spec for each sample).
Finally formatting the resulting 8 page report for delivery to the
requester.

What I want to do is build my own desktop solution to make my job a
little easier. I've already got a randomizer function I built long
ago in Access so I'd like to do everything but the final formatting in
Access. I'd like to just pick a query from a combo box, enter some
criteria in some other text boxes and hit a button to spit out the
Excel files.

So (sorry about all the wind), how does one populate a combo box with
the queries in the db? I saw it done once (for tables) in a friend's
db years ago but can't remember how he did it. Nor have I been able
to find anything on the web.

Thanks for any help,
RD
Click to expand...
Click to expand...
Click to expand...
 
Back
Top