populate listbox form database table



I have hte following code to populate a listbox in Outlook form an MS Access database table

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim rsArray As Variant

strSQL = "SELECT tbl_PJ_Project.PJ_Name, tbl_PJ_Project.PJ_ProjectUID " & _
"From tbl_PJ_Project " & _
"WHERE (((tbl_PJ_Project.PJ_Name) Like '*" & Me.txtSearchProjName & "*')) " & _
"ORDER BY tbl_PJ_Project.PJ_Name;"

'Debug.Print strSQL

Set db = OpenDatabase(fncProjectDB)
Set rs = db.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
'there are records so do something
rsArray = rs.GetRows
'no records do something else
rsArray = "------No results found--------"
End If

'Place data in the listbox
With Me.lstResult
..ColumnCount = 2
..BoundColumn = 2
..Column = rsArray
..ListIndex = -1
End With

Set rs = Nothing
Set db = Nothing


Problem is its only populating the listbox for the first record.

Any suggestions why this is

Thanks Grant


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question