Access db, trying to list the results of query (in a recordset) in a list box using VBA
Posted on 2016-11-30
I am using VBA in Access 2010.
I am trying to show the results of a query in a list box. I am putting the data into a recordset and then using a function to put it into the list box. I have used the code before many time with no problem, but I decided to modify the codes a bit to use DAO.
The problem is it only grabs the first row. The record count for the query is 87 rows. Not sure why it is not pulling all of them. The code is below.
Private Sub cmdFindBatch_Click()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strList As String
strSQL = "SELECT BATCH_NO, DateRan " _
& "FROM batch LEFT JOIN tblChron ON batch.BATCH_NO = tblChron.BatchDate " _
& "WHERE Vendor = 'M' " _
& "ORDER BY BATCH_NO DESC"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
strList = BuildString(rst)
Me.lstView.RowSource = strList
Private Function BuildString(rst As DAO.Recordset) As String 'Follows Findbatch
Dim strReturn As String
Dim varItems As Variant
Dim x As Integer
Dim y As Integer
varItems = rst.GetRows()
For x = LBound(varItems, 2) To UBound(varItems, 2)
For y = LBound(varItems, 1) To UBound(varItems, 1)
strReturn = strReturn & varItems(y, x) & ";"
BuildString = strReturn