Treeview assistance requested 2

[directorz]

Hello experts,

I’m stumbling with a treeview. I’ve created my first one and have it populated. I have difficulty in understanding how to associate the value that a node represents to a record…let me elaborate.

The db I’m working on focuses on periodic maintenance of cars. Maintenance schedules can be assigned to each car like preventive maintenance 'a'(PM-A) or preventive maintenance 'b' (PM-B) and so on. Each PM is broken down into intervals such as interval 1, interval 2 etc. So, for example, PM-A / Interval 1 might be items that occur every 3000 miles, PM-B might be items that occur every 6000 miles etc. This arrangement is represented by a treeview and it’s nodes:

PM-A
l
l
l---Interval 1
l
l---Interval 2
l
PM-B
l
l
l---Interval 1
l
l---Interval 2

While entering data in a form relative to a car, I can call the treeview. What I need to understand is how to associate or ’assign’ the value of a node, say interval 1, to that car. I would like to dblclick the node and have that value placed into the record of the car. Could someone assist.

Thank you

Directorz

 

[PHV]

Some suggestions.
1) In the FrmVehicles module window, declare a public variable and assign to it the choosen value:
Option Compare Database
Public unitID As Long
Private Sub Command4_Click()
...
unitID = Me!ID
DoCmd.OpenForm stDocName, , , stLinkCriteria

2) In the treeview0 module window, play with the above variable:
Private Sub TreeView0_DblClick()
...
Dim unitID As Long
...
If (Left$(nod.Key, 5) = "Code=") Then
unitID = Forms!FrmVehicles.unitID
MsgBox unitID & "," & nod.Parent & "," & nod
DoCmd.RunSQL "Insert Into PMSchedule (UnitID, PMName, PMInterval) Values (" & unitID & ",'" & nod.Parent & "','" & nod & "')"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[directorz]

PHV,
I'm a better mechanic than a coder. I believe I've modified code from FancyPrairie by swapping out field and table names. Will you do an upload

Directorz

 

[PHV]

My suggestions are based on an upload made today at 12:31:00 GMT.
With this amendments the treeview0 form populate the PMSchedule table with the current vehicle and the data from the treeview.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Oops,confused between upload and download :~/
Done at 14:32 GMT

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

I finally got a chance to download your new version of the database. Looks like PHV got you setup. I had a little spare time so decided to show you what I meant by using a subform. I uploaded the new version of the database for your review. So take a look at. It just seemed to me that you would want to see what PMs were scheduled for each vehicle. The subform provides this method. Consequently, you can either use the subform to enter your PM intervals or use your TreeView. If you use the TreeView, the subform will automatically be updated. The stuff I'm doing runs pretty quickly. However, if you have many PM intervals it might slow down. Try it and see what you think.

This is what it does.

1. In the OnOpen event of frmVehicles, form TreeView0 is also opened. (Don't need the button "Add PM" anymore.)

2. The OnCurrent event of frmVehicles loops thru the subform to determine which "Categories" have already been chosen. Since only one interval per category is allowed, I disable the category within the treeview so that the user can not select another interval from the same category. This is done by calling the new funcion TreeView0_Enable (this is within the module tab).

Note that it doesn't really disable/enable the Category, but rather changes the forecolor of the category. In the OnDoubleClick event, if the forecolor is grey, then the OnDoubleClick event exits without doing anything. However, if the forecolor is not grey, then the interval is added to the table and the subform is requeried to show the addition.

3. The AfterUpdate event of cboPMName (located in the subform) requeries the combobox cboPMInterval so that cboPMInterval only displays the Intervals assigned to the category selected in cboPMName.

NOTE that I changed the names of the Intervals (added the category name to the end of them). This was done just so that you could see that indeed the correct intervals were showing up (they will need to be removed). Also note that the OnCurrent event of the subform calls the AfterUpdate event of cboPMName to make sure everything stays in sync.

4. Finally, I noticed that your intervals were not always shown in the correct order. So I modified query1 and query3 to sort correctly.

I did not grey out the Intervals on the Treeview because it would take me another 15 minutes and I don't have time. Also, the user can enter a duplicate PM via the combobox. Didn't have time to deal with that hole either.

Let me know what you think.

 

[directorz]

FancyPrairie,
My DSL has been down so I have not been able to reply. What can I say...ingenius! This is incredible. Decisions, decisions, treeview or combo. I love the treeview and have tried so long to get it and spent way too many hours. But, as you said, the combo arrangemnent is neat! I'll probably spend too many hours making a decision. The grey out idea is fantastic. I do have a question relative to the queries..what does 'QLKP' stand for?
PHV - you know your observations are welcome and appreciated. Thank you so much!

I may have some followup as I incorporate the creation

Directorz

 

[FancyPrairie]

qlkp" stands for "Query (Lookup)". "qlkp" is just a prefix and follows the Reddick VBA Naming convention (Hungarian). Some people use it, some don't, some use a variation of it. As you can see from the example, you can use both the treeview and subform.

 

[directorz]

FancyPrairie,
All is OK, here’s an after thought observation. Each PM has several intervals to choose from and we are able to assign a PM / interval to each car. Because each car can have only 1 interval per PM assigned,at which time it becomes 'disabled', I modified values to read

PM-1(Hours) 10
PM-2(Hours) 50
PM-3(Hours) 100
PM-4(Hours) 250
PM-5(Hours) 500
PM-6(Hours) 1000
PM-7(Hours) 2000
PM-8(Miles) 3000
PM-9(Miles) 5000
PM-10(Miles) 10000
PM-11(Miles) 15000 etc, etc

And this arrangement is fine. The afterthought is this. Rather than disabling the PM after it has been used, how difficult is it to disable the selected node, thus allowing multiple intervals within a PM to be used. I know I had indicated earlier that the limit would be one interval per PM. I guess it’s six of one and half the other.

Directorz

 

[FancyPrairie]

It's not any more difficult. I uploaded the changes. Note the change in the primary key of table PMSchedule. Not the best way to design a table, but you can see how the code works.

 

[directorz]

The scheme is a bit out of the norm, but each field needs to be a pk in order to collectively allow for unique entries. Does this present any performance problems as compared to the first method.

 

[directorz]

FancyPrairie,
Take a look at Query 3 sorting."Order by...PMinterval.PMInterval..." If just numbers (actual numbers)as opposed to alphas (words like interval)are used in the tv / intervals nodes like 10, 50, 100, 250 etc. they appear as:
10
100
1000
2000
250
50
500
<Instead of>
10
50
100
250
1000
2000
Even though the field is set for sort ascending. Similar to the Y2K thing.

 

[FancyPrairie]

If the field type is defined as text, then it going to sort each char. from left to right. So, 200 will come before 50, because 2 comes before 5. However, it it's numeric then they will sort correctly (i.e. 50 comes before 200).

Quite frankly, you don't have your tables defined in the most efficient way. You have a lot of redundancy. You might want to look at using the AutoNumber datatype in your tables.

 

[directorz]

FancyPrairie,
Points well taken. I modified the interval field to numeric and it works fine. 'ID' was changed to autonumber and tblpmintervaltolerance was eliminated (really was not necessary) so things are cleaned up. The 'add Pm' button however I am going to need. When the tv is called by it, the 'disabled' nodes don't show up as disabled for the current car. What coding do I need to allow this. Seems like it should be in an on load event for the tv...maybe requery tblpmschedule.

Thank you,
Directorz

 

[FancyPrairie]

Right, put the code in the OnLoad event after the end of the loop that builds the tree. Something like this:

Do Until rstPMS.EOF
...
Loop

TreeView0_Enable

 

[directorz]

FancyPrairie,
I do not see where to put this

 

[FancyPrairie]

Put it in the OnLoad event of the Treeview0 form.

 

[directorz]

FancyPrairie
I must not understand. In the On Load event of the TV, I'm attempting to requery the form with:
...
rstINTERVALS.MoveNext
If rstINTERVALS.EOF Then Exit Do
Forms!FrmVehicles.Requery
Loop
End If
Where am I erroring

 

[FancyPrairie]

Assuming tv stands for form TreeView0, then the OnLoad event should look like this (based on the uploaded file).

Private Sub Form_Load()

'********************************
'* Declaration Specifications *
'********************************

Dim nodCat As Node 'Node for Category
Dim nodCatCode As Node 'Node for Category Code

Dim i As Integer 'Working variable

Dim strKey As String 'Unique ID (key) for the node
Dim strPMInterval As String 'Title (name shown on tree)

Dim rstPMS As New ADODB.Recordset
Dim rstINTERVALS As New ADODB.Recordset

'***********************************
'* Open the necessary recordsets *
'***********************************

On Error GoTo ErrHandler

rstPMS.Open "query1", CurrentProject.Connection
rstINTERVALS.Open "query3", CurrentProject.Connection

'*********************************
'* Loop through the categories *
'*********************************

i = 0

Do Until rstPMS.EOF

'**********************************************
'* Build Category Node (i.e. A-PM, B-PM...) *
'**********************************************

strKey = "Category=" & CStr(rstPMS!CategoryCode) 'Uniquely identifies the node
strPMInterval = rstPMS!Description 'Title shown up on Tree View

Set nodCat = TreeView0.Nodes.Add(, , strKey, strPMInterval, 1)
nodCat.ExpandedImage = 1

'******************************************************************
'* Loop to build Interval Node (i.e. Interval 1, Interval 2...) *
'******************************************************************

If Not rstINTERVALS.EOF Then

Do While rstINTERVALS!CategoryCode = rstPMS!CategoryCode

i = i + 1
strKey = "Code=" & rstINTERVALS!PMInterval & "_" & i 'Uniquely identifies the node
strPMInterval = rstINTERVALS!PMInterval 'Title shown up on the tree

Set nodCatCode = TreeView0.Nodes.Add(nodCat, 4, strKey, strPMInterval, 1) 'Could use tvwChild instead of 4 if reference is made

rstINTERVALS.MoveNext

If rstINTERVALS.EOF Then Exit Do


Loop
End If

rstPMS.MoveNext
Loop

TreeView0_Enable

'********************
'* Exit Procedure *
'********************

ExitProcedure:

Exit Sub

'****************************
'* Error Recovery Section *
'****************************

ErrHandler:

MsgBox "(Form_Load) Err.Number = " & Err.Number & vbCrLf & Err.Description
Resume ExitProcedure

End Sub

 

[directorz]

Must be my error. The sample works fine but my app does not. I will re-trace my steps.
Thank you

 

[directorz]

FancyPrairie,
I found the error. In the module for TreeView0 I had made an error when adapting the code for my app. All is well so far..

If (rst!PMName = strCategory) Then
instead of
If (rstPMInterval = strCategory) Then

Directorz