How to Automatically Update Form Fees via Combo Box in Access
Question details
The user needs a Microsoft Access form to automatically populate first, second, and third-term fee fields when a specific year or class is selected from a combo box dropdown.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building a class or school management database where selecting a class automatically retrieves the correct term fees for the user.
- Observed behavior
- The user wants the term fees to display dynamically based on the dropdown selection, but needs to ensure that historical transaction records retain the original fee charged even if the base rates change later.
Ensure your combo box is already linked to a Row Source (a table or query) that includes both the class names and their corresponding term fees in its columns.
Use the After Update Event in VBA to Copy Fee Values
Use a simple VBA script triggered by the combo box's After Update event to pull fee data from the dropdown's columns and permanently store it in your form's transaction fields.
Because fee rates often change over time, it is crucial to copy the current fee directly into your transaction table rather than continually referencing the live fee table. This approach acts similar to storing a product's price at the time of an order, ensuring past records do not inadvertently change if you update next year's fees.
Microsoft Access combo box columns are zero-based. This means the first column is recognized as Column(0), the second is Column(1), and so forth.
Right-click your form in the navigation pane and select 'Design View'.
Click on the combo box used to select the class or year. Press F4 to open the Property Sheet.
Navigate to the 'Event' tab in the Property Sheet. Find the 'After Update' property, click the three dots (...) next to it, and select 'Code Builder' to open the VBA editor.
In the code module, assign the column values to your form fields. For example, type: Me.UnitPrice = Me.ItemID.Column(1). Adjust 'UnitPrice' and 'ItemID' to match your actual text box and combo box names.
Save the VBA code, close the editor, switch back to 'Form View', and select a class to verify the fees update correctly.

Configure the Combo Box Row Source Properties
Before the VBA code can correctly identify the fees, the combo box must be formatted to load the fee columns from your source table behind the scenes.
Manage Data and Automate Inputs Easily with WPS Office
If you are not experienced with Access macros or VBA, creating automated forms can be overwhelming. As a highly compatible alternative to Microsoft Office, WPS Spreadsheet allows you to build powerful, automated fee management trackers without writing any code. Enjoy a lightweight, familiar interface that simplifies your daily data tasks for free.
- 1. Create a Reference Table: In WPS Spreadsheet, set up a simple reference sheet containing your Class Names and their respective Term Fees.
- 2. Set Up a Dropdown List: Highlight your input cell, go to the 'Data' tab, and select 'Validation' to create a dropdown menu linked to your Class Names.
- 3. Automate Fees with VLOOKUP: In the fee cells, use the VLOOKUP function (e.g., =VLOOKUP(A2, FeeTable, 2, FALSE)) to automatically pull the correct term fees the moment a class is selected.

Frequently Asked Questions
Why is my combo box returning the wrong fee value in VBA?
Microsoft Access combo box columns are zero-based. This means Column(0) represents the first column, Column(1) is the second column, and so forth. If your code is pulling the wrong data, check your Row Source query and shift your column index down by one.
Should I link the fee text box directly to the combo box using a Control Source?
It is highly discouraged to set the Control Source of the fee text box directly to '=ComboName.Column(1)'. Doing so only displays the fee visually but does not save the actual value to your underlying transaction table. If fee rates change next year, all historical records will dynamically update to the new rate, breaking your accounting records. Always use the After Update event to copy the value.
How do I hide the fee data inside the combo box dropdown list?
You can hide columns by adjusting the 'Column Widths' property found in the combo box's Format tab. Setting the width of the fee columns to '0cm' or '0"' will keep the data loaded behind the scenes for VBA to utilize, while hiding it from the user interface.




