VBA Problem on Treeview

  • Thread starter Thread starter g
  • Start date Start date
G

g

Hi, is there other way you can make this code shorter. This is just a sample
and it will go more than this.

Private Sub TreeView1_Expand(ByVal Node As Object)
Dim VarAA As Variant
Dim VarBB As Variant
Dim VarCC As Variant

VarAA = DLookup("TAH", "table_EmpProfile", "EmpName=UName2.Value")
VarBB = DLookup("MAH", "table_EmpProfile", "EmpName=UName2.Value")
VarCC = DLookup("WAH", "table_EmpProfile", "EmpName=UName2.Value")


If Node.Text = "TAH" Then
If VarBB = -1 Then
TreeView1.Nodes("B1").Expanded = False
End If
If VarCC = -1 Then
TreeView1.Nodes("C1").Expanded = False
End If

ElseIf Node.Text = "MAH" Then

If VarAA = -1 Then
TreeView1.Nodes("A1").Expanded = False
End If
If VarCC = -1 Then
TreeView1.Nodes("C1").Expanded = False
End If

ElseIf Node.Text = "WAH" Then

If VarAA = -1 Then
TreeView1.Nodes("A1").Expanded = False
End If
If VarBB = -1 Then
TreeView1.Nodes("B1").Expanded = False
End If

*I need to use the condition to check if the column TAH,MAH and WAH are true
coz some employe sometimes has 2 or more site assigned.

Thank you
 
hi G,
Hi, is there other way you can make this code shorter. This is just a sample
and it will go more than this.
Click to expand...
There is always another way :) This should work:

--
Option Compare Database
Option Explicit

' Using type-safe events.
Private WithEvents m_TreeView As MSComctlLib.TreeView

Private Sub Form_Load()

' The .Object is important.
Set m_TreeView = yourTreeViewCtl.Object

End Sub

Private Sub Form_Close()

Set m_TreeView = Nothing

End Sub

Private Sub m_TreeView_Expand(ByVal Node As MSComctlLib.Node)

Dim VarAA As Variant
Dim VarBB As Variant
Dim VarCC As Variant

VarAA = DLookup("TAH", "table_EmpProfile", "EmpName=UName2.Value")
VarBB = DLookup("MAH", "table_EmpProfile", "EmpName=UName2.Value")
VarCC = DLookup("WAH", "table_EmpProfile", "EmpName=UName2.Value")

Select Case Node.Text
Case Is = "TAH"
CollapseNode VarBB, "B1"
CollapseNode VarCC, "C1"
Case Is = "MAH"
CollapseNode VarAA, "A1"
CollapseNode VarCC, "C1"
Case Is = "WAH"
CollapseNode VarAA, "A1"
CollapseNode VarBB, "B1"
End Select

End Sub

Private Sub CollapseNode(AVariant As Variant, AKey As String)

On Local Error Resume Next

If AVariant = -1 Then
m_TreeView.Nodes(AKey).Expanded = False
End If

End Sub
 
Hi G,

and try to avoid several DLookup's on the same table with the same
criteria. I wrote a ALookup function so you can write:

Call ALookup("TAH,MAH,WAH", "table_EmpProfile", "EmpName=UName2.Value",
VarAA, VarBB, VarCC)

'----------------------------------------------------------------------
Public Function ALookup(Expr As String, Domain As String, Criteria As
String, ParamArray Vars()) As Boolean
On Error GoTo Err_ALookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Expr -> List of Field Names / Expressions
'Many Thanks to: Allen Browne. (e-mail address removed)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer

'Build the SQL string.
strSQL = "SELECT TOP 1 " & Expr & " FROM " & Domain & " WHERE " &
Criteria

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
ALookup = False
Else
For i = 0 To UBound(Vars)
If IsObject(Vars(i)) Then
Vars(i).Value = Nz(rs(i).Value)
Else
Vars(i) = Nz(rs(i).Value)
End If
Next
ALookup = True

End If
rs.Close

Exit_ALookup:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ALookup:
MsgBox Err.Description, vbExclamation, "ALookup Error " & Err.Number
Resume Exit_ALookup
End Function
 
Back
Top