Use onClick event to open query text displayed in form field?

[joswald]

I have a table that holds the SQL texts for ca. 1000 Select queries (mostly minor variants that are used to programmatically swap out RowSource strings for combo boxes). I'd like to have a quick and easy way to open/review/modify these queries.

One strategy would be to display the SQL strings in a field on a Datasheet form, then use an onClick event on a text box linked to the SQL-holding field (or perhaps an onClick event tied to an unbound text box on the form) to open the associated query. That would allow me to view the SQL of the query that I want to open, allow me to quickly scroll through the list of stored SQL texts, and give me options for sorting or limiting the SQL-texts displayed in the datasheet form.

But, I can't seem to get the onClick event to work. The problem seems to be that I can't figure out how to pass the SQL string contained in the field to a function that will use that string to open the query (sorry, I'm not too proficient in Visual Basic at this point...). I may be overlooking something simple. Can anyone help out with some coding ideas to get this done?

Or, is there another strategy that might be better to accomplish the general objective here?

 

[CJ_London]

It may be your terminology but you should be passing the name of the query (not the SQL string) to your function.

In fact you don't need a function just in the onclick event put the following code

DoCmd.OpenQuery "YourQueryNameHere"

I'm not sure why you have the sql strings in a separate table - must be quite a job to make sure they are the same as the query itself - which makes me wonder if I have understood the question.

 

[pbaldy]

No offense, but this is highly suspect. What makes these different: "mostly minor variants that are used to programmatically swap out RowSource strings for combo boxes"?

You aren't creating separate queries when you could just use parameters, like for different companies, departments, employees or whatever are you?

 

[joswald]

Hi Paul: Most of the queries are already paramaterized select queries. But the table actually contains a wide variety of different kinds of queries, all of which I would still like a convenient way to open and display. At the risk sidetracking my original question, below is a description of how most of the queries are used.

The paramaterized queries are used as part of a two-step process to display data for individual records on a complex form. Step 1: a specific search strategey (say, search by year) is selected from one one combo box; that selection loads a paramaterized query as the RowSource behind a second combo box; Step 2: clicking the arrow on the second combo box runs the paramaterized query; after accepting user data (say, 1845), the second combo box displays the results of the paramaterized query; the second combo box then retains the results of that query, allowing multiple records to be selected from the results of that query. There are lots of queries because (1) the same search strategy is employed on about 10 different complex forms, (2) each form contains duplicate two-step controls [allowing the results from two different queries to be temporarily stored on each form -- one in each of the Step 2 combo boxes -- and allowing records from either list to be displayed on the form], and (3) there are lots of Step 1 options (typically 20-30), which are different on each of the forms.

 

[joswald]

CJ: The queries do not exist as separately named objects in the database; they only exist as SQL strings in the table. According Database Journal article 3505836 (sorry, I can't paste the link, my post count isn't high enough) "raw SQL" like this can not be handled by OpenQuery.

I explained above a bit about why the queries proliferated. After a while I got tired of making up new names for them, and they cluttered up the old database window, so I eventually just put them all in a table where I could recall them as needed.

 

[CJ_London]

OK, I actually do something similar for queries which would otherwise be hardcoded in VBA or I don't want to allow the users to mess with - Since these are SELECT queries suggest you use some code to create a querydef for a temporary query, save it then open it.

Another more complex alternative, but perhaps neater solution is to have a default datasheet form as a subform with enough unbound textbox controls (Note, you don't need the label controls) to satisfy the largest number of columns required (Note to cover all eventualities, have 255 controls which is the maximum a query can return), then in VBA set the datasheet recordsource to your SQL string and parse through the columns assigning fields in your rowsource to the controlsources of your controls, then hiding any remaining unbound controls. Note it only works for text, date and number datatypes (booleans will appear as 0 or -1).

 

[joswald]

CJ: If I understand your second solution correctly, it would work primarily to just display the results of the query in the subform. What I would really like is a way to open the query in the Access query window so that I can toggle back and forth among Datasheet, SQL and Design views. That would allow me to easily view, and modify/test, the queries. Your first solution seems better suited to that. I think the following code get's me most of the way there -- but nothing is being displayed. Suggestions?

Private Sub cmdDisplayQuery_Click()
Dim qdf As DAO.QueryDef
Dim strSQL As String
    strSQL = Me!txtSql  'initalize string strSQL with SQL text extracted from form field
    Set qdf = CurrentDb.QueryDefs("qryTemp")   'initialize query definition qdf with
    'existing database temporary query qryTemp
    qdf.sql = "" & strSQL & ""    'reset query definition qdf to SQL from form field
    CurrentDb.OpenRecordset "qryTemp", , dbFailOnError  'open recordset from qryTemp; this
    'appears to work, at least I don't get any errors, but nothing is displayed; why
    'doesn't this open a window and display the results of the query? What do I need
    'to do to actually display qryTemp in an Access query window? Am probably missing
    'something pretty basic here...
 
End Sub

 

[pbaldy]

Untested, but try

DoCmd.OpenQuery "qryTemp", acViewDesign

The OpenRecordset would open the query into memory where it could be worked with, not visually.

 

[CJ_London]

You might need to do

qdf.Save
qdf.close
set qdf=nothing

before using DoCmd.OpenQuery per Pauls suggestion

 

[joswald]

Paul: Works like a charm. Had lost sight of the fact that qryTemp was in fact a named QueryDef object, so OpenQuery would work.

CJ: Don't appear to need the extra lines, but they might be good just to finish things off more cleanly.

Thanks to both of you for the suggestions.

 

[pbaldy]

Happy to help!