Automatically Fill Description and Price from a Product Code in Access
Question details
The user needs an Access form to display a product description and price after a product code is entered.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A form uses a product code to retrieve information stored in separate related tables.
- Observed behavior
- The goal is for the matching description and price to appear automatically when the user selects or enters a product code.
Identify the product code fields and the tables containing descriptions and prices. Check that the matching code fields have compatible data types and that the query can return the intended description and price for each code.
Use a Combo Box and Query to Display Product Details
Use this approach to select or type a product code and display its matching description and price on the form.
The description and price controls in this example display lookup values; they do not save copies of those values in the form's underlying table.
Open Database Tools > Relationships. Add the relevant tables and link their matching product code fields, using the appropriate keys for your database.
Select Create > Query Design, add the product code, description, and price tables, and verify their joins. Add the product code, description, and price fields to the query in that order, then save it.
Open the form in Design View and add a combo box. In its Property Sheet, set Name to cboProduct, Row Source to the saved query, Column Count to 3, and Bound Column to 1. If the form stores a product code, set Control Source to that field.
Add two text boxes in Design View. Set the description text box's Control Source to =[cboProduct].Column(1) and the price text box's Control Source to =[cboProduct].Column(2). Set the price text box's Format property to Currency if appropriate.
Switch to Form View, select a product code from cboProduct, and confirm that the matching description and price appear. You can also type a code into the combo box and select the matching entry.

Work with Product Lists in WPS Office
WPS Office is a free, lightweight alternative for everyday document and spreadsheet work, with a familiar interface and support for common Microsoft Office formats. Move product lists in Excel format into WPS Spreadsheets for editing; WPS Office does not fix or replace an Access database form.

Frequently Asked Questions
Why are the description and price blank after I select a code?
Open the combo box's Row Source query in Query Design and check its joins and results. Also verify that the query columns are ordered as product code, description, and price, and that the text boxes refer to cboProduct.Column(1) and cboProduct.Column(2).
Can I type a product code instead of choosing it from the list?
Yes. In Form View, type into the combo box and select the matching entry. In Design View, check the combo box's Auto Expand property if you want Access to suggest matching entries as you type.
Will the displayed price be saved with the order?
Not with the display-only text box in this example. To retain the price at the time of an order, save it in a field in the order record and populate that field when the product is selected.
What if the price table contains several prices for one product?
Adjust the Row Source query in Query Design so it returns the intended price, such as the current price, for each product code. Otherwise, the combo box may show multiple entries for the same product.




