Call a VBA Function from an Access Form Update Event
Question details
Calculate an Overall Grade from Quantitative Grade and Qualitative Grade by calling a VBA function from an Access form.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A form needs to calculate an overall grade using two grade controls when a record is updated.
- Observed behavior
- The user wants the function to return the higher grade before the record is saved, or to display the calculation without storing it.
Open the form in Design View and confirm the names of the three controls on the Property Sheet. Make sure MaxOfList is an accessible VBA function; it is not a built-in Access function.
Calculate the Overall Grade in the form's Before Update event
Use the form's Before Update event when the calculated result must be saved to a bound field with the record.
In Access, right-click the form in the Navigation Pane and select Design View. Select the form using the square at the upper-left corner, then open Property Sheet > Event.
Next to Before Update, select [Event Procedure] and click the ellipsis button to open the VBA editor.
Add this line inside Form_BeforeUpdate: Me.[Overall Grade] = MaxOfList(Me.[Quantitative Grade], Me.[Qualitative Grade])
Save the form, switch to Form View, change the grade controls, and save the record. Confirm that Overall Grade is bound to the intended field and contains the expected result.

Display the grade in an unbound calculated control
Use a calculated control when users only need to see the result and do not need to store it in a table.
Use WPS Office for grade spreadsheets and other office files
WPS Office is a free, lightweight alternative for working with documents and spreadsheets. Its familiar interface and Microsoft Office format support make it easy to move compatible files. It does not run Access forms or fix Access VBA events.

Frequently Asked Questions
Why use the form's Before Update event instead of After Update?
Before Update runs before Access writes the record, so the assignment can be included in the save. The form's After Update event runs after the record has already been written.
Is MaxOfList built into Access?
No. Define MaxOfList in accessible VBA code, such as a public function in a standard module, before calling it from the form or a calculated control.
Should I store the calculated overall grade in a table?
Usually, a calculated value is better displayed in a query or unbound form control. Storing it can create inconsistent data if the source grades change without recalculating the result.
What happens if one of the grade controls is blank?
That depends on how MaxOfList handles Null values. Check the function's code and test records with one or both grades blank before relying on the result.




