Solved

Recursive Module (for BOM)

Posted on 2007-12-03
7
1,142 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 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.
--
JimFive
0
 
LVL 1

Expert Comment

by:Computer101
ID: 21189585
Forced accept.

Computer101
EE Admin
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

810 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