CODEWITHSUNDEEP
excelvbams-accessrecordset
aCarella
aCarella

Reputation: 2578

VBA: How to add entire row of recordset into form list

I have Excel VBA code that runs a select query on an access database and returns a recordset. The connection works fine. When I get the recordset, I can access each field in each row fine. Here is what I am currently using:

Sub accessSelect(sql)
Set rs = New ADODB.Recordset
Call accessConnection

With rs
    .Open sql, conn
End With

Do While Not rs.EOF
    'rtvEditForm.rtvList is a list box.
    rtvEditForm.rtvList.AddItem rs(1) & " - " & rs(2)
    rs.MoveNext
Loop
End Sub

I want to be able to throw the whole rs in the rtvEditForm.rtvList without having to refer to each field. I've tried rtvEditForm.rtvList.AddItem rs, but that does not work because I get a "Type Mismatch" error.

Seems simple but I just can't figure this out. How would I do that with my current code?

Upvotes: 0

Views: 2318

Answers (1)

Brad
Brad

Reputation: 12253

No need to do any looping. A listbox has a recordset property which can be bound to an ADO recordset.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection

Set cn = CurrentProject.Connection

Set rs = New ADODB.Recordset

With rs
    Set .ActiveConnection = cn
    .Source = sql
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open
End With

Set Me.rtvList.Recordset = rs
rtvList.ColumnCount = rs.Fields.Count

Upvotes: 2

Related Questions