Bill of Materials Code Questions

[SALPBE]

I am new to the group and to Access, but have been reading through related materials and getting sample code to pull together a Bill of Materials (BOM) from an ODBC data source (Sage MAS90 DB). I found a BOM.ZIP example from which I was able to modify to get the correct information from my sample database.

What I am having problems with is some basics.

1. Passing more than one parameter from a combo box.
2. Dealing with NULL values in the data.
3. How to place more information into the Array.

1.
The sample DB uses a Form to collect information. It originally collected only ONE column of information, but I need to pass TWO items to the Module that assembles the BOM. (The second piece of info is the CurrentRevision of the Assembly)

I have the following code for the Event Procedure:

Option Compare Database
Option Explicit
Private Sub Combo0_Click()
Dim strAssembly As String
Dim intCurrentRev As String
strAssembly = Combo0.Column(0)
intCurrentRev = Combo0.Column(1)
BOMHost (strAssembly) <============
End Sub

This works as written (passing strAssembly), but I don't understand how to add the second item intCurrentRev to the BOMHost call.

Also If and when the parameter is passed to BOMHost, how can I get the information to the Sub so that I can limit the SELECT statement to only the CurrentRevision items.

Thinking something like:

Set rs1 = db1.OpenRecordset("Select * from BM2_BillMaterialsDetail Where " _
& "(((BM2_BillMaterialsDetail.BillNumber)=" & Qu & strBillNumber & Qu & ")AND ((BM2_BillMaterialsDetail.Revision) = " & Qu & intCurrentRev & Qu & "))", DB_OPEN_DYNASET)

I have attached the code file.

2.


In the table that comes from MAS90 (Read Only), there are /C codes in separate columns associated with the BillNumber. For these rows, there are NULL values for the ComponentItemCode and QtyPerBill fields. Since I can't change the information in the table, I need to pass back the /C codes from the BM2_BillMaterialsDetail.C column and not error out because of the NULL values.

3.
Idealy, my OutPutTable should have the Assembly, SubAssembly, and component information. I just need an example of how to increase the amount of information passed into the OutPutTable.

If I have posted incorrectly, please let me know.

Thanks

[RuralGuy]

Without looking at your db, you will need to modify the BOMHost() procedure to accept the second value to do as you are asking.

[orange]

There are some data models for Gill of Materials here that may be useful

http://www.databaseanswers.org/data_.../bom/index.htm conceptual
http://www.databaseanswers.org/data_...ysical_bom.htm physical


Sample BOM for a paper warehouse
http://www.databaseanswers.org/data_..._warehouse.htm

[SALPBE]

Thanks for the responses. I have been looking at the links that "orange" posted, but don't really have an issue with the concept of a BOM.

RuralGuy. You mention that I need to modify the BOMHost() procedure. This is one of my questions. I know I need to modify, but I don't know the correct format.

Does anyone have a "Specific" resource that would get me going in the right direction?

I appreciate all the help I can get.

[SALPBE]

I was able to get past the issue of the procedure not passing the information by removing the () from the call.

Private Sub Combo0_Click()
Dim strAssembly As String
Dim strCurrentRevision As String

strAssembly = Combo0.Column(0)
strCurrentRevision = Combo0.Column(1)

BOMHost strAssembly, strCurrentRevision
' Original call BOMHost (strAssembly, strCurrentRevision)

End Sub


I still need assistance with the other 2 questions on my original post if anyone has any ideas.