Solved

# Possibly Use Treeview control, Recursive Algorithm not sure???

Posted on 2006-04-10
391 Views
Hi guys,
I'm producing a database for our service department which I thought I'd wrapped up but now I've been asked that in the faults section the engineers can see a list of parts for the units that they are working on and base the service reports around these.

So I initially had to deal with some Excel spreadsheets and have managed to populate a table from all the BOM's and pull this into Access.

So I now have a table that looks like the following

Assembly             Component
100-109               100-108
100-109               100-045
100-109               100-068
100-109               100-268
100-108               100-072
100-108               100-357

You get the idea - that a component can also be an assembly.

I also have a master parts list table which has
Part Number        Part Description         Type(ie finished, sub assembly, componen)

Now the form I've designed, the engineer opens and it shows a unit waiting to be fixed with its part number. What I want is for him to be presented with all the parts for that item (including all the sub levels). Be able to select the faulty part (of which there may be multiple) and write a brief description.
The faults found table is set up
UnitFaultsPK(autonumber)       Unit Serial Number, Service Number, Fault code(that will be the selected part number), Description

So what I was originally looking for was a recursive algorithm to populate a drop down list. Then I got to thinking a treeview or similar of some sort would be far easier for the engineer as there could be a lot of parts.

If anybody has any idea how to populate the treeview with a recursive algorithm (or tell me a better way off going about it) and in Access create the link to the partnumber I'd be a very grateful man.

Cheers

0
Question by:cymru_cowboy

LVL 58

Accepted Solution

Hello,

Seems this question has been overlooked...

The general idea of recursion is good, so is the treeview control (which isn't truely recursive). But let's start with the basics. You can get lists of components with a query like this:

SELECT DISTINCTROW Z.[Part Number], A.Component
FROM
tblWhichHas AS Z
LEFT JOIN tblThatLooksLike AS A
ON Z.[Part Number] = A.Assembly

UNION
SELECT DISTINCTROW Z.[Part Number], B.Component
FROM (
tblWhichHas AS Z
INNER JOIN tblThatLooksLike AS A
ON Z.[Part Number] = A.Assembly)
INNER JOIN tblThatLooksLike AS B
ON A.Component = B.Assembly
UNION

SELECT DISTINCTROW Z.[Part Number], C.Component
FROM ((
tblWhichHas AS Z
INNER JOIN tblThatLooksLike AS A
ON Z.[Part Number] = A.Assembly)
INNER JOIN tblThatLooksLike AS B
ON A.Component = B.Assembly)
INNER JOIN tblThatLooksLike AS C
ON B.Component = C.Assembly

This one is only three levels deep, each new level adds complexity and reduces performance. However, the links are clean, and this could suit your needs.

Now the "recursive" idea. The full module below will create lists suitable for list boxes and as WHERE criteria (examples in the module).

---------------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

ByVal pstrPart As String, _
Optional ByRef pvarList = Null)

' Add new components of pstrPart to the list
' Uses: tblThatLooksLike

Dim strSQL As String
Dim r As DAO.Recordset

strSQL _
= "SELECT Component" _
& " FROM tblThatLooksLike" _
& " WHERE Assembly = '" & pstrPart & "'" _
& " AND Component Not In(" + pvarList + ")"

' build list
Do Until .EOF
pvarList = pvarList + "," & "'" & !Component & "'"
.MoveNext
Loop

' recursive call
If .RecordCount Then .MoveFirst
Do Until .EOF
.MoveNext
Loop

.Close
End With

End Function

Private Function SortParts(pvarList) As String

' Sorts a list of parts
' Uses: tblWhichHas

Dim strSQL As String
Dim r As DAO.Recordset

strSQL _
= "SELECT [Part Number] As PN" _
& " FROM tblWhichHas" _
& " WHERE [Part Number] In(" & pvarList & ")" _
& " ORDER BY [Part Number]"

' build the new sorted list
pvarList = Null
Do Until .EOF
pvarList = pvarList + "," & "'" & !PN & "'"
.MoveNext
Loop

.Close
End With

SortParts = pvarList

End Function

Function Components(PartNumber, Optional Sort As Boolean = False)
'
' This builds a comma-separated list of components for the passed
' part number. The builder function recurses to obtain sub-components
' at an arbitrary depth.
'
If IsNull(PartNumber) Then Components = Null: Exit Function
If Not IsNull(Components) And Sort Then _
SortParts Components

End Function
---------------------------------------------------------------------------------------------------------

Now you can have on a form something like for your form's current event:

Private Sub Form_Current()
Me.lstComponents.Value = Null
Me.lstComponents.RowSource = Nz(Components(Me.Part_Number, True))
End Sub

Well, that's basically it.
Good luck!
(°v°)
0

LVL 58

Expert Comment

PS:

1) lstComponents is a list box with RowSourceType: Value List

2) If you want to include the "current" part number in the list box (why not?), replace this line in Components():

Components = AddComponents(PartNumber, "'" & PartNumber & "'")

And add this to Form_Current() to highlight the "main" part number.

Me.lstComponents.Value = Me.Part_Number

Cheers!
(°v°)
0

Author Comment

Hi thanks for the help,
thought I'd got lost in the wildeness with this one. I've gone for the recursive option but I've got a slight snag at the moment. The call
in the addcomponents function throws up the error "No value given for one or more required paramaters" on the first recursion (ie 2nd time the call is made).
I did a watch on all the values and it seems its the currentproject.connection that becomes out of context on the 2nd run through.

0

LVL 58

Expert Comment

Hi,

When you get that error message, do this:
* debug the code
* switch to immediate pane
* ? strSQL
* copy the query, paste into a new blank query and test there.

When run as a regular query, you will have more explicit messages.

By the way, we know the problem does not come from the connection obect, given that it readily tries to open your recordset, albeit with an error. Debug strSQL first.

Cheers!
(°v°)
0

Author Comment

Hey cheers,
It was a typo in the sql query. Excuse my blushes. Thanks for the help. Got a list view all populated, going to try and get the info into a treeview now.

Thanks again

Kieron
0

LVL 58

Expert Comment

I'm glad you found it! And success with that treeview!
(°v°)
0

## Featured Post

This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…