Others

How to Automatically Update Form Fees via Combo Box in Access

John WilsonJohn Wilson Nov 3, 2026 869 views

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.

How to Automatically Update Fees When a Class is Selected in Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Open Form in Design View

Right-click your form in the navigation pane and select 'Design View'.

2
Access the Combo Box Properties

Click on the combo box used to select the class or year. Press F4 to open the Property Sheet.

3
Trigger the Code Builder

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.

4
Enter the VBA Code

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.

5
Save and Test

Save the VBA code, close the editor, switch back to 'Form View', and select a class to verify the fees update correctly.

Use the After Update Event in VBA to Copy Fee Values
Historical Integrity Maintained: By copying the value into a bound text box via VBA, the specific fee amount is saved permanently in your transaction record, protecting your historical accounting data.
Free Microsoft Office alternative

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. 1. Create a Reference Table: In WPS Spreadsheet, set up a simple reference sheet containing your Class Names and their respective Term Fees.
  2. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) formats, ensuring seamless data migration.Create dynamic dropdown lists and auto-fill data using intuitive VLOOKUP functions instead of complex VBA.Lightweight installation with a familiar, easy-to-use tabbed interface.Free to download and use for your daily office, accounting, and data management tasks.
microsoft office alternative - wps office

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.