Lookup currentdb queries in tbl

[GMan]

Hi All,

I want a combo box to list all the queries in the current db so the query
can be selected and opened using a button within a form.

I've created a Tbl for the Qry's to go in...
I've got a combo box on my form that reads the tbl list... that works

I cant link the tbl list to the db query list.

Ive read around the subject and it says something about Modules!!?

Any help would be much appreciated

 

[Jeff Boyce]

You've apparently decided on an approach ("combobox listing queries")...

Now, what business problem are you attempting to solve? (there may be more
than one potential solution!)

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[BruceM via AccessMonster.com]

The queries are already listed in a system table mSysObjects at least that's
what it's named in Access 2003). You can view System tables by clicking
Tools >> Options >> View, and check System Objects. You can open the tables
to see what is in them, but don't touch anything beyond that. Once you have
looked at them, hide them again.

You can create a query along the lines of:

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

I think there are other ways to get the same list, but that's probably the
simplest.

That being said, I wonder along with Jeff what you are trying to accomplish.
Is the idea to open the queries directly? Do you see that as preferable to
using forms or reports? If you have a similar combo box for tables, I need
to point out that tables should not be exposed directly to the users.
However, it seems you are saying the table list opens the tables. If so,
what is the code? What code are you trying to use to open the queries.

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ChooseReportFromList2.mdb" which illustrates how to do this.
You can download it for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=382. The
sample name says "Reports" but this one has the option to select queries as
well as reports for printing.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[GMan]

Cheers Fella's replies much appreciated.

I want my user to be able to create and preview their own queries, the combo
box will list the database queries, ive placed 2 buttons below, 1 for New
Query, the other Open Query after selecting from the combo box.

I'm gonna look at the sample from Roger and i'll post later on my results,
any other suggestions always welcome.

 

[GMan]

Hi Roger,

Ta for the sample DB. Its exactly what i want to do, but i want to do it on
one form. I'm gonna have a sort out with the code and try a few things.
I'll come back to you with either what ive done, or a thank you for your
stunning example and of course i can always post it to you. Many thanks.

 

[GMan]

Hi Jeff,

I'm creating a db bottom up for a catering solution (, supplier,
ingredients, recipe control, etc).

Ive created a seperate form which i can call in from the main page and i
want to have a combo box, which lists the actual queries in the db, it will
also allow me to create a new query that opens the design view.

In short, i want to recreate what access does, but on a front end form and
blocking the user from accessing the backend tables.

 

[Roger Carlson]

I just posted another sample called "ChooseQueryFromList.zip"
(http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=528), which
has everything except listing and opening queries stripped out. Perhaps
that will help.

 

[GMan]

Hi Roger,

Thanks so much for the help, very much appreciated. Ive got half way there,
the queries populate in the list box and the button opens it okay.

I tried the same with reports (i took it one stage further) using info from
your suggestions, but it doesnt work yet... (but it will )

? In the query properties Row Source SELECT... it specifies 'Type=5'
I'm assuming that tells the machine to look at the queries section, because
there's no mention of Qry or Rpts.
Ive tried changing the numbers from 0-15 / -32675 and get a series of
different results, so the right one must be there somewhere!?

Once again many thanks, i'll give it another bash tomorrow. I'll still post
my result once ive got it worked out for others.

 

[GMan]

I'VE DONE IT!! Yeah......

Many thanks Roger

I now have 1 form with 2 list boxes, 1 for dbQueries the other for db
Reports, they operate by selecting the Qry/Rpt from the list and click a
corresponding button to produce the result.

If anyone wants help with this, then please let me know and i can send you
the form.

Queries:

SELECT DISTINCTROW msysobjects.Name, msysobjects.Type, * FROM msysobjects
WHERE (((msysobjects.Name) Not Like "~*") AND ((msysobjects.Type)=5));

Command Button: DoCmd.OpenQuery stDocName, acViewNormal

Reports:

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

Command Button: DoCmd.OpenReport stDocName, acViewPreview