Others

How to Link an Access Quote Form to a BOM Subform and Calculate Costs

Huma Ashraf Ch Sep 28, 2026 868 views

Question details

The user needs to connect a parent quoting form to a Bill of Materials (BOM) subform in Microsoft Access and calculate assembly costs based on multiple parameters.

How to Link an Access Quote Form to a BOM Subform and Calculate Costs

Product: Microsoft Access
Device & OS: not provided
Scenario: Building a quoting database where tube assemblies (BOM) must be linked to parent quotes, and total costs need to be calculated dynamically from various materials and parameters.
Observed behavior: The goal is to correctly establish a one-to-many relationship between the quote and BOM tables, link the subform, and accurately calculate costs while preventing data anomalies from future price changes.

Before you start

Ensure you have fully designed your database tables and defined the primary and foreign keys before building your forms, as establishing correct table relationships is the critical first step.

Solution 1Recommended

Establish Table Relationships and Link the Subform

Use a one-to-many relationship with a numeric primary key to connect your main quote form to the BOM subform.

Always design your tables and relationships first. Even if users see a formatted quote number, the relational link between tables should rely on a numeric primary key to ensure stability and efficiency.

Add a Primary Key

In your main quote table (e.g., tblTUBEQUOTES), add a numeric primary key named QuoteID.

Add a Foreign Key

In your BOM table (e.g., tblTUBEBOM), add a matching numeric field named QuoteID to act as the foreign key.

Enforce the Relationship

Open the Database Tools tab, click Relationships, and drag QuoteID from the quote table to the BOM table. Check the box to enforce referential integrity to create a one-to-many relationship.

Configure the Subform Properties

Open your main quote form in Design View. Select the BOM subform control, open the Property Sheet, and set both the 'Link Master Fields' and 'Link Child Fields' properties to QuoteID.

Establish Table Relationships and Link the Subform

Relationship Secured: By enforcing referential integrity, Access will automatically insert the correct QuoteID into new BOM records added via the subform.

Solution 2

Store Unit Costs and Calculate Totals Dynamically

Store cost values at the time of creation to prevent historical quotes from altering when future prices change, but calculate totals on the fly.

Free Microsoft Office alternative

Manage BOMs and Quotes Easily with WPS Spreadsheet

Building relational databases can be complex and time-consuming. If you need a more straightforward way to generate quotes and manage Bill of Materials, WPS Spreadsheet offers a powerful, free alternative to Microsoft Office. You can easily build automated quote calculators using familiar spreadsheet formulas.

1. Open a Quote Template: Launch WPS Spreadsheet and browse the extensive template library for ready-to-use quoting or BOM templates.
2. Create a Parts Database: Set up a separate sheet to list all your materials, tube assemblies, and their current unit prices.
3. Automate Pricing with Formulas: Use Data Validation to create drop-down lists for your parts, and VLOOKUP formulas to automatically pull the correct unit price into your quote.

Fully compatible with Microsoft Excel (.xlsx) formatsUse VLOOKUP and Data Validation for automated pricing calculationsLightweight, fast, and completely free to useBuilt-in professional invoice and quoting templates

microsoft office alternative - wps office

Frequently Asked Questions

Why should I store the unit price in the BOM table instead of just referencing the product table?

Costs change over time. By storing the unit price directly in the BOM table at the moment the quote is created, you freeze the price for that specific quote. If you only link to the product table, opening an old quote later will display the new price, altering your historical data incorrectly.

How do I populate the unit price automatically when a user selects a part in Access?

You can use an AfterUpdate event in VBA on your part-selection combo box. An expression like Me.UnitPrice = Me.cboProduct.Column(1) will fetch the value from the combo box's second column (since the column index is zero-based) and save it to the current record.

Is it bad practice to store the calculated line total in my database table?

Yes. Storing a calculated value (like UnitPrice multiplied by Quantity) creates data redundancy. If either the unit price or quantity is modified later and the total isn't explicitly updated via code, your data becomes inconsistent. Always calculate totals dynamically in your queries, forms, or reports.

Office Quick Solutions