VBA & Macro Problems

How to Clear Text Boxes, Combo Boxes, and Option Groups in Access VBA

Ayan Masood Oct 12, 2026 869 views

Question details

The user needs a VBA method to reset or clear specific types of form controls simultaneously without triggering errors.

Clear Text Boxes, Combo Boxes, and Option Groups in Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Resetting a data entry form to its default or blank state after a submission or cancellation.
Observed behavior: The goal is to programmatically iterate through form controls and assign a Null value exclusively to controls that support it, such as text boxes, combo boxes, and option groups.

Before you start

Ensure you have saved your current form design and backed up your database before running new VBA macros to prevent accidental data loss or form corruption.

Solution 1Recommended

Use a Select Case Statement to Target Specific Controls

This method safely targets specific control types by checking their TypeName before assigning a Null value.

By utilizing the TypeName function, you can determine exactly what kind of control the loop is currently examining. This allows you to selectively apply the Null value only to TextBoxes, ComboBoxes, and OptionGroups, avoiding errors on unsupported controls like Labels or Command Buttons.

Open the VBA Editor

Open your Access Database, go to Form Design View, and press ALT + F11 to open the VBA Editor.

Insert the Select Case Loop

Write the following VBA code in your chosen event procedure (like a Reset button's OnClick event): Dim ctl As Control For Each ctl In Me.Controls Select Case TypeName(ctl) Case "TextBox", "ComboBox", "OptionGroup" ctl.Value = Null End Select Next ctl

Save and Test

Save your VBA code, return to your form in Form View, and trigger the event to see the target controls clear successfully.

Use a Select Case Statement to Target Specific Controls

Best Practice: This is the safest and most structured approach, as it clearly defines which controls are affected and makes future code maintenance much easier.

Solution 2

Clear Controls Using the Tag Property

Identify only the specific controls you want to clear by assigning a custom tag, useful when you have some inputs you want to keep filled.

Solution 3

Force Clear Using Error Handling (On Error Resume Next)

A quick brute-force method that attempts to assign Null to every control, ignoring the errors generated by controls that don't support Null.

Free Microsoft Office alternative

Need a Fast, Free Office Suite Alternative? Try WPS Office

While WPS Office doesn't feature a direct database application like MS Access, it provides a highly compatible, free, and lightweight suite for Word, Excel, and PowerPoint files. If you handle data exports from Access, WPS Spreadsheets is perfect for analyzing, visualizing, and formatting that data.

1. Download WPS Office: Visit the official WPS website and download the free installation package.
2. Export Access Data: In Access, export your tables or query results as an Excel (.xlsx) file.
3. Open in WPS Spreadsheets: Launch WPS Spreadsheets to easily open, analyze, and chart your exported database records.

100% compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint formats.Lightweight installation that runs smoothly on older hardware.Familiar ribbon interface for seamless migration from MS Office.Free to use with built-in PDF editing tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get an error when setting a control to Null in Access VBA?

Not all controls support the Null value. For instance, Labels, Command Buttons, and CheckBoxes bound to non-nullable fields will throw a runtime error if you attempt to assign Null to them. This is why filtering by TypeName or using tags is necessary.

Can I clear a ListBox using the same Select Case method?

Yes, you can easily add "ListBox" to your Case statement (e.g., Case "TextBox", "ComboBox", "OptionGroup", "ListBox"). Setting a ListBox to Null successfully clears the current user selection.

How do I clear controls only within a specific subform?

Instead of looping through Me.Controls (which targets the main form), you should loop through the controls of the subform container specifically. Use a loop like: For Each ctl In Me.YourSubformName.Form.Controls.

Is it better to set a TextBox to an empty string ("") or Null?

Generally, Null is preferred in database forms because it truly empties the field and represents an absence of data. An empty string ("") is evaluated as an actual text value of zero length, which can affect queries and database storage.

Excel & Data Problems