ListView control loads very slowly

Question

What is the fastest way to populate a ListView from a query when it has more than 15000 lists with 9 subitems. It takes me about 6 minutes to download.

Here is what I wrote to populate the ListView control.

Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly, dbReadOnly) With Me.listViewData .View = lvwReport .GridLines = True .FullRowSelect = True .ListItems.Clear .ColumnHeaders.Clear End With 'Set up column headers With Me.listViewData.ColumnHeaders .Add , , "Client", 1440, lvwColumnLeft .Add , , "Contact", 2160, lvwColumnLeft .Add , , "Quote #", 720, lvwColumnCenter .Add , , "Date", 1140, lvwColumnLeft .Add , , "GrandTotal", 1440, lvwColumnRight .Add , , "Weighted Value", 1440, lvwColumnRight .Add , , "Chance %", 500, lvwColumnRight .Add , , "Sales Cycle", 1140, lvwColumnRight .Add , , "Won Orders", 1000, lvwColumnRight .Add , , "SalesRep", 1000, lvwColumnRight End With While Not rs.EOF Set lstItem = Me.listViewData.ListItems.Add() lstItem.Text = Nz(rs!Name, "") lstItem.SubItems(1) = Nz(rs!Company, "") lstItem.SubItems(2) = Nz(rs!QuoteNumber, "") lstItem.SubItems(3) = Nz(rs!OrderDate, "") lstItem.SubItems(4) = Nz(Format(rs!GrandTotal, "Currency"), "0.00") lstItem.SubItems(5) = Nz(Format(rs!GrandTotal * rs!Customfield1 / 100, "Currency"), "") lstItem.SubItems(6) = Nz(rs!Customfield1, "") lstItem.SubItems(7) = Nz(rs!Date1, "none") lstItem.SubItems(8) = Nz(rs!Detail, "") lstItem.SubItems(9) = Nz(rs!CustomT1, Nz(rs!UserID, "")) For I = 1 To Me.listViewData.ColumnHeaders.Count - 1 Set sb = lstItem.ListSubItems(I) If rs!Customfield1 = 100 Or Not IsNull(rs!List) Then sb.ForeColor = vbBlue lstItem.ForeColor = vbBlue ElseIf rs!Cancelled = -1 Then sb.ForeColor = vbRed lstItem.ForeColor = vbRed Else sb.ForeColor = vbBlack lstItem.ForeColor = vbBlack End If DoEvents Next rs.MoveNext Wend 

Answer 1

I can remember a couple of things:

While ... Wend is a slower looping mechanism; use to ... Next. For ... Further it is faster - even if you need to run one more command to receive RecordCount. This is what I use:

 With rs If .RecordCount > 0 Then '-- MoveLast...MoveFirst will update the .RecordCount; depending on the type of DAO Recordset, RecordCount might only return "1" when there are more than that. .MoveLast .MoveFirst For lngCounter = 1 To .RecordCount '-- Code to add ListItems here .MoveNext Next lngCounter End If .Close End With 

Use with ... End With to add your subtypes:

 With Me.listViewData.ListItems.Add .Text = Nz(rs!Name, "") .SubItems(1) = Nz(rs!Company, "") .SubItems(2) = Nz(rs!QuoteNumber, "") .SubItems(3) = Nz(rs!OrderDate, "") .SubItems(4) = Nz(Format(rs!GrandTotal, "Currency"), "0.00") .SubItems(5) = Nz(Format(rs!GrandTotal * rs!Customfield1 / 100, "Currency"), "") .SubItems(6) = Nz(rs!Customfield1, "") .SubItems(7) = Nz(rs!Date1, "none") .SubItems(8) = Nz(rs!Detail, "") .SubItems(9) = Nz(rs!CustomT1, Nz(rs!UserID, "")) End With 

Exchange population code in:

 DoCmd.Echo False '-- Populate code DoCmd.Echo True 

Hope this helps!

Answer 2

The first thing you need to do is get rid of doevents. This is a real productivity killer.

Do I need to load listview dynamically? Why not bind it directly to the data source?

Answer 3

Check the methods of the Listview control for something like beginupdate / endupdate or set refresh to false (if possible). This means that the user interface will not try to refresh the screen after adding each element, thereby making the addition much faster.

The Listview control is designed to receive a large number of items, so this is possible with a little digging.