asked on

Recordset into Listview

Experts:

I have a piece of code (See below) that I am having some difficulty with. I want the list view to grab the Product Name (pdtname) from the rst instead of the Product Number (pdtnumber) in the
first column of the list view. Right now the first column is the pdtnumber......so for the matching pdtnumber in the array populate the matching pdtname from the recordset. Any ideas?

Thanks.

Code Snippet:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

conn.Open

rst.Open "SELECT pdtnumber, pdtabbr, pdtname FROM product", conn, adOpenKeyset, adLockOptimistic

' Create the number of columns you need.
lvwData.ColumnHeaders.Add , , "Product Number", 1390
lvwData.ColumnHeaders.Add , , "Issue", 2400, vbCenter
lvwData.ColumnHeaders.Add , , "Ordered", 2400, vbCenter

For j = 1 To 32

k = VBGetProdData(j, stnpd(j))

' filter out blanks here
If stnpd(j).pdtno > 0 Then

Set oItem = lvwData.ListItems.Add(, , stnpd(j).pdtno)

With lvwData.ListItems(j)

.SubItems(1) = stnpd(j).issord
.SubItems(2) = stnpd(j).pdtdmy

End With

j1 = j1 + 1

End If

Next j

ASKER CERTIFIED SOLUTION

MYLim

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rnaylor_psu

ASKER

not really what i needed, but a good post non the less. I appreciate your response so I accepted the answer. Thank you. The solution I wanted is below...I used the rst.Filter to solve the issue.

thanks again.

CODE SNIPPET:

rst.Open "SELECT * FROM vwActiveProducts", conn, adOpenKeyset, adLockOptimistic

For j = 1 To 32

k = VBGetProdData(j, stnpd(j))

' filter out blanks here
If stnpd(j).pdtno > 0 Then

rst.Filter = "pdtno = '" & stnpd(j).pdtno & "'"
Set oItem = lvwData.ListItems.Add(, , rst!PublicationDate)

With lvwData.ListItems(j)

rst.Filter = "pdtno = '" & stnpd(j).pdtno & "'"
.SubItems(1) = rst!ProductName
.SubItems(2) = stnpd(j).pdtno
.SubItems(3) = stnpd(j).publyear
.SubItems(4) = stnpd(j).issord
.SubItems(5) = stnpd(j).pdtrnno

End With

j1 = j1 + 1

End If

Next j