Using a Recordset as the RowSource for a multi-column List Box

Question

I have the following code:

Private Sub Form_Load()

    Dim dbs As DAO.Database

    Dim qdf As DAO.QueryDef

    Dim rst As DAO.Recordset

    Set dbs = CurrentDb

    Set qdf = dbs.QueryDefs("qryGetRelatedSPRFNumbers")

    qdf.Parameters("SPRFNum").Value = strSelectedSPRFNum

    qdf.Parameters("AppName").Value = strSelectedProviderApp

    Set rst = qdf.OpenRecordset(dbOpenSnapshot)

    With Me.RelatedSPRFsListBox

        .RowSource = rst

        .Requery

    End With

End Sub

However, when I attempt to compile the code, I get a "Compile error: Type mismatch" error message, with the .RowSource = rst line highlighted.  Please note that qryGetRelatedSPRFNumbers produces records containing data in two fields, and RelatedSPRFsListBox's ColumnCount property = 2.

Is there a way to modify my code so that I can utilize the recordset as the RowSource for the List Box?  Thanks in advance for any assistance.

Answer 1

The RowSource property is a string. It can be set to

• The name of a table.
• The name of a query.

- A SQL string corresponding to a Select query.

A recordset is an object, not a string, so you can't set the RowSource property to a recordset.

You could create a string variable strSQL, set it to the SQL of the qryGetRelatedSPRFNumbers query and replace the SPRFNum parameter with strSelectedSPRFNum and the AppName parameter with strSelectedProviderApp. Then set the RowSource of the list box to strSQL.

Answer 2

Thanks for the confirmation.

Answer 3

True, but in most situations that shouldn't be a problem...

Answer 4

Thanks for your suggestion.  It sounds like I cannot take advantage of the Jet engine's Rushmore technology since I will be executing the query from within VBA using something like dbs.Execute strSQL.  Do you agree?  Thanks again.