Recursive Module (for BOM)

Posted on 2007-12-03
Last Modified: 2008-03-23
I have some code here that I am working on to try to export table data in a BOM structure.  Not sure if I should export the data to excel, or into Word where I can indent things and it will look prettier.  Here is an example of what my recordset looks like:
ID      BOMID      BOMPartNumber      BOMDescription      BOMParentPartNumber
71      700-0001-001      700-0001-001      TOP ASSY, D1, 5W, 808NM      
72      700-0001-001      210-0001-001      CS MOUNT      71
73      700-0001-001      210-0004-001      HEAT SINK, D1      72
74      700-0001-001      315-0001-001      FIBER, 105/125/140, POLYIMIDE COATED      71
75      700-0001-001      320-0001-001      WINDOW SPACER      71
76      700-0001-001      321-0003-001      SCREW, SHCS, #2-56 X 3/16      73
77      700-0001-001      610-0001-001      SUB ASSY., D1, COATED SUB-MOUNT      71
78      700-0001-001      321-0005-001      SCREW, BHCS, #2-56 X 1/8      77
79      700-0001-001      327-0001-001      O-RING, LUG, 70 BUNA, -007      77
80      700-0001-001      371-0001-001      WIRE, PVC, AWG 22      75
81      700-0001-001      373-0001-001      RIBBON, GOLD, 0.002" X 0.024"      71

My code snippet is rather sloppy as I am trying to put this together quickly.  Im stuck and trying to create the module that will loop through the recordset and perform a tree view type of export.  Any suggestions??
Private Sub cmdExportBOM_Click()
Dim varBOMID As String
Dim varMasterPN As String
'excel code begin
Dim appExcel As New Excel.Application
Dim xlsheet As Worksheet
   appExcel.Visible = True
      FileCopy "p:\vic\empty.xls", "" & Environ$("UserProfile") & "\desktop\test.xls"
   appExcel.Workbooks.Open "" & Environ$("UserProfile") & "\desktop\test.xls"
   Set xlsheet = appExcel.ActiveSheet
   Dim int_cell As Integer
   int_cell = 1
'excel code End
varBOMID = Me.cmbBOM.Column(0)
Dim rsBOM As DAO.Recordset
Dim DB As DAO.Database
Dim strSQLBOM As String
Set DB = CurrentDb()
strSQLBOM = "SELECT tblBOMID.ID, tblBOMID.PartID, tblBOMID.BOMID, tblBOMID.BOMPartNumber, tblBOMID.BOMDescription, tblBOMID.BOMParentPartNumber, tblBOMID.BOMPartQty " _
& " FROM tblBOMID " _
& " WHERE (((tblBOMID.BOMID)=""" & varBOMID & """));"
Set rsBOM = DB.OpenRecordset(strSQLBOM, dbOpenDynaset)
        varMasterPN = rsBOM!ID
        Do Until rsBOM.EOF       
                xlsheet.Range("a" & int_cell).Value = !Bompartnumber '
                'call recursive module here
                int_cell = int_cell + 1      
Exit Sub
End Sub

Open in new window

Question by:Lasers07
LVL 76

Expert Comment

ID: 20397568
Is it necessary to know what BOM is?
If it's a flat (non-hierarchical) query, you could consider using Word mail merge.  Otherwise, perhaps you could use the Access report function.
LVL 15

Expert Comment

ID: 20398143
I think what you want to do is something like this:

First get the Primary part at line 27
SELECT ... WHERE tblBOMID.BOMID = """ & varBOMID & """ AND BOMParentPartNumber is null "

Then for your recursive bin at line 39 you'll want a function call that
uses another recordset, you'll want to pass the BOMParentPartNumber and an Indentlevel

Sub recurseBOM(BOMID, parentnumber, indentlevel)
strSelect = "SELECT ... WHERE tblBOMID.BOMID = """ & BOMID & """ AND BOMParentPartNumber = """ & rsBOM("BOMParentPartNumber") & """"

set rsRecurse = DBOpenRecordSet(strSelect, dbOpenDynaset)
if rsRecurse.BOF and rs.Recurse.EOF then
  exit sub
end if
While not rsrecurse.eof
   print out current info
   recurseBOM(rsRecurse.ParentPartNum, indentlevel+1)

end sub

LVL 61

Expert Comment

ID: 20398207
Take a look at my post in this question:

It describes how to set up your tables for such a function, and includes a sample db containing a function that loops through a Parts/assemblies table outputting an indentured (heirarchical) parts list to another table.
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

ID: 20399329
Graham... Not sure what you mean, sorry

Yes this is along the lines of what Im looking for.  Im not sure how to print the parts out into excel or word and have them indent properly.  then to make matters worse, how am I supposed to go back and insert lines where needed.  

Can you correct the line 27 select statement please.  I tried doing that earlier but was getting stumped on the null part.  That is how I was going to get the varMasterPN at line 34

Thanks for noticing everything.  I was using an older username and now its been cancelled.  I had to open another one with my new company.  I dont think your option will work because I since developed too much and im pretty much set on how its going now.  this treeview is pretty useless since it doesnt allow you to export it to any other formats!
LVL 15

Accepted Solution

JimFive earned 500 total points
ID: 20402563
For line 27 (Assuming that it is null and not an empty string):

strSQLBOM = "SELECT tblBOMID.ID, tblBOMID.PartID, tblBOMID.BOMID, tblBOMID.BOMPartNumber, tblBOMID.BOMDescription, tblBOMID.BOMParentPartNumber, tblBOMID.BOMPartQty " _
& " FROM tblBOMID " _
& " WHERE (((tblBOMID.BOMID)=""" & varBOMID & """)) AND tblBOMID.BOMParentPartNumber is NULL;"

To write into an excel cell you want to use:
ActiveWorksheet.Cell(Row, Column).Value = data
To do the indents just add the indentlevel to your base column number

When doing your recursion you write the header level, then recurse that level you shouldn't need to go back to insert rows because it should write them out in the proper order.

In my pseudocode above you may want to add the excel worksheet as a parameter into recurseBOM()
I would probably add a global variable to keep track of the next excel rownumber to write.

Expert Comment

ID: 21189585
Forced accept.

EE Admin

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question