Access 2010 VBA Module to Explode Bill of Material

I need to explode the bills of material from our ERP.  I believe that I can use a VBA module to achieve this.  I found some code that may work, but am having difficulty getting a test to run.  I am including below the VBA code and the test data that I have in Access tables to test the VBA code.  When I enter the code in to a Module as either a subroutine or a function I cannot run it even if I try to invoke the function version with a Macro.  Any help would be appreciated.
Option Compare Database
Public Function EnumBOM(Optional llc As Long = 0, Optional PK As Long)

 Dim rst As Recordset
 Dim x As Long

 If llc Then
 Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
 & "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
 & "FROM (tblBOM INNER JOIN tblBOMDetail " _
 & "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
 & "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
 & "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
 & "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";")
 Else
 Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
 & "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
 & "FROM (tblBOM LEFT JOIN tblBOMDetail " _
 & "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
 & "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
 & "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
 & "WHERE tblBOMDetail.lngBOMDetailID Is Null;")
 End If

 Do While Not rst.EOF
 For x = 1 To llc
 Debug.Print vbTab;
 Next x
 Debug.Print rst!strPartNo; ": "; rst!strDescription
 If IsNull(rst!FK) = False Then EnumBOM llc + 1, rst!lngBOMID
 rst.MoveNext
 Loop

 rst.Close

 End Function

Open in new window

lngBOMID	strPartNo
AssyNo1   	Assembly No. 1
AssyNo2   	Assembly No. 2
Comp1   	Component 1
Comp2   	Component 2
Comp3   	Component 3
SubAssy1	Sub-Assembly 1
SubAssy2	Sub-Assembly 2
SubAssy3	Sub-Assembly 3

Open in new window

lngBOMDetailId	lngBOMID	intQuantity
1            	3            	1
7            	3            	1
1            	4            	1
2            	4            	1
7            	4            	1
2            	5            	1
6            	5            	1
8            	5            	1
1            	6            	1
2            	7            	1
7            	8            	1

Open in new window

DAWhiteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BitsqueezerCommented:
Hi,

please provide a demo database where the needed tables and your code and maybe needed queries are included. You should create it in format A2003 so the most experts can open it.

Cheers,

Christian
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
There are two ways of doing this:

1. With SQL
2. With VBA

 I prefer the VBA method for a couple of reasons.   With that in mind, give this a read through:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_19-BOM-parts-list-expansion.html

 Written by mbizup.

 Also check out the attached sample DB's (I've included the SQL solutions as well).   The VBA one is along the same lines as the code in mbizup's article, but doesn't do the explosion as a recursive call, which saves some overhead.  The table structures are slightly different as well.

Jim.
BOMSample.zip
NestedBOM.zip
NestedBOM-nico5038.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.