cymru_cowboy
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
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.
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,
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.
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°)
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°)
ASKER
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
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
I'm glad you found it! And success with that treeview!
(°v°)
(°v°)
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°)