Filling a listview control

[VB-Mike]

How can I loop through a db and fill a listview control with corresponding column names on the control. I set the column names when the form loads, ie.

ListView.ColumnHeaders.Clear
ListView.ColumnHeaders.Add , , "First Name"
ListView.ColumnHeaders.Add , , "Last Name"
ListView.ColumnHeaders.Add , , "Address"
ListView.ColumnHeaders.Add , , "City"
ListView.ColumnHeaders.Add , , "State"
ListView.ColumnHeaders.Add , , "Zip"
etc.....

I assume I am going to have to do a do until rs.eof loop, but I cannot figure out what to put in between....

[Chris]

Here is the sample code & hope it can help you.
Assume your Listview control name is lvwAddressBook
and the database name is AddressBook.mdb.

Option Explicit
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim SQL As String
Dim ADDRESSBOOK As ListItem
Private Sub Form_Load()
Set Db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\AddressBook.mdb", False, False)
SQL = "SELECT * FROM tblAddresssBook ORDER BY [First Name] ASC;"
Set Rs = Db.OpenRecordset(SQL, dbOpenSnapshot)
If Rs.RecordCount <> 0 Then
    lvwAddressBook.listitems.Clear
    lvwAddressBook.Visible = False
    While Not Rs.EOF
        DoEvents
        Set ADDRESSBOOK = lvwAddressBook.listitems.Add(, "A" & Rs.AbsolutePosition, Rs.Fields("First Name"))
        ADDRESSBOOK.SubItems(1) = Rs.Fields("Last Name")
        ADDRESSBOOK.SubItems(2) = Rs.Fields("Address")
        ADDRESSBOOK.SubItems(3) = Rs.Fields("City")
        ADDRESSBOOK.SubItems(4) = Rs.Fields("State")
        ADDRESSBOOK.SubItems(5) = Rs.Fields("Zip")
        Set ADDRESSBOOK = Nothing
        Rs.MoveNext
    Wend
    lvwAddressBook.Visible = True
    .Close
End If
Set Rs = Nothing
Db.Close
Set Db = Nothing
End Sub

[VB-Mike]

Chris, thank you for the quick reply. I think I am getting close but still not yet.

Here is what I have in the loop:

If rs_assoc.RecordCount <> 0 Then
While Not rs_assoc.EOF
DoEvents
Set ADDRESSBOOK = ListView.ListItems.Add(, "A" & rs_assoc.AbsolutePosition, rs_assoc.Fields("First Name"))
ADDRESSBOOK.SubItems(1) = rs_assoc.Fields("Last Name")
ADDRESSBOOK.SubItems(2) = rs_assoc.Fields("Address")
ADDRESSBOOK.SubItems(3) = rs_assoc.Fields("City")
ADDRESSBOOK.SubItems(4) = rs_assoc.Fields("State")
ADDRESSBOOK.SubItems(5) = rs_assoc.Fields("Zip")
Set ADDRESSBOOK = Nothing
rs_assoc.MoveNext
Wend
End If

The db connect and rs creation are in a module so all I need is the loop for this sub routine. So if I have a field in my database table called fname and a column header called First Name which I programatically set when the form loads then how do I pull the fields in fname and put them under First Name?

[Chris]

Originally posted by VB-Mike
Chris, thank you for the quick reply. I think I am getting close but still not yet.

Here is what I have in the loop:

If rs_assoc.RecordCount <> 0 Then
While Not rs_assoc.EOF
DoEvents
Set ADDRESSBOOK = ListView.ListItems.Add(, "A" & rs_assoc.AbsolutePosition, rs_assoc.Fields("First Name"))
ADDRESSBOOK.SubItems(1) = rs_assoc.Fields("Last Name")
ADDRESSBOOK.SubItems(2) = rs_assoc.Fields("Address")
ADDRESSBOOK.SubItems(3) = rs_assoc.Fields("City")
ADDRESSBOOK.SubItems(4) = rs_assoc.Fields("State")
ADDRESSBOOK.SubItems(5) = rs_assoc.Fields("Zip")
Set ADDRESSBOOK = Nothing
rs_assoc.MoveNext
Wend
End If

The db connect and rs creation are in a module so all I need is the loop for this sub routine. So if I have a field in my database table called fname and a column header called First Name which I programatically set when the form loads then how do I pull the fields in fname and put them under First Name?

Just change the following line will do.

Set ADDRESSBOOK = ListView.ListItems.Add(, "A" & rs_assoc.AbsolutePosition, rs_assoc.Fields("fname"))

[VB-Mike]

That did it. One more question for you. If the listview control finds a blank record in one of the record set fields it does not display any further records. Is there a simple way of telling the listview control to ignore blank entries and continue.

[ttingen]

You could use an If..End If to check the value of the record field. If the field isnull you could move to the next record and loop back to the beginning of the loop.



Example:

If IsNull(rs.Fields!YOUR_FIELD) Then
      rs.movenext
      loop 'or wend or next
End If