SQL for List Box Row Source

[Guest]

I am creating SGL statements for a list box programmatically in VBA.
Unfortuneately, they can get long, and sometimes exceed the length that the
rowsource property can hold. Is there a way to get around that, such as
saving the SQL as a query def, and simply calling the query?

 

[Douglas J. Steele]

Yes, you can just put the name of a named query.

 

[Guest]

The problem is that the SQL changes based on other controls on the form, so
the Query definition would have to change. Can I define the query through
VBA and then requery the control?

 

[Van T. Dinh]

Do you mean you use the values in the other Controls in the selection
criteria for the RowSource of the ListBox?

In that case, use the references to other Controls as Paramters in your
saved Query (use as the RowSource) and simply requer the ListBox when values
in other Controls change. Access will automatically resolve the Parameters
to the values in the other Controls and select the rows for the ListBox
according to your criteria.

 

[Guest]

that is correct, however, the values are not criteria for the fields, which
would be easy. The values are used in a subquery which is being used as a
criteria for one of the fields. As such, I wrote an algorithm which creates
the SQL, including the subqueries. The full SGL gets pretty long doing that,
and so can't be used as the value of the rowsource property. I need to find
another approach...

 

[Guest]

There are two ways I can think of. You can store the query by creating a
query definition in VBA. I know this is do-able, but haven't done it myself.
That's probably the best way to go, but like I said I haven't done it.
SOmeone else here could help with that. The other (possibly more complicated
and inefficient) way is to have a table defined in your appl that has all the
fields defined that you need for the listbox. Then in your VBA, after
building the SQL statement, create recordsets for the query and for the table
and loop through the query, writing a row to the table for each row from the
query. After you fill the table, requery the listbox. Base the listbox on the
table. You'd always need to empty the table before doing this (just use
docmd.runsql "Delete * from yourTableName" to empty it). When opening the
query, specify the name of the variable that has the SQL text rather than a
query name.

Jim B

 

[Guest]

Thanks Jim, that is definitely another approach, and was my plan B. Again,
thanks for the help

 

[John Spencer]

Use a saved query.

You can change the SQL of the saved query and then use it.

Currentdb.QueryDefs("MyAlreadySavedQuery").SQL = StrSQL

Forms!NameOfSomeForm!NameOfSomeControl.Requery


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..