Access VBA InputBox: Ask for User Input in a Macro

Yes. In Access VBA, call the built-in InputBox function to display a small dialog and collect a text response; for example, answer = InputBox("Enter a department name:", "Department lookup"). It returns a string, so your code should check the response before using it.

What the Access VBA InputBox Does

An InputBox is a quick way for a procedure to ask someone for one piece of information while the procedure is running. Access displays a dialog with a prompt, a text field, and OK and Cancel buttons. The function returns whatever the user typed as a string when they choose OK.

It is useful for a small, occasional request, such as asking for a search term, a short label, or a date that your code will validate. It is not a control permanently placed on an Access form: the dialog appears only when VBA reaches the function call, and its response is available to the calling procedure. It does not automatically save the value in a table or run a query.

Access has other ways to ask questions. A message box is better for a simple confirmation or choice, while a form is usually better when people need several fields, clearer instructions, or repeated interaction. InputBox is convenient precisely because it is brief, but that simplicity also limits how much you can guide and validate the user.

How to Use InputBox in an Access Procedure

For a button on a form, create a click event procedure and put the function call there. In Access for Microsoft 365 on Windows, open the form in Design View, select the button, and use the Property Sheet’s Event tab to set its On Click property to [Event Procedure]. Click the ellipsis button to open the VBA editor. The following example assumes the form contains a text box named txtDepartment:

Private Sub cmdAsk_Click()
    Dim answer As String

    ' Ask for a department name.
    answer = InputBox("Enter a department name:", "Department lookup")

    ' A blank response can mean Cancel or an empty answer.
    answer = Trim$(answer)
    If Len(answer) = 0 Then
        MsgBox "No department was entered.", vbInformation, "Department lookup"
        Exit Sub
    End If

    ' Put the response in the form's text box.
    Me.txtDepartment = answer
End Sub

The first argument is the prompt. The second is the title shown on the dialog. The assignment stores the returned string in a declared variable, and the final line puts it in the form control. The procedure does not write the value to a table by itself; saving a bound form record or adding explicit data-handling code is a separate step.

Before running a macro or VBA procedure, save a copy of the database or workbook. VBA changes and actions performed by a macro generally cannot be reversed with Ctrl+Z. To open the Visual Basic Editor on Windows, press Alt+F11; on a Mac, Option+F11 may open the editor in Office applications that support VBA. Microsoft Access desktop is a Windows application and does not have a Mac desktop edition.

InputBox Syntax and Optional Arguments

The general VBA syntax is InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]). The square brackets in this notation mark optional arguments; do not type the brackets into your code. The prompt is required. The title and default text are commonly useful, while the position and help arguments are less often needed.

prompt: The question or instruction displayed above the input area.
title: Text for the dialog’s title bar. If omitted, VBA uses a default title.
default: Text that appears in the input field initially. A default can save typing, but should not imply that an unconfirmed value has already been accepted.
xpos and ypos: Optional numeric coordinates for positioning the dialog. If omitted, the dialog is positioned by the system.
helpfile and context: Optional values linking the dialog to a Help file and a help topic. They are mainly relevant in applications that provide that help system.

For example, InputBox("Enter a region:", "Report filter", "North") opens the dialog with “North” already in the field. The user can keep, edit, or delete that text. A default is only a starting value; the procedure still needs to decide what to do with the returned answer.

Handling Cancel, Blank Answers, and Validation

A central detail is that the Access VBA InputBox function returns a string. When the user clicks Cancel, or closes the dialog, it returns a zero-length string. If the user clicks OK after deleting the text, the result is also a zero-length string. The function therefore does not let your code distinguish Cancel from an intentionally empty answer. If that distinction matters, use a form with separate buttons or another interaction method.

Use Len to test whether a string has content, and consider Trim$ to remove spaces at its beginning and end. Without trimming, an answer containing only spaces is not technically empty. Decide what blank means in the context of your task: it might mean cancel, an optional value, or a request to show an error. Do not silently treat it as valid data unless that is intended.

For data that must have a particular type or format, validate it before converting or using it. For example, a numeric response should be checked with IsNumeric before conversion, and then checked against the acceptable range for the field. A date response can be checked with IsDate, but date interpretation can depend on the user’s regional settings. If the field is important, provide a clear expected format and handle invalid input with a useful message rather than letting a conversion error interrupt the procedure.

Validation also depends on what happens next. A text value inserted into a SQL statement needs appropriate handling; concatenating untrusted input directly into SQL can cause errors and unsafe query behavior. For database lookups and updates, prefer a parameterized query or a form control connected to a query, rather than building SQL by joining the response into a string. An InputBox collects data; it does not make that data safe or valid.

When to Choose a Form or Another Dialog

InputBox works well when one short response is enough and the consequences of a mistaken answer are low. A dedicated Access form is a better choice when the task needs multiple related values, field labels, combo boxes, validation messages, or a clear way to cancel without confusing that action with a blank answer.

Choose InputBox for a quick search term, a one-off name, or a simple optional prompt inside an existing procedure.
Choose a form control when users will enter the value repeatedly, need to see it alongside other data, or should select from a list of valid options.
Choose MsgBox when the task is asking for a confirmation or a small set of predefined responses rather than free-form text. A message box returns a button choice, not a typed string.
Choose a custom dialog form when you need to tell Cancel apart from an empty value, explain errors in place, or collect several fields together.

A form also gives you more control over accessibility, layout, and instructions. For example, a free-text InputBox cannot prevent someone from entering a department that does not exist. A combo box whose row source comes from a department table can guide the user toward valid entries and reduce cleanup later.

Access InputBox Compared with Excel InputBox

The VBA InputBox function is available in Access and other Office VBA hosts, but the details of the host application still matter. In Access, the standard function returns text. Excel VBA also has an Application.InputBox method with a Type argument that can constrain the kind of response, such as a number or a range. That Excel-specific method should not be assumed to work in Access.

Code examples found online may also refer to a worksheet cell, workbook, or Excel object. Those objects are not automatically available in Access. In Access, a response might instead be assigned to a form control, used to filter an Access query, or passed to a database procedure. Check which application the example targets before copying its code, and replace host-specific objects with the corresponding Access objects.

Common Problems and Troubleshooting

The function is not recognized: Check spelling and confirm that the code is in a VBA procedure, not typed into a query criteria row or a form property that expects an expression. The built-in VBA function is written as InputBox.
The dialog appears but nothing is saved: That is expected unless your code assigns the returned value to a control, variable, or database operation. A variable also disappears when its procedure ends unless the value is stored somewhere else.
The code continues after Cancel: Test for a zero-length result before doing work that requires an answer. Remember that Cancel and OK with an empty field have the same return value.
A conversion causes an error: The response is text even when it looks like a number or date. Validate it before converting, and check that converted numbers fit the destination field’s range.
The dialog is covered or appears in an inconvenient place: Avoid relying on optional screen coordinates unless there is a real need to position it. A custom form offers more predictable control over the interaction.

If VBA code is blocked, review Access’s security settings and the source of the database. Only enable content for files you trust; enabling macros broadly can allow untrusted code to run. In managed workplaces, an administrator may control trusted locations or macro policies. Changing security settings is not a substitute for checking where a database came from.

Frequently Asked Questions

How do I open an InputBox in Access VBA?
Call InputBox in a VBA procedure, for example answer = InputBox("Enter a name:", "Name"). Store the returned string in a variable, then decide how to validate and use it.

What does InputBox return when I click Cancel?
It returns a zero-length string. Clicking OK with the field empty produces the same result, so the function alone cannot tell those two actions apart.

Can an Access InputBox return a number?
Its return value is a string, even if the user types digits. Check the response and its allowed range before converting it to a numeric VBA type.

Can I save the answer directly to an Access table?
Not automatically. Assign the response to a bound form control and save the record, or write explicit database code that validates and stores the value appropriately.

Is InputBox the same in Access and Excel?
The basic VBA function is similar, but Excel also provides the Excel-specific Application.InputBox method with options not available in Access’s standard function. Do not copy Excel-only arguments into Access code.

Practical Takeaway

Use Access VBA’s InputBox when a procedure needs one brief text response, and make the prompt specific enough that the user knows what to enter. Check for a blank result, validate the value for its intended use, and store it explicitly if it needs to persist. When users need a reliable choice, multiple fields, or a clear distinction between Cancel and empty input, build the interaction with an Access form instead.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.