Load ListView from a Query 1

[ZmrAbdulla]

Hi,
Can anyone show me sample code to populate a ListView from a Query with Criteria. I am using a code but unable to populate form a query.
Code is below

Private Sub ListNames_Click()
    Dim rs As New ADODB.Recordset   
    Dim colHeader As ColumnHeader
    Dim lstItem As ListItem
    Dim SQL As String  
  
    ListView1.ListItems.Clear
    SQL = "SELECT * FROM Ledger;"

    rs.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
   
        
    Me!ListView1.View = lvwReport
  
     'Set up column headers (not using)
   ' With ListView1.ColumnHeaders
    '    .Add , , "Names", 2000
     '  .Add , , "Date", 1000
     '   .Add , , "Category", 2000
     '   .Add , , "Credits", 1000
     '   .Add , , "Debits", 1000
   ' End With
   
    
   rs.MoveFirst
   While Not rs.EOF
    
 ' Add items and subitems to list control.
   
    Set lstItem = ListView1.ListItems.Add()
    lstItem.Text = Nz(Trim(rs!Names))
    lstItem.SubItems(1) = Format(Nz(Trim(rs!PymtDate)), "Medium Date")
    lstItem.SubItems(2) = Nz(Trim(rs!Category))
    lstItem.SubItems(3) = Format(Nz(Trim(rs!Credits), "0.00"), "Fixed")
    lstItem.SubItems(4) = Format(Nz(Trim(rs!Debits), "0.00"), "Fixed")
      
   rs.MoveNext    
     Wend  
     rs.Close 
  Exit Sub
End Sub

I am able to populate without criteria, not with criteria. This gives me an error saying Failed to open recordset.

'SQL = " SELECT * FROM Ledger WHERE Names = Forms![MainForm]![ListNames];"

Thanks


Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[MagicNS]

Thanks RoyVidar
Tip that you had give me is working fine. In the meantime I have put the .eof check in to my code but thanks for that also.
Thanks both of you for helping so quickly and I hope in the future my problems are going to be solved at least speedy as this one.
(In past week I had problems with my ISP and I wasn’t able to go online)

PS: I have another question but this time I have posted it in new thread “Encrypting data”