Run and Display Dynamic Query Results

[Jean9]

I know this has got be so easy that even the question is going to make me look dumb but what code can one use on dynamically created sql (sSQL dimmed as string) to run a query with that sql and display the results in grid format (read only) to the user?

-User enters criteria
-SQL is created
-Want to run SQL and display results to user

Thanks in advance,

 

[PHV]

Why not set dynamically the RecordSource of a Form in DataSheet view ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya Jean9 . . .

What you want can be done with [blue]QueryDefs[/blue]. Wether you use [blue]PHV's[/blue] suggestion or [blue]QueryDefs[/blue] ... depends on wether or not the [blue]base SQL[/blue] will vary as far as [blue]tables[/blue] are concerned ... [blue]not the criteria[/blue].

If the base SQL is static (were not talking criteria here) then [blue]PHV[/blue] has you in tow. Otherwise you need to move to [blue]QueryDefs[/blue].

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

You can display a saved query by setting the SourceObject of a subform control like:

   Me.SubForm.SourceObject = "query.myquery"

Duane
Hook'D on Access
MS Access MVP

 

[Jean9]

I'd like to use querydefs please. The sql is going to be different every time the user makes a different selection on a form and every sql executed is going to have criteria associated with it. I guess a form would still work but wouldn't I then have to mess with the number of columns on this said datasheet form each time because the number of columns is going to change per user selection, too? I'd prefer to stick with querydefs...

Even though I've worked with querydefs before, I haven't used them in a manner in which their results are displayed to the user. I'd appreciate any coding help that can be provided for that.

Thanks,
J9

 

[dhookom]

Assuming you have a saved query named "myquery" and have code that builds a strSQL of the SQL statement. To get the SQL into myquery:

   CurrentDb.QueryDefs("myquery").SQL = strSQL

Assuming you have a form that runs the above code, add a blank subform named "MySubForm" to the form. Then use code like I suggested above:

      Me.MySubForm.SourceObject = "query.myquery"

It doesn't make any difference how many columns are returned. It just works.

Duane
Hook'D on Access
MS Access MVP

 

[Jean9]

Thanks guys for all your help. I kind of went with a hybrid of what was suggested. I created a query that contained no objects and saved it. From the form I execute the following script where the BuildQuery() function creates the sql string:

Private Sub CmdRUN_Click()
On Error GoTo Err_CmdRUN_Click
    
    sSQL = BuildQuery()
    CurrentDb.QueryDefs("QryTemp").SQL = sSQL
    DoCmd.OpenQuery "QryTemp", acViewNormal, acReadOnly
    
Exit_CmdRUN_Click:
    Exit Sub

Err_CmdRUN_Click:
    MsgBox Err.Description
    Resume Exit_CmdRUN_Click
    
End Sub

I know that unless I set the query sql back to "Select;", it will forever retain the last sql it was set to, but that is a non-issue, really.

One of the main reasons I chose not to go with a subform, is because a subform on the form set up for the user to run queries from is not practical (neither space wise or functional wise).
Thanks again for all the help!

 

[dhookom]

I use the subform solution and set the size of the subform to maximize the available screen space with code like:

Private Sub Form_Resize()
   On Error GoTo Form_Resize_Error

   Me.MySubForm.Height = Me.InsideHeight - (Me.Section(1).Height + Me.SubForm.Top + 100)
   Me.MySubForm.Width = Me.InsideWidth - 200

   On Error GoTo 0
   Exit Sub

Form_Resize_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Resize of VBA Document Form_frmMyQueryResults"
End Sub

This assures me the subform will grow to fit the available space.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Jean9 . . .

Your last presented code is exactly what I had in mind! Since your using different base SQL's along with variable criteria (per user), Open query save you column and heading problems.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]