TreeView Help

[thelke]

Ok. this is in Access 2002. I currently have code here that creates 2 Trees. Tree1 opens forms in subMain on frm_Switchboard. Tree2 opens forms in subSelect on frm_Switchboard. All of the info comes from tbl-TreeForms. Backend is SQL 2k. I want to have 1 Tree open to either subMain or subSelect, depending on the criteria contained in tbl_TreeForms. I cannot figure out how to tell the code to differentiate. Can somone please look at the code and help me?

Thank you.

Option Compare Database
Option Explicit

Dim aTreeEnabled As Boolean
Dim bTreeEnabled As Boolean

Public Sub aEnableForm(aEnable As Boolean)
'=============================================================
'
' Enable/Disable the form
'
'=============================================================
' Parameters
'---------------
' aEnable (Boolean)
'=============================================================
On Error GoTo ErrorHandler

aTreeEnabled = aEnable

Finally: 'Always call this block of code to clean up objects etc.

Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.aEnableForm", ""

End Select
' End Error handling block.
End Sub

Public Sub bEnableForm(bEnable As Boolean)
'=============================================================
'
' Enable/Disable the form
'
'=============================================================
' Parameters
'---------------
' bEnable (Boolean)
'=============================================================
On Error GoTo ErrorHandler

bTreeEnabled = bEnable

Finally: 'Always call this block of code to clean up objects etc.

Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.bEnableForm", ""

End Select
' End Error handling block.
End Sub

Private Sub Form_Load()
'=============================================================
'
' Initialize the form on load
'
'=============================================================
' Parameters
'---------------
'
'=============================================================
On Error GoTo ErrorHandler

'DoCmd.Maximize

aTreeEnabled = True
bTreeEnabled = True
aBuildTree
bBuildTree

'InitUser

'subScratchpad.Form.Filter = "UserID = '" & objUserDetails.UserName & "'"
'subScratchpad.Form.FilterOn = True

'If subScratchpad.Form.UserID = "" Or IsNull(subScratchpad.Form.UserID) Then
'subScratchpad.Form.UserID = objUserDetails.UserName

'End If

Finally: 'Always call this block of code to clean up objects etc.

Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.Form_Load", ""

End Select
' End Error handling block.
End Sub

Sub aBuildTree()
'====================================================
'
' Build the tree of unapproved tasks based on the user
' information supplied.
'
' --------------
' Author - MCAN
' Created - 2001/01/03
'====================================================
On Error GoTo ErrorHandler

Dim conPO As ADODB.Connection
Dim rsFormCategories As ADODB.Recordset
Dim rsForms As ADODB.Recordset

Dim strCategoryText As String
Dim strFormText As String
Dim strCategoryKey As String
Dim strFormKey As String

Dim iJobID As Integer
Dim strJobKey As String
Dim strTaskKey As String
Dim strDate As String
Dim strDateKey As String
Dim dblTotalTaskDuration As Double
Dim dblTotalJobDuration As Double
Dim strDurationInHours As String

Set conPO = CurrentProject.Connection

Set rsFormCategories = New ADODB.Recordset

'Open a recordset of data for Purchase orders with outstanding items.
rsFormCategories.Open "select distinct Section from tbl_TreeForms where Application = 'DV' or Application = 'CUST' or Application = 'ADMIN' or Application = 'HR' or Application = 'ACCTG' order by section", conPO, adOpenStatic, adLockReadOnly

If rsFormCategories.RecordCount > 0 Then
Set rsForms = New ADODB.Recordset
Else
GoTo Finally
End If

treForms.Nodes.Clear

'Make sure we are at the first record.
rsFormCategories.MoveFirst

'Step through the recordset building the tree.
Do While Not rsFormCategories.EOF
'Construct the display text and unique key for the top level node.
'Node Keys must begin with a letter not a number
With rsFormCategories
strCategoryText = .Fields("Section&quot.Value
strCategoryKey = .Fields("Section&quot.Value

'Add the top level node for this record.
'And set the total duration for this date to 0
treForms.Nodes.Add , , strCategoryKey, strCategoryText

rsForms.Open "Select Description, FormName from tbl_TreeForms where Section = '" & .Fields("Section&quot.Value & "' and (Application = 'DV' or Application = 'CUST' or Application = 'ADMIN' or Application = 'HR' or Application = 'ACCTG')", conPO, adOpenStatic, adLockReadOnly

End With

Do While Not rsForms.EOF
With rsForms
strFormText = .Fields("Description&quot
strFormKey = .Fields("FormName&quot.Value

treForms.Nodes.Add strCategoryKey, tvwChild, strFormKey, strFormText
.MoveNext

End With

Loop

rsForms.Close
rsFormCategories.MoveNext

Loop

Finally:
Set conPO = Nothing
Set rsFormCategories = Nothing
Set rsForms = Nothing
Exit Sub

ErrorHandler:
Select Case Err
Case 70 'Progress Component Error
Resume Next

Case 0
Resume

Case Else
HandleGeneralError Err, "aBuildTree", Me.Name

End Select

End Sub

Sub bBuildTree()
'====================================================
'
' Build the tree of unapproved tasks based on the user
' information supplied.
'
' --------------
' Author - MCAN
' Created - 2001/01/03
'====================================================
On Error GoTo ErrorHandler

Dim conPO As ADODB.Connection
Dim rsFormCategories As ADODB.Recordset
Dim rsForms As ADODB.Recordset

Dim strCategoryText As String
Dim strFormText As String
Dim strCategoryKey As String
Dim strFormKey As String

Dim iJobID As Integer
Dim strJobKey As String
Dim strTaskKey As String
Dim strDate As String
Dim strDateKey As String
Dim dblTotalTaskDuration As Double
Dim dblTotalJobDuration As Double
Dim strDurationInHours As String

Set conPO = CurrentProject.Connection

Set rsFormCategories = New ADODB.Recordset

'Open a recordset of data for Purchase orders with outstanding items.
rsFormCategories.Open "select distinct Section from tbl_TreeForms where Application = 'subDV' or Application = 'subCUST' or Application = 'subADMIN' or Application = 'subHR' or Application = 'subACCTG' order by section", conPO, adOpenStatic, adLockReadOnly

If rsFormCategories.RecordCount > 0 Then
Set rsForms = New ADODB.Recordset
Else
GoTo Finally
End If

treSubForms.Nodes.Clear

'Make sure we are at the first record.
rsFormCategories.MoveFirst

'Step through the recordset building the tree.
Do While Not rsFormCategories.EOF
'Construct the display text and unique key for the top level node.
'Node Keys must begin with a letter not a number
With rsFormCategories
strCategoryText = .Fields("Section&quot.Value
strCategoryKey = .Fields("Section&quot.Value

'Add the top level node for this record.
'And set the total duration for this date to 0
treSubForms.Nodes.Add , , strCategoryKey, strCategoryText

rsForms.Open "Select Description, FormName from tbl_TreeForms where Section = '" & .Fields("Section&quot.Value & "' and (Application = 'subDV' or Application = 'subCUST' or Application = 'subADMIN' or Application = 'subHR' or Application = 'subACCTG')", conPO, adOpenStatic, adLockReadOnly

End With

Do While Not rsForms.EOF
With rsForms
strFormText = .Fields("Description&quot
strFormKey = .Fields("FormName&quot.Value

treSubForms.Nodes.Add strCategoryKey, tvwChild, strFormKey, strFormText
.MoveNext

End With

Loop

rsForms.Close
rsFormCategories.MoveNext

Loop

Finally:
Set conPO = Nothing
Set rsFormCategories = Nothing
Set rsForms = Nothing
Exit Sub

ErrorHandler:
Select Case Err
Case 70 'Progress Component Error
Resume Next

Case 0
Resume

Case Else
HandleGeneralError Err, "bBuildTree", Me.Name

End Select

End Sub

Private Sub treForms_NodeClick(ByVal Node As Object)
'=============================================================
'
' When a node of the tree is clicked if it is a form node, a child
' then show the corresponding form in the sub main area unless the
' bTreeEnabled is set to true in which case a form in sub main
' is currently being edited
'
'=============================================================
' Parameters
'---------------
' Node (Object)
'=============================================================
On Error GoTo ErrorHandler

Echo False

If Node.Parent Is Nothing Then
'Do Nothing

Else
If aTreeEnabled Then
subMain.SourceObject = Node.Key

End If

End If

Finally: 'Always call this block of code to clean up objects etc.
Echo True
Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.treForms_NodeClick", ""
GoTo Finally
End Select
' End Error handling block.
End Sub

Private Sub tresubForms_NodeClick(ByVal Node As Object)
'=============================================================
'
' When a node of the tree is clicked if it is a form node, a child
' then show the corresponding form in the sub main area unless the
' bTreeEnabled is set to true in which case a form in sub main
' is currently being edited
'
'=============================================================
' Parameters
'---------------
' Node (Object)
'=============================================================
On Error GoTo ErrorHandler

Echo False

If Node.Parent Is Nothing Then
'Do Nothing

Else
If aTreeEnabled Then
subSelect.SourceObject = Node.Key

End If

End If

Finally: 'Always call this block of code to clean up objects etc.
Echo True
Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.tresubForms_NodeClick", ""
GoTo Finally
End Select
' End Error handling block.
End Sub

Public Sub RefreshToDos()
'=============================================================
'
' Refresh the To-Do Sub form
'
'=============================================================
' Parameters
'---------------
'
'=============================================================
On Error GoTo ErrorHandler

' subToDos.Form.RefreshForm

Finally: 'Always call this block of code to clean up objects etc.

Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.RefreshToDos", ""

End Select
' End Error handling block.
End Sub

Public Sub RefreshMainForm()
'=============================================================
'
' Refresh the main form
'
'=============================================================
' Parameters
'---------------
'
'=============================================================
On Error Resume Next

subMain.Form.RefreshData
subSelect.Form.RefreshData

Finally: 'Always call this block of code to clean up objects etc.

Exit Sub

' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
ErrorHandler: ' Automatic error handler last updated at Thursday, March 22, 2001 6:36:06 PM
Select Case Err
Case Else
HandleGeneralError Err, "Form_frm_Switchboard.RefreshMainForm", ""

End Select
' End Error handling block.
End Sub