Treeview node click event to open a Specific Form

[cneill]

Can some one help?
I have built a treeview in MS Access 2003 but would now like to open a specific form using the node click event.
Not sure what info you need to help me, happy to supply my treeview code so that you can point me in the right direction.

Many thanks
cneill

 

[MajP]

normally I save the primary key of the record in the node key. Might require some string manipulation. Here is an example:

Private Sub Xtree_NodeClick(ByVal selectedNode As Object)
On Error GoTo Err_NodeClick
    Dim stDocName As String
    Dim stLinkCriteria As String
   Dim selectedUnit As String
    selectedUnit = Right(selectedNode.Key, Len(selectedNode.Key) - 1)
    stDocName = "frmSelectUnits"
    stLinkCriteria = "txtUICcode = '" & selectedUnit & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_NodeClick:
    Exit Sub
Err_NodeClick:
    MsgBox Err.Description
    Resume Exit_NodeClick
End Sub

 

[cneill]

hI Majp,

Thanks for the reply, I am getting a message to say the form has been cancelled. I am guessing that the underling query of the form is not picking up the stLinkCriteria, so I thought I should give you some more info.Below is the Treeview Code, the Parent being the MemberID, Child is the SiteID and the Child of Child is the ActivityID, so I need the ActivityID to be selected when opening the form so the correct record is selected.

Private Sub Form_Open(Cancel As Integer)
SetupTreeview
CreateKAMNodes
CreateSiteNodes
CreateSchemeNodes
End Sub

Private Sub SetupTreeview()
With Me.DatabaseTreeview
.Style = tvwTreelinesPlusMinusText
.LineStyle = tvwRootLines
.Indentation = 240
.Appearance = ccFlat
.HideSelection = False
.BorderStyle = ccFixedSingle
.HotTracking = True
.FullRowSelect = True
.Checkboxes = False
.SingleSel = False
.Sorted = False
.Scroll = True
.LabelEdit = tvwManual
.Font.Name = "Tahoma"
.Font.Size = 8
End With
End Sub

Private Sub CreateKAMNodes()

Dim rst As DAO.Recordset ' recordset for category data

' open the recordset for categories
Set rst = CurrentDb.QueryDefs!QryLookupKAMList.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.DatabaseTreeview.Nodes.add Text:=rst!MemberName, _
Key:="KAM=" & CStr(rst!MemberID)

rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Sub

Private Sub CreateSiteNodes()

Dim rst As DAO.Recordset ' recordset for product data

' open the recordset for products
Set rst = CurrentDb.QueryDefs!QryTreeSiteNames.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.DatabaseTreeview.Nodes.add Relationship:=tvwChild, _
Relative:="KAM=" & CStr(rst!MemberID), _
Text:=rst!SiteName, Key:="Site=" & CStr(rst!SiteID)

rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Private Sub CreateSchemeNodes()

Dim rst As DAO.Recordset ' recordset for product data

' open the recordset for products
Set rst = CurrentDb.QueryDefs!QryTreeSchemeNames.OpenRecordset

' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
Me.DatabaseTreeview.Nodes.add Relationship:=tvwChild, _
Relative:="Site=" & CStr(rst!SiteID), _
Text:=rst!SchemeName, Key:="ActivityID=" & CStr(rst!ActivityID)

rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Sub

I have modified your code as follows

Private Sub DatabaseTreeview_NodeClick(ByVal selectedNode As Object)
On Error GoTo Err_NodeClick

Dim stDocName As String
Dim stLinkCriteria As String
Dim selectedUnit As String
selectedUnit = right(selectedNode.Key, Len(selectedNode.Key) - 11)

stDocName = "FrmAccountSchemeCFLDetails"
stLinkCriteria = "ActivityID = '" & selectedUnit & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_NodeClick:
Exit Sub
Err_NodeClick:
MsgBox Err.Description
Resume Exit_NodeClick
End Sub

Not sure if my Treecode is the best way/fastest way to do but it works.
any thoughts where I am going wrong?
Thanks
cneill

 

[MajP]

Are you getting the message on the nodeclick event or another event? Does the rest of the code work?

After this
stLinkCriteria = "ActivityID = '" & selectedUnit & "'"
put a
msgbox stLinkCriteria
and see if this is returning what you really want to return.

Is your ActivityID numeric or text? You have it set up for text.

 

[cneill]

I am getting the message on the nodeclick event
When I interupt the Code using F8 to step through it

The SelectedUnit is giving me SelectedUnit = "2468" this is the correct ActivityID Number for the node I clicked

StrLinkCriteria is giving me StrLinkCriteria = "ActivityID ='2468'"

The ActivityID is numeric, how do I change it from Text to numeric?

Thanks

 

[MajP]

In sql you need to surround text field values in quotes. So if ActivityID is a numeric field then the "where" string looks like
"ActivityID = 2468"
and text would be like you have it
"ActivityID = '2468'"

To make sure this is working ok and there is not another issue, try a test by hardwiring the values.

stDocName = "FrmAccountSchemeCFLDetails"
stLinkCriteria = "ActivityID = 2468"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[cneill]

Hard wired and it worked so changed
stLinkCriteria = "ActivityID = '" & selectedUnit & "'"
to
stLinkCriteria = "ActivityID = " & selectedUnit & ""
all working fine, thanks for all your help
I am now going to move on to the next stage as some of the ActivityID's need to open different forms, wish me luck

Thanks for all your help very much appreciated.

 

[MajP]

Look at the tag property. When you create the nodes you could set the tag property equal to the name of the specific form to open.

 

[cneill]

Great Idea thanks
talk to you soon

 

[cneill]

Do you know how to correct the following
I have the following code =Avg([Total]) in a report footer
This calulates the avg of 8 records but if one of the records is Zero which I do not what to include in the Avg. How can I change the code to not include the Zero records?
Thanks