Using MS Access/MS Access


I have a table with an associated form listing all of our raw materials that we have in house (sometimes multiple lots of materials)

I have a table with an associated form defining the components (raw materials) of all of our products

I have a “Build” table with which we would use a form to document the lot number of the product, the product line, batch size, etc.

The Build form has a subform which details the build in terms of raw material lots numbers used.

What I need is for the subform to automatically populate with raw materials associated with the product being built once it is selected in the main form. Then I can set it up so users can click a dropdown box for each material and document which lot number was used.

The whole auto-population thing is the crux. If there’s 10 components to the product, I want to see 10 items listed in the subform as soon as I choose that product.

Note: This is not the same as a cascading drop box. I want the list to populate to avoid mistakes caused by forgetting about a component material.

Any idea how I could do this?  Thanks for any help!!!


Sure, its relatively simple. So you should have a table something like this:

ProductComponentID (PK Autonumber)
ProductID (FK)
ComponentID (FK)

This lists, for each product you make, the components used to make that product.

Now on your main form you have a button to add components. The main form is bound to a specific product being made. So after you select the ProductID you run code like the following (Use the After Update event of the ProductID combo):

Dim strSQL As String

strSQL = "INSERT INTO tablename (ProductID, ComponentID, PieceID) " & _
        "SELECT, ProductID, ComponentID, " & Me.PieceID & " AS PieceID " & _
        "FROM tblProductComponents " & _
        "WHERE ProductID = " & Me.cboProduct & ";"
CurrentDB.Execute strSQL

Where tablename is the name of the table the subform is bound to. PieceID is the PK of the piece you are actually making. And cboProduct the combo on the form where you select the product you are making.

This code will then add a record for each component you have identified as being part of the selected product.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.