[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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

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
cymru_cowboy
Asked:
cymru_cowboy
  • 4
  • 2
1 Solution
 
harfangCommented:
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

Private Function AddComponents( _
    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 + ")"
       
    With New ADODB.Recordset
        .Open strSQL, CurrentProject.Connection, adOpenStatic
       
        ' build list
        Do Until .EOF
            pvarList = pvarList + "," & "'" & !Component & "'"
            .MoveNext
        Loop
       
        ' recursive call
        If .RecordCount Then .MoveFirst
        Do Until .EOF
            AddComponents !Component, pvarList
            .MoveNext
        Loop
       
        .Close
    End With

    AddComponents = pvarList

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]"
       
    With New ADODB.Recordset
        .Open strSQL, CurrentProject.Connection, adOpenStatic
       
        ' 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
    Components = AddComponents(PartNumber)
    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
 
harfangCommented:
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
 
cymru_cowboyAuthor Commented:
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
.Open strSQL, CurrentProject.Connection, adOpenStatic
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
harfangCommented:
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
 
cymru_cowboyAuthor Commented:
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
 
harfangCommented:
I'm glad you found it! And success with that treeview!
(°v°)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now