Show Query Design Grid via VBA

[Guest]

Hello.

I would like to use VBA to show the Query Design Grid, then after using the
QDG to select Tables, Fields, Criteria, etc., exit the QDG and have the
resulting SQL placed in a TextBox.

Is this possible?

-Michael

 

[Steve]

How about the following on a form:
1. a multiselect listbox for selecting the tables
2. an unbound subform with three columns. Column one would list all the
fields in the selected table(s). Column 2 would be a checkbox to select the
fields to include in the SQL. Column 3 would be where you enter the criteria
for the selected fields.
3. A textbox to hold the SQL string.
4 Code in the click event of a button to create the SQL string in the
textbox from the selected tables, selected fields and the entered criteria.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Thanks for the reply and the suggestion. I've actually got something similar
already in place and functional, but it is a bit clumsy. That's why I was
hoping to be able to use the QDG Interface instead.

-Michael

 

[fredg]

Hello.

I would like to use VBA to show the Query Design Grid, then after using the
QDG to select Tables, Fields, Criteria, etc., exit the QDG and have the
resulting SQL placed in a TextBox.

Is this possible?

-Michael

You wish to show the actual SQL as text in the control?

I think you need to separate this into 2 steps, using 2 different
command buttons.
Button 1:

DoCmd.OpenQuery "QueryName", acViewDesign

This opens the query in design view. Make whatever changes or
additions you wish.
Manually close and save the query.

Button 2:
Code the click event of this button:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
Me![ControlName] = qdf.SQL
Set qdf = Nothing

 

[Guest]

Hi Fred.

Thanks for your reply. There are no stored Queries involved. I'm just
trying to generate some SQL that will be used elsewhere in my VBA code.
Here's how I envision it:

If the TextBox is blank, the user clicks a command button next to it. The
Query Design Grid opens, and is completely blank (like when creating a new
Query). The user selects Tables, Fields, etc. and closes the QDG. The
TextBox is then populated with the resulting SQL.

If the TextBox contains SQL, the user clicks the command button, and the QDG
is populated with the SQL from the TextBox. The user makes changes, exits
the QDG, and the TextBox is populated with the changed SQL.

Is it just not possible for VBA to interface with the Query Design Grid? I
forgot to mention previously that I'm using Access 2003 (if that matters).

If not, perhaps I can create a Temp Query , then use your code to open it
and populate it with SQL. I'd make changes, save it, and then output the SQL
to a TextBox. I'll give this a try, however, if there is any way to
interface directly with the QDG, I'd love to hear about it.

-Michael

Hello.

I would like to use VBA to show the Query Design Grid, then after using the
QDG to select Tables, Fields, Criteria, etc., exit the QDG and have the
resulting SQL placed in a TextBox.

Is this possible?

-Michael

You wish to show the actual SQL as text in the control?

I think you need to separate this into 2 steps, using 2 different
command buttons.
Button 1:

DoCmd.OpenQuery "QueryName", acViewDesign

This opens the query in design view. Make whatever changes or
additions you wish.
Manually close and save the query.

Button 2:
Code the click event of this button:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
Me![ControlName] = qdf.SQL
Set qdf = Nothing

 

[Guest]

Hi Fred.

With one small exception, I've got this working. Here is the code behind
the CommandButton:

Private Sub cmdTextSQL_Click()
On Error Resume Next
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryTextSQL")
qdf.SQL = Me!rsTextSQL
DoCmd.OpenQuery "qryTextSQL", acViewDesign
Me!rsTextSQL = Replace(qdf.SQL, vbCrLf, " ")
Set qdf = Nothing
End Sub

The one problem is that all the code runs as soon as I click the button. I
need it instead to pause after the "DoCmd" line, wait for the Query to be
saved and closed, then resume execution with the "Me!rsTextSQL =" line. Can
this be done? Or is that why you suggested using two different buttons?

-Michael

Hello.

I would like to use VBA to show the Query Design Grid, then after using the
QDG to select Tables, Fields, Criteria, etc., exit the QDG and have the
resulting SQL placed in a TextBox.

Is this possible?

-Michael

You wish to show the actual SQL as text in the control?

I think you need to separate this into 2 steps, using 2 different
command buttons.
Button 1:

DoCmd.OpenQuery "QueryName", acViewDesign

This opens the query in design view. Make whatever changes or
additions you wish.
Manually close and save the query.

Button 2:
Code the click event of this button:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
Me![ControlName] = qdf.SQL
Set qdf = Nothing

 

[fredg]

Hi Fred.

With one small exception, I've got this working. Here is the code behind
the CommandButton:

Private Sub cmdTextSQL_Click()
On Error Resume Next
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryTextSQL")
qdf.SQL = Me!rsTextSQL
DoCmd.OpenQuery "qryTextSQL", acViewDesign
Me!rsTextSQL = Replace(qdf.SQL, vbCrLf, " ")
Set qdf = Nothing
End Sub

The one problem is that all the code runs as soon as I click the button. I
need it instead to pause after the "DoCmd" line, wait for the Query to be
saved and closed, then resume execution with the "Me!rsTextSQL =" line. Can
this be done? Or is that why you suggested using two different buttons?

-Michael

Hello.

I would like to use VBA to show the Query Design Grid, then after using the
QDG to select Tables, Fields, Criteria, etc., exit the QDG and have the
resulting SQL placed in a TextBox.

Is this possible?

-Michael

You wish to show the actual SQL as text in the control?

I think you need to separate this into 2 steps, using 2 different
command buttons.
Button 1:

DoCmd.OpenQuery "QueryName", acViewDesign

This opens the query in design view. Make whatever changes or
additions you wish.
Manually close and save the query.

Button 2:
Code the click event of this button:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
Me![ControlName] = qdf.SQL
Set qdf = Nothing

Regarding: >Or is that why you suggested using two different buttons?<
Yup!
Also, I don't see where you are saving the changed query.
DoCmd.Close acQuery, "qryTextSQL", acSaveYes

 

[Guest]

Hi Fred.

I'm not closing/saving the query in code, but rather manually as you
suggested in your first post.

I got this to work with one button by placing code in the Form_Activate
event which would otherwise have been run by clicking the second button. The
Form_Activate code does not run unless a boolean variable is set to True
(which happens in the event for the single button).

Except for the fact that the QDG window is not Modal, this is working
satisfactorily now. Thanks for your help.

-Michael

Hi Fred.

With one small exception, I've got this working. Here is the code behind
the CommandButton:

Private Sub cmdTextSQL_Click()
On Error Resume Next
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryTextSQL")
qdf.SQL = Me!rsTextSQL
DoCmd.OpenQuery "qryTextSQL", acViewDesign
Me!rsTextSQL = Replace(qdf.SQL, vbCrLf, " ")
Set qdf = Nothing
End Sub

The one problem is that all the code runs as soon as I click the button. I
need it instead to pause after the "DoCmd" line, wait for the Query to be
saved and closed, then resume execution with the "Me!rsTextSQL =" line. Can
this be done? Or is that why you suggested using two different buttons?

-Michael

On Mon, 7 May 2007 11:15:01 -0700, Michael H wrote:

Hello.

I would like to use VBA to show the Query Design Grid, then after using the
QDG to select Tables, Fields, Criteria, etc., exit the QDG and have the
resulting SQL placed in a TextBox.

Is this possible?

-Michael

You wish to show the actual SQL as text in the control?

I think you need to separate this into 2 steps, using 2 different
command buttons.
Button 1:

DoCmd.OpenQuery "QueryName", acViewDesign

This opens the query in design view. Make whatever changes or
additions you wish.
Manually close and save the query.

Button 2:
Code the click event of this button:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
Me![ControlName] = qdf.SQL
Set qdf = Nothing

Regarding: >Or is that why you suggested using two different buttons?<
Yup!
Also, I don't see where you are saving the changed query.
DoCmd.Close acQuery, "qryTextSQL", acSaveYes