?
Solved

Recursive Module (for BOM)

Posted on 2007-12-03
7
Medium Priority
?
1,154 Views
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)
    rsBOM.MoveLast
    rsBOM.MoveFirst
        varMasterPN = rsBOM!ID
        
        Do Until rsBOM.EOF       
                xlsheet.Range("a" & int_cell).Value = !Bompartnumber '
               
                'call recursive module here
                
                rsBOM.MoveNext
                int_cell = int_cell + 1      
        Loop
Exit Sub
 
End Sub

Open in new window

0
Comment
Question by:Lasers07
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
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.
0
 
LVL 15

Expert Comment

by:JimFive
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)
wend

end sub

--
JimFive
0
 
LVL 61

Expert Comment

by:mbizup
ID: 20398207
Take a look at my post in this question:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22940403.html#20239390

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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

JimFive,
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

mbizup,
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!
0
 
LVL 15

Accepted Solution

by:
JimFive earned 2000 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.
--
JimFive
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21189585
Forced accept.

Computer101
EE Admin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

719 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