2
\$\begingroup\$

I have the following function that causes a significant visual jittering and lag in my access database - This is a treeview selection based on data in a table (tblDATA) with the fields:

Object | Attribute | Property | Value

I have tried echo and me.painting but figured my actual for loops need to be looked at for optimisation - wondering if there is a better way to cycle through a Recordset and populate a Treeview ?

' [1] Update the treeview
'------------------------------------

Private Sub btnUpdateFilter_Click()
    
    'Me.Painting = False
    'Echo False
    'DoCmd.Hourglass True
    
    Dim sArea As String, N As Integer
    Dim oData_distinct As DAO.Recordset
    Dim oData As DAO.Recordset
    Dim oNone As DAO.Recordset
    Dim rs As DAO.Recordset
    Dim element As Variant
    Dim Arr_distinct() As Variant
    
    If IsNull(cbxAttributeFilter01) Or IsNull(cbxPropertyFilter01) Then Exit Sub
    

    Set oData_distinct = CurrentDb.OpenRecordset("SELECT DISTINCT [Value] FROM tblData WHERE Attribute = " & cbxAttributeFilter01 _
                                                & " AND Property = " & cbxPropertyFilter01, dbOpenSnapshot)
    
    Set oNone = CurrentDb.OpenRecordset("SELECT [Tagname] FROM tblData WHERE NOT Attribute = " & cbxAttributeFilter01 _
                                                & " AND NOT Property = " & cbxPropertyFilter01, dbOpenSnapshot)
    
    Set oData = CurrentDb.OpenRecordset("SELECT [Tagname], [Value] FROM tblData WHERE [Attribute] = " & cbxAttributeFilter01 _
                                                & " AND [Property] = " & cbxPropertyFilter01, dbOpenSnapshot)
    
    
    If oData.RecordCount = 0 Then TreeView.Nodes.Clear: Exit Sub
    
    Arr_distinct = oData_distinct.GetRows(oData_distinct.RecordCount)
    
    TreeView.Nodes.Clear
    
    ' Standard
    TreeView.Nodes.Add , , "MISC", "MISC"
    For Each element In Arr_distinct
        If Not element = "" Then
            If IsNumeric(element) Then element = "_" & element
            TreeView.Nodes.Add , , element, element    ' ADD Parent
        End If
    Next element
    
    Dim sTemp As String
    
    With oData
        .MoveFirst
        Do While Not .EOF
            sTemp = !Value
            If IsNumeric(sTemp) Then sTemp = "_" & sTemp
            If sTemp = "" Or sTemp = " " Then sTemp = "MISC"
            On Error Resume Next
            TreeView.Nodes.Add sTemp, tvwChild, DLookup("Tagname", "MASTER", "ID = " & !Tagname), DLookup("Tagname", "MASTER", "ID = " & !Tagname)
        .MoveNext
        Loop
    End With
        
    With oNone
        .MoveFirst
        Do While Not .EOF
            On Error Resume Next
            TreeView.Nodes.Add "MISC", tvwChild, DLookup("Tagname", "MASTER", "ID = " & !Tagname), DLookup("Tagname", "MASTER", "ID = " & !Tagname)
            .MoveNext
        Loop
    End With
   
   'DoCmd.Hourglass False
   'Echo True
   'Me.Painting = True
   
End Sub
```
\$\endgroup\$
3
\$\begingroup\$

Avoid the multiple DLookUps with eachTreeView.Nodes.Add call for every row of recordset! At the very least store your DLookUp value once and apply it to treeview. This cuts down the repetitive 4 calculations down to 2 for every row.

But since DLookUp essentially is a query anyway, simply integrate a JOIN to Master object in SQL queries of OpenRecordset calls. This cuts down the repetitive 4 calculations to 0 for every row.

sql = "SELECT d.[Tagname], m.[Tagname] AS [MasterTagname] " _
       & "FROM tblData d " _
       & "LEFT JOIN Master m " _
       & "    ON m.ID = d.[Tagname] " _
       & "WHERE NOT d.[Attribute] = " & cbxAttributeFilter01 _
       & "  AND NOT d.[Property] = " & cbxPropertyFilter01

Set oNone = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)

sql = "SELECT d.[Tagname], m.[Tagname] AS [MasterTagname] " _
       & "FROM tblData d " _
       & "LEFT JOIN Master m " _
       & "    ON m.ID = d.[Tagname] " _
       & "WHERE d.[Attribute] = " & cbxAttributeFilter01 _
       & "  AND d.[Property] = " & cbxPropertyFilter01

Set oData = CurrentDb.OpenRecordset(sql, dbOpenSnapshot) 

...

TreeView.Nodes.Add sTemp, tvwChild, !MasterTagname, !MasterTagname

...

TreeView.Nodes.Add "MISC", tvwChild, !MasterTagname, !MasterTagname

Also, best practice in VBA is to never use On Error Resume Next but to handle any error that raises which in this case may be a missing or Null DLookUp result. Therefore, enclose If logic.

...

If Not IsNull(!MasterTagName)
   TreeView.Nodes.Add sTemp, tvwChild, !MasterTagname, !MasterTagname
End If

...

If Not IsNull(!MasterTagName)
   TreeView.Nodes.Add "MISC", tvwChild, !MasterTagname, !MasterTagname
End If
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.