• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1172
  • Last Modified:

Recursive Module (for BOM)

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

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

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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Lasers07Author Commented:
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!
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.
Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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