Open Query Action - Browse for query name in current DB

[John via AccessMonster.com]

Hi, I am using the Open Query Action in a macro and would like to browse for
the query name (to select the query to open) within the same DB. Any
suggestions?

 

[Allen Browne]

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name

 

[Duane Hookom]

Create a form with a list box (or combo box) that displays all queries. The
row source of the control might be something like:

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

I would much prefer to keep a table of query names that I would want to
expose since queries might be update or delete or make table. Also, my query
names are not generally for "public consumption".

 

[John via AccessMonster.com]

Allen, I created the form, but I am not sure how to relate the from to my
macro. Maybe I should have explained that I wrote a small Macro to open up
the query (first action "Open Query"), then it uses the "Output To" action to
send the query in .XLS format to a folder in MyDocuments.

The last macro action closes the query.

I understand that I can create a form to open queries, but I wanted to use
these commands within a macro to open and export the file. However, I may
have several different queries and wanted the ability to browse within the
"Opn Query"action to allow me to select the query I want to export (rathen
than having to hard code it in the macro every time I want to export a
different query result.

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name

Hi, I am using the Open Query Action in a macro and would like to browse
for
the query name (to select the query to open) within the same DB. Any
suggestions?

 

[John via AccessMonster.com]

Duane, I created the form, but I am not sure how to relate the from to my
macro. Maybe I should have explained that I wrote a small Macro to open up
the query (first action "Open Query"), then it uses the "Output To" action to
send the query in .XLS format to a folder in MyDocuments.

The last macro action closes the query.

I understand that I can create a form to open queries, but I wanted to use
these commands within a macro to open and export the file. However, I may
have several different queries and wanted the ability to browse within the
"Opn Query"action to allow me to select the query I want to export (rathen
than having to hard code it in the macro every time I want to export a
different query result.

 

[Allen Browne]

In your macro, under OutputTo, set the Object Name to:
[Forms].[Form1].[lstQuery]
replacing Form1 with the name of your form.

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

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

Allen, I created the form, but I am not sure how to relate the from to my
macro. Maybe I should have explained that I wrote a small Macro to open
up
the query (first action "Open Query"), then it uses the "Output To" action
to
send the query in .XLS format to a folder in MyDocuments.

The last macro action closes the query.

I understand that I can create a form to open queries, but I wanted to use
these commands within a macro to open and export the file. However, I may
have several different queries and wanted the ability to browse within the
"Opn Query"action to allow me to select the query I want to export (rathen
than having to hard code it in the macro every time I want to export a
different query result.

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name

Hi, I am using the Open Query Action in a macro and would like to browse
for
the query name (to select the query to open) within the same DB. Any
suggestions?

 

[John via AccessMonster.com]

Allen,

I apologize for my ineptness. However, when I follow both sets of your
responses and run the query, what I wind up getting is a window popping up
prompting me to name the file being saved in the destination folder. I am
still looking for the macro to prompt me to select the query (from a list or
browse) to run and export before prompting me to rename the file/query being
exported.

i.e.,

1. Click on query and be prompted to select the query (from a list of
queries in my DB) to run and export.
2. Prompts me to rename the file/query in .XLS format after the query is
selected to open/run and be exported.

I tried the methodology twice and got the same result as in the first
paragraphy above.

In your macro, under OutputTo, set the Object Name to:
[Forms].[Form1].[lstQuery]
replacing Form1 with the name of your form.

Allen, I created the form, but I am not sure how to relate the from to my
macro. Maybe I should have explained that I wrote a small Macro to open

[quoted text clipped - 48 lines]

 

[Duane Hookom]

I defer to Allen. If you want a complete solution to your requirements, you
might want to review the Query By Form applet at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
solution allows you to present users with a list of "master" queries that
you have defined. They can select fields, set sorting & criteria, and send
results to HTML, Excel, CSV,...

 

[Allen Browne]

Ignore the code suggested earlier. It now turns out that you do not want to
OpenQuery, but ratther to OutputTo a file.

You will be prompted for a file name if you do not type one into the macro
under the OutputTo action.

You will be prompted for the query name if the form is not open at the time,
and has a query selected. You will also be prompted if the reference is
incorrect (e.g. if your form is not named Form1 without a space, or your
list box is not named lstQuery.)

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

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

Allen,

I apologize for my ineptness. However, when I follow both sets of your
responses and run the query, what I wind up getting is a window popping up
prompting me to name the file being saved in the destination folder. I am
still looking for the macro to prompt me to select the query (from a list
or
browse) to run and export before prompting me to rename the file/query
being
exported.

i.e.,

1. Click on query and be prompted to select the query (from a list of
queries in my DB) to run and export.
2. Prompts me to rename the file/query in .XLS format after the query is
selected to open/run and be exported.

I tried the methodology twice and got the same result as in the first
paragraphy above.

In your macro, under OutputTo, set the Object Name to:
[Forms].[Form1].[lstQuery]
replacing Form1 with the name of your form.

Allen, I created the form, but I am not sure how to relate the from to
my
macro. Maybe I should have explained that I wrote a small Macro to open

[quoted text clipped - 48 lines]

the query name (to select the query to open) within the same DB. Any
suggestions?

 

[John via AccessMonster.com]

Allen, was I to type in this code in the "Row Source" of my list box (named
1stQuery)? That is what I did.

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

Then typed this as the object name in the Open Query action within the Macro.

[Forms].[Form1].1stQuery


as I said before, I want to browse for the query I wish to select to
output/export.

I have left the output file field blank so I can rename the query/exported
file as appropriate at the time.
The Form is named Form1, and the list box is named 1stQuery (no spaces).

Ignore the code suggested earlier. It now turns out that you do not want to
OpenQuery, but ratther to OutputTo a file.

You will be prompted for a file name if you do not type one into the macro
under the OutputTo action.

You will be prompted for the query name if the form is not open at the time,
and has a query selected. You will also be prompted if the reference is
incorrect (e.g. if your form is not named Form1 without a space, or your
list box is not named lstQuery.)

Allen,

[quoted text clipped - 27 lines]

 

[Allen Browne]

Answers In line

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

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

Allen, was I to type in this code in the "Row Source" of my list box
(named
1stQuery)? That is what I did.

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

Yes. That goes into the RowSource property of the list box. When you open
the form, you should then see your queries named in the list box. If you
don't see them, you cannot proceed. If you do see them, click on the one you
want to export.

Then typed this as the object name in the Open Query action within the
Macro.

[Forms].[Form1].1stQuery

That goes in the lower pane of the macro, beside the object name.
I am assuming that you choose Query as the Object Type (on the line above in
the lower pane of macro design.)

Be sure to put square brackets around the 1stQuery part as well. (The
suggestion was to use LST as the prefix for Listbox, but the 1 should still
work if you are consistent and add the square brackets.)

 

[Guest]

That was cool!
Can you suggest a similar procedure for displaying all tables in a database?
I have a combo box from which I want to see all my tables.
--
Glint

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name

 

[Duane Hookom]

Glint,
Take Allen's Row Source property and open it in a query design window. You
should be able to figure out how to modify the criteria to display table
names.
--
Duane Hookom
MS Access MVP
--

That was cool!
Can you suggest a similar procedure for displaying all tables in a
database?
I have a combo box from which I want to see all my tables.
--
Glint

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name

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

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

Hi, I am using the Open Query Action in a macro and would like to
browse
for
the query name (to select the query to open) within the same DB. Any
suggestions?

 

[Allen Browne]

Tables:
=====
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 for linked tables, and type 4 for ODBC linked tables.

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

Forms:
=====
SELECT [Name] FROM MsysObjects
WHERE (([Type] = -32768) AND ([Name] Not Like '~*'))
ORDER BY MsysObjects.Name;

Reports:
======
SELECT [Name] FROM MsysObjects
WHERE ([Name] Not Like "~*") AND ([Type] = -32764))
ORDER BY [Name];

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

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

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

That was cool!
Can you suggest a similar procedure for displaying all tables in a
database?
I have a combo box from which I want to see all my tables.
--
Glint

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name

 

[Guest]

Thanx Allen, that was wonderful. I appreciate it.
--
Glint

Tables:
=====
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Include type 6 for linked tables, and type 4 for ODBC linked tables.

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

Forms:
=====
SELECT [Name] FROM MsysObjects
WHERE (([Type] = -32768) AND ([Name] Not Like '~*'))
ORDER BY MsysObjects.Name;

Reports:
======
SELECT [Name] FROM MsysObjects
WHERE ([Name] Not Like "~*") AND ([Type] = -32764))
ORDER BY [Name];

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

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

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

That was cool!
Can you suggest a similar procedure for displaying all tables in a
database?
I have a combo box from which I want to see all my tables.
--
Glint

1. Create a simple little unbound form

2. Add a list box, and set its Name to lstQuery
Set its Row Source property to:
SELECT MSysObjects.Name FROM MsysObjects
WHERE ((MSysObjects.Type = 5) AND ([Name] Not Like "~*"))
ORDER BY MSysObjects.Name;

3. Add a command button with these properties:
Name cmdOk
Default Yes
On Click [Event Procedure]

4. Click the Build button (...) beside the On Click property.
Access opens the code window.
Set up the code so it looks like this:
Private Sub cmdOk_Click()
If IsNull(Me.lstQuery) Then
Beep
Else
DoCmd.OpenQuery Me.lstQuery
End If
End Sub

5. Add another command button with these properties:
Name cmdCancel
Cancel Yes
On Click [Event Procedure]

6. Add this line to the Click event procedure:
DoCmd.Close acForm, Me.Name