• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Filter a Data Control and then create a new record set with the results. Then show filtered record

Filter a Data Control and then create a new record set with the results. Then show filtered record

Filter a Data Control and then create a new record set with the results. Then show filtered record set in a listview control.

Rate Filter a Data Control and then create a new record set with the results. Then show filtered record

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Dim rs As Recordset
Dim itmx As ListItem
Dim colx As ColumnHeader

'Make sure you have a listview added to your form and a data control. You
'also need to add a text box and a command button to activate this sub

'set listview to report view
ListView1.View = lvwReport

'setup listview column headings
'You can change these to match headings in your table

Set colx = ListView1.ColumnHeaders.Add(, , "Inquiry ID")
Set colx = ListView1.ColumnHeaders.Add(, , "Date")
Set colx = ListView1.ColumnHeaders.Add(, , "Given By")
Set colx = ListView1.ColumnHeaders.Add(, , "Taken By")
Set colx = ListView1.ColumnHeaders.Add(, , "Order Number")
Set colx = ListView1.ColumnHeaders.Add(, , "Invoice")
Set colx = ListView1.ColumnHeaders.Add(, , "Customer PO")

'set on error so that if a field is null it will
'not crash the sub or your program

On Error Resume Next

'clear the listview incase there was data there
ListView1.ListItems.Clear

'move the data control to the first record
Data1.Recordset.MoveFirst

'apply a filter using the field name in the table and the data to filter by
'add a text box to your form and a command button to activate this sub to
'test the filter

Data1.Recordset.Filter = "C_Account= '" & Text1.Text & "'"
'add the filtered data to a new recordset named rs

Set rs = Data1.Recordset.OpenRecordset

'move the new recordset to the first record
rs.MoveFirst

'loop through the new table and add each item to 
'the listview control

While rs.EOF = False
    
        Set itmx = ListView1.ListItems.Add(, , rs!IQID)
        itmx.SubItems(1) = rs!c_date
        itmx.SubItems(2) = rs!c_givenby
        itmx.SubItems(3) = rs!c_takenby
        itmx.SubItems(4) = rs!c_orderNo
        itmx.SubItems(5) = rs!c_invoice
        itmx.SubItems(6) = rs!c_custpo
    
rs.MoveNext
Wend

end sub