Others

How to Enter a Number in an Unbound Access Form Control

Rana Garcia Sep 29, 2026 869 views

Question details

The user needs to accept a numerical input in a Microsoft Access form control that is not bound to any underlying table field.

How to Enter a Number in an Unbound Access Form Control

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a form where temporary numerical data needs to be entered for calculations or VBA processing without saving it directly to a database table.
Observed behavior: The form requires an unbound text box control to accept numbers, which must then be retrieved and validated using VBA before further processing.

Before you start

Ensure your Access database form is open in Design View and that you have basic familiarity with the VBA editor to add validation and processing code.

Solution 1Recommended

Use an Unbound Text Box and VBA for Numerical Input

Add an unbound text box to your form to capture user input, and use VBA to safely validate and process the number.

Because the control is unbound, the value entered by the user will not be automatically saved to a database table. You must use VBA to retrieve, validate, and utilize the number for your intended calculations, parameterized queries, or condition checks.

Add an unbound text box

Open your form in Design View. Select the 'Text Box' tool from the Form Design ribbon and click on the form to place an unbound control.

Rename the control

Open the Property Sheet for the new text box, navigate to the 'Other' tab, and change the 'Name' property to something descriptive like 'txtNumber'.

Set the format

In the Property Sheet, switch to the 'Format' tab and set the 'Format' property to 'General Number' or 'Standard' so it displays numerical data properly.

Access the VBA editor

Add a Command Button to your form to process the input. Right-click the button, select 'Build Event', and choose 'Code Builder' to open the VBA editor.

Write the retrieval and validation code

In the VBA editor, write code to retrieve and convert the value. For example: `Dim userInput As Double` followed by `userInput = CDbl(Nz(Me.txtNumber.Value, 0))`.

Use an Unbound Text Box and VBA for Numerical Input

Data Validation: Always validate the input before conversion. Use the IsNumeric() function to verify the user entered a valid number, which prevents runtime errors if the field contains text or is left completely blank.

Free Microsoft Office alternative

Looking for a Free Alternative to Microsoft Office?

While WPS Office does not include a complex relational database tool like Access, it offers a robust, highly compatible alternative for everyday data processing. If you need to manage numerical data, perform complex calculations, and run VBA macros without building heavy database forms, WPS Spreadsheet is an excellent choice.

1. Download WPS Office: Visit the official WPS Office website to download the free software installer.
2. Install the Software: Run the setup file and follow the standard installation prompts on your device.
3. Start Processing Data: Launch WPS Spreadsheet to manage your data lists, calculations, and numerical inputs with ease.

Free to download with a lightweight and fast installation process.Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Supports advanced spreadsheet formulas and VBA for numerical data processing without needing complex form controls.Familiar user interface makes transitioning completely seamless and easy.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Type Mismatch' error when reading the unbound text box?

A 'Type Mismatch' error occurs if the text box contains text characters or is completely empty when your VBA code attempts to convert it to a number. You can use the 'Nz()' function to handle null values and the 'IsNumeric()' function to verify the content before converting.

How can I restrict an unbound text box to only accept numbers?

You can set an Input Mask in the Property Sheet (e.g., using '0' or '9' characters) to restrict what a user can type. Alternatively, you can use the 'KeyPress' event in VBA to intercept and block non-numeric keystrokes.

Can I save the unbound text box value to a table later?

Yes. Even though the control is unbound, you can use an SQL 'INSERT' or 'UPDATE' statement in your VBA code, or utilize a Recordset, to write the value from the text box into a specific table field when a save button is clicked.

Office Quick Solutions