ms-access: instead of rowsource, running from query

Question

i have a very complex query that is running from a listbox rowsource. i just do a listbox1.requery and it populates the listbox.

instead of doing it this way, i would like to:

1. i just want to save the query in the queries section
2. call it from there.
3. then i want to save the results of the query into a string
4. then i want to feed the string into the listbox

can you please help me with the code for these four questions.

thanks!

Answer 1

you could just set the rowsource of the listbox to your query.

Answer 2

I think your first 3 items are addressed by this answer to your other question:

http://stackoverflow.com/questions/1369594/ms-access-save-query-in-a-string/1370095#1370095

As for the fourth item in this question, set your list box Row Source Type to "Value List" and write your string to its Row Source property.

Answer 3

another solution is to open the query in a recordset and then set the recordset property of the listbox control to it. I have my own function for that (I use it mostly for comboboxes). If necessary, you can add an extra 'connection' parameter to the sub when you want to open a recordset from another database.

Public Sub addQueryToCombobox(x_query As String, x_control As Control)
Dim rs As ADODB.Recordset

On Error GoTo ERREUR

Set rs = New ADODB.Recordset

Set rs.ActiveConnection = CurrentProject.AccessConnection

rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly
rs.CursorLocation = adUseClient

rs.Open x_Query

Set rs.ActiveConnection = Nothing

Set x_control.Recordset = rs

Set rs = Nothing

On Error GoTo 0
Exit Sub

ERREUR:
'add here your own error manager'
End Sub