Populating listbox with MS Access table

[Techworks]

I have the following code to populate a listbox in Outlook from 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

'Stop

Set db = OpenDatabase(fncProjectDB)

Set rs = db.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then

'there are records so do something

rsArray = rs.GetRows

Else

'no records do something else

rsArray = "------No results found--------"

End If

'Place data in the listbox

With Me.lstResult

.Clear

.ColumnCount = 2

.BoundColumn = 2

.Column = rsArray

.ListIndex = -1

End With

rs.Close

Set rs = Nothing

Set db = Nothing

----------------------

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

Any suggestions why this is

Thanks Grant

 

[EricLegault]

Maybe try looping through the recordset instead?

e.g.

Do Until  rs.EOF = True[INDENT]lstResult.AddItem(rs.Fields("MyField").Value)[/INDENT] 
[INDENT]rs.MoveNext[/INDENT] 
 Loop

 

[Techworks]

ok figured out why not working. Obviously most of the examples out there are not real world solutions that work as they have flawed code.

I finally managed to find one example that was different and it works. The rst.GetRows line need to specify how many rows to return, so should look like this:
rsArray = rst.GetRows(lngRowCount)