Form_TreeView_BOM |
Post Reply |
Author | |
A.D. Tejpal
Microsoft MVP Joined: 30 Jun 2008 Status: Offline Points: 192 |
Post Options
Thanks(0)
Posted: 19 Jul 2010 at 4:34am |
Form_TreeView_BOM Updates:
30-Aug-2010: SQL mode incorporated - for extracting product chain. 04-Mar-2012: Subroutine P_ExtractProductChainByCode in general module modified so as to identify base parts in first iteration itself. Explanatory Notes This sample db demonstrates extraction of complete hierarchical chain of sub-assemblies and components for the selected item. The user can select the desired item either by selection via combo box or by using the navigation buttons on the master subform at top left, bound to the master table T_PartMaster. Fields PartName and UnitPrice are available for user interaction (i.e. editing or new entry). UnitPrice is to be entered only for base parts (items which are not meant to have any child assemblies or components). There is no limit to the number of nested levels permitted. Two alternative modes for extraction of product chain are demonstrated, i.e. either via SQL or via code.
Traditional approach using SQL for extracting product chain is known to suffer from certain limitations as follows: Adoption of a novel technique in this sample db, involving cascaded execution of SQL, has not only overcome all the limitations mentioned in previous para, but also ensured normalized output, with no limit as to the number of nested levels permitted. This approach represents a major breakthrough and is expected to provide significant gain in performance for large data sets. For the selected item, complete hierarchical chain of sub-assemblies and components gets displayed in the lower subform at right. For base parts, total quantity as well as total price are also shown in respective columns. Summary information (like maximum nesting levels, number of distinct assemblies and base parts as well as total cost of base parts needed for the selected item) gets depicted in label caption at top of this subform. For convenient analysis, the user can select any of the following styles of display, via option group below this subform: Simultaneously, the hierarchical chain of sub-assemblies and components gets depicted as tree view. Total cost of base parts (i.e. items which have no child assemblies or components) also gets depicted at bottom of tree view. Contents of tree view remain in step with the latest selection (out of five listed above) in option group. Also, there is two way synchronization between tree view and the lower subform at right. If the user selects any node in tree view, corresponding record in subform becomes the current record. Conversely, if the user navigates to any record in the subform, corresponding node in tree view assumes selected state. PartID of matching record in the subform gets highlighted in a manner similar to the shade signifying selected node in treeview. As the user navigates through master subform at top left, the contents of tree view as well as the two subforms at right keep getting updated as per current selection (treating current PartID on master subform as the top item for which hierarchical chain is to be extracted). The upper subform at right enables the user to build new assemblies or edit the contents of existing ones. The three editable fields are: ParentPartID, PartID and Qty. Depending upon user preference, as per selection in the option group below this subform, contents of junction table get displayed in one of the following styles: Certain features as listed below have been incorporated as a help towards proper data entry or editing in the above subform: Note: Version: Access 2000 file format. [Special Note Of Thanks to Arthur Fuller, Drew Wutka, Jim Dettman, Rocky Smolin and Shamil Salakhetdinov (mentioned in alphabetical order) for the valuable inputs kindly provided by them in AccessD discussion group (Database Advisors)] DownLoad File >>: uploads/37/Form_TreeView_BOM.zip
|
|
Sponsored Links | Want a good read? Try The Summer of His Life (available on Amazon!) |
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |