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

Comment Utility
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

Comment Utility
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

Comment Utility
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.
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!


Author Comment

Comment Utility
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
Comment Utility
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

Comment Utility
Forced accept.

EE Admin

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now