How to Set Product Prices by Customer Price Level in Microsoft Access
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.

- 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.
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.
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.
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.
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.
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.
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).

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. 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. 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. 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.

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.




