reusing SQL statement (1 Viewer)

[pottera]

I have a listbox on one of my forms which is populated by an SQL query that I build in code. The query can be reasonably complex with a few fields, many joins and where conditions. The main table selected from is the Agency table (40 odd fields) but there are joins to five other tables to match the possible search criteria. I am using totalling in the query (ie Group by, first etc).

Once the listbox is populated, I want to use the list of selected Agencies as a select list for reports and table edits.

If I use the Select statement or WHERE statement that I generate, I find it very confusing and limiting - having to use FirstOF.. instead of field names etc limits the way I can utilise the form or report.

Is there some way that I can capture the Agency ID's produced by my original query and pass that alone to a report or form?

Hope someone there is not confused by my explanation.

Thanks in advance.

 

[Rich]

Can't you use a Dynamic QueryDef, assuming I've understood you correctly?

 

[Jack Cowley]

Try something along these lines...

Dim db As DAO.Database
Dim QD As DAO.QueryDef
Dim where As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.
On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

...All your SQL stuff here...

Set QD = db.CreateQueryDef("Dynamic_Query", YourSQLStringVariableHere)

Set the List RowSource to Dynamic_Query

hth,
Jack

 

[pottera]

Jack/Rich, Thanks for the tip there - code worked perfectly Jack - cut and paste and away it went - I now know how to use QueryDefs.

However, it still leaves me with the main problem - From the list box, I can double click on one of the items and bring up an Agency Update form - I want it too pull up all records from the listbox (and position to the selected item). Hence the QueryDef should be ideal except that the original SQL is full of Joins to other tables that are not used in my Update form as well it uses totalling. I could add the 40 or so fields to the query (with FirstOfFieldNames etc) and change my Update form - however this has two issues - 1) seriously complicates my code and 2) I also want to access the Update date form directly - prior to the SQL being generated and would perfer not to have two copies.

Any views?

 

[Jack Cowley]

Create a query based on your table and the Dynamic_Query. Add the Asterisk for the table if you want all the fields from the table and those that you want from the Dynamic_Query. Base your Agency Update form on that....

I hope I understood what you are trying to do...

Jack

 

[pottera]

Thanks Jack - that worked great.