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
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.