Others

How to Set Product Prices by Customer Price Level in Microsoft Access

Rana GarciaRana Garcia Nov 2, 2026 871 views

Question details

The user needs to configure an Access database to dynamically filter and display item prices based on a specific customer's assigned price level.

How to Set Product Prices by Customer Price Level in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Creating an invoice or order form where product prices automatically adjust depending on the selected customer's price tier.
Observed behavior
To achieve this, the item combo box must join the items and price list tables, filtering prices according to the customer's price level selection without returning undefined-function errors.
Before you start

Ensure your Access database has existing tables for Customers (including a Price Level ID), Items, and a PriceList that maps items to various price levels. Back up your database before modifying form VBA codes or RowSource queries.

Solution 1Recommended

Configure Combo Box RowSource and After Update Event

Use a custom RowSource query to filter items by the customer's price level and VBA code to populate the price text box.

By joining the Items and PriceList tables in the item combo box's RowSource, you can filter the available pricing based on the customer selected in the main invoice form. Passing the correct column index in VBA will automatically fill in the price.

1
Set up the Main Form and Subform

Ensure your customer selection combo box is located on the main invoice form, and your invoice line items (where the product is selected) are placed within a subform.

2
Modify the Item Combo Box RowSource

Open the subform in Design View. Select the Item combo box, navigate to the Property Sheet's 'Data' tab, and click the ellipsis next to 'Row Source' to open the query builder. Add both the Items table and the PriceList table, creating a join between them on the ItemID.

3
Add the Filter Expression

In the query builder, set the criteria for the `PriceList.PriceLevelID` field to reference the customer's price level from the main form. Use the correct syntax: `=Forms!MainFormName!CustomerComboBoxName.Column(X)`. Replace 'MainFormName' and 'CustomerComboBoxName' with your actual control names.

4
Assign Price Using VBA

Close the query builder and save the RowSource. With the item combo box still selected, go to the 'Event' tab in the Property Sheet. Click the ellipsis next to 'After Update' and open the Code Builder. Enter the following VBA code: `Me.txtPrice = Me.cboItem.Column(2)` (assuming your price field is the third column in the RowSource query).

Configure Combo Box RowSource and After Update Event
Zero-Based Column Indexing: In Microsoft Access VBA and control expressions, the `.Column()` property is zero-based. This means `.Column(2)` actually refers to the third column in your combo box's RowSource query.
Free Microsoft Office alternative

Manage Dynamic Pricing Easily with WPS Office

While Microsoft Access is a powerful tool for complex relational databases, many pricing models and invoice tasks can be managed far more easily using a spreadsheet. WPS Office provides a free, lightweight, and highly capable alternative to Microsoft Office, allowing you to build automated pricing calculators using simple formulas.

  1. 1. Set Up Your Data Sheets: Open WPS Spreadsheet and create separate sheets for 'Customers' (with their pricing tiers) and 'Price Lists' (detailing item costs per tier).
  2. 2. Create Drop-down Menus: On your Invoice sheet, go to the 'Data' tab and use 'Data Validation' to create easily selectable drop-down lists for your Customers and Items.
  3. 3. Automate Pricing with VLOOKUP: Use the `=VLOOKUP()` function to automatically pull the correct item price from your Price Lists sheet based on the customer's selected pricing tier, instantly creating an automated invoice without VBA coding.
100% compatible with Microsoft Excel (.xlsx) formats for easy data migration.Manage dynamic customer pricing effortlessly using VLOOKUP or INDEX/MATCH functions.Free, lightweight, and features a familiar interface identical to what you already know.Includes a built-in PDF toolkit for generating and sending professional customer invoices.
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an 'undefined-function' error when referencing a combo box column?

This error typically occurs when the expression syntax in the RowSource or Control Source is incorrectly formatted. Ensure you are using the exact format `=Forms!YourFormName!YourControlName.Column(X)`. Additionally, the form being referenced must be actively open when the query runs.

How do I ensure the subform updates when I change the customer?

If you change the customer on the main form, the item combo boxes in the subform won't automatically refresh their available price levels. You must add a `Me.YourSubformControlName.Requery` command to the 'After Update' event of the customer combo box on the main form.

Can I automatically pull the price without using VBA macros?

Yes. Instead of using VBA in the 'After Update' event, you can set the Control Source of your price text box to `=cboItem.Column(2)`. However, this will only display the price; it won't permanently store the price in the invoice record table, meaning historical invoices could change if you update your core pricing list later.