Access VBA InputBox: Syntax, Examples, and Tips

VBA & macros · Updated November 12, 2026

In Access VBA, use the built-in InputBox function to show a simple dialog and collect one line of text. It returns the user’s response as a string; if the user clicks Cancel, it returns an empty string, which is also what it returns when the user submits a blank response.

What InputBox Does in Access

InputBox is a quick way for a procedure to ask for one piece of information while it runs. For example, a macro or button event can request a search term, a short note, or a value that the procedure will check before continuing. The dialog is modal: the user must respond to it or dismiss it before returning to the Access window.

The function belongs to VBA, so it is available in Access code without creating a form or adding a reference. It is not an Access data-entry control: the response is not automatically saved to a table or written into the active form. Your code must read the returned string, validate it, and decide what to do with it.

An InputBox is best for a brief, one-off prompt. It is not a substitute for a well-designed form when the user needs several fields, a list of choices, clear validation messages, or a reliable distinction between Cancel and a blank answer.

Syntax and Optional Arguments

The VBA syntax is InputBox(prompt, [title], [default], [xpos], [ypos], [helpfile], [context]). The prompt is required. The other arguments are optional and let you customize the dialog:

  • prompt: The text displayed in the dialog. Keep it specific, and say what format or range is expected when that matters.
  • title: Text shown in the dialog’s title bar. If omitted, VBA uses a default title.
  • default: Text initially displayed in the response box. A useful default can reduce typing, but users can edit or remove it.
  • xpos and ypos: Optional positions for the dialog. If omitted, the dialog is centered. These position arguments use twips, a unit of screen measurement.
  • helpfile and context: Optional help-file information for a context-sensitive Help button. These are usually unnecessary for a small Access prompt.

The result is a String, even when the user types digits. That means code should not assume the response is already a number, date, or valid database value. Treat it as text first, check it, and convert it only after validation.

A Basic Access VBA Example

This procedure asks for a name, rejects an empty response, and then displays a confirmation. The empty-response check also catches Cancel; the next section explains why those two actions cannot be distinguished with the built-in function alone.

Public Sub AskForName()
    Dim answer As String

    ' Ask for one value and provide a clear dialog title.
    answer = InputBox("Enter a customer name:", "Customer lookup")

    If Len(Trim$(answer)) = 0 Then
        MsgBox "No name was entered, or the prompt was canceled.", vbInformation
        Exit Sub
    End If

    MsgBox "You entered: " & Trim$(answer), vbInformation
End Sub

To try code that changes data or runs an action, save a copy of the database first. VBA macros and procedures generally cannot be reversed with Ctrl+Z, so a backup is important before testing changes to records or other database content.

Validating Text and Numeric Responses

Validation belongs in the procedure that receives the response. A prompt that says “Enter a number” does not force numeric input; the user can type letters, paste unexpected text, or leave the box empty. Use checks before converting the returned string, and explain how to correct a rejected value.

For example, the following procedure accepts a whole number from 1 through 1,000. It uses IsNumeric to check the text, converts it to a Double for range and whole-number checks, and only then assigns it to a Long.

Public Sub AskForQuantity()
    Dim response As String
    Dim numericValue As Double
    Dim quantity As Long

    ' A default is a convenience, not a validation rule.
    response = InputBox("Enter a whole number from 1 to 1000:", _
                        "Quantity", "1")

    If Len(Trim$(response)) = 0 Then
        MsgBox "No quantity was entered, or the prompt was canceled.", vbInformation
        Exit Sub
    End If

    If Not IsNumeric(response) Then
        MsgBox "Enter a numeric value.", vbExclamation
        Exit Sub
    End If

    On Error GoTo InvalidEntry
    numericValue = CDbl(response)

    If numericValue < 1 Or numericValue > 1000 Then
        MsgBox "The quantity must be from 1 to 1000.", vbExclamation
        Exit Sub
    End If

    If numericValue <> Fix(numericValue) Then
        MsgBox "Enter a whole number, not a decimal.", vbExclamation
        Exit Sub
    End If

    quantity = CLng(numericValue)
    MsgBox "Quantity accepted: " & quantity, vbInformation
    Exit Sub

InvalidEntry:
    MsgBox "That value cannot be used as a quantity.", vbExclamation
End Sub

The checks are deliberately separate: “numeric” does not necessarily mean “an allowed whole number.” Conversions can also fail for extreme values, so the error handler prevents a conversion error from abruptly stopping the procedure. For dates, currency, or identifiers, use checks suited to the actual data. A value such as a postal code may contain leading zeroes and should often remain text rather than be converted to a number.

Cancel, Blank Answers, and Defaults

A common question is whether VBA can tell that the user clicked Cancel rather than submitting an empty box. With the standard InputBox function, it cannot: both actions return a zero-length string. Testing Len(response) = 0 is useful when either action should stop the procedure, but it does not reveal which action occurred.

A non-empty default changes one common case but does not solve the general problem. If the user leaves the default untouched and submits it, the function returns that default; clicking Cancel still returns an empty string. However, the user can erase the default and submit a blank response, which again produces the same result as Cancel. Do not treat a default value as a dependable cancellation signal.

If the difference matters—for example, a blank note is allowed but Cancel must abandon a multi-step task—use a small Access form with a text box and separate OK and Cancel buttons. Your code can store an explicit result when each button is clicked. This gives the procedure an unambiguous choice and lets the form display validation instructions beside the relevant field.

Using InputBox from an Access Form or Procedure

You can call InputBox from a standard module, a form’s event procedure, or another VBA procedure. A common place is a command button’s On Click event: the click handler asks for a short value, checks it, and then performs the next step. Keep the prompt and its validation close to the action that depends on the answer so another developer can understand the flow.

For example, a search button might ask for a customer name and then use that text to filter a form. Avoid writing a query by concatenating untrusted input into SQL. For database operations, use a parameterized query or a carefully constructed form filter, and validate the value according to the field’s type. An InputBox is a user-interface convenience, not a database security boundary.

The built-in VBA function is not the same thing as Excel’s Application.InputBox, which has Excel-specific behavior and can return values of different types. In Access, use VBA’s InputBox function for a simple text response. If you need typed controls, lookup lists, or richer interaction, build an Access form rather than relying on an Excel-specific method.

When a Form Is Better

InputBox has a small, fixed interface and provides little control over layout or interaction. It is convenient when a user needs to enter a single temporary value, but its limitations become significant as the task grows. A form can make required fields visible, offer combo boxes instead of free-form text, keep instructions on screen, and provide distinct buttons for saving and canceling.

Consider replacing an InputBox with a form when any of these apply:

  • The user must enter two or more related values, such as a date and a reason.
  • The answer must be selected from known records or a controlled list.
  • Blank is valid but Cancel must be reported separately.
  • The workflow needs repeated corrections without closing and reopening a dialog.
  • The information should be saved directly to a table or remain visible for review.

A form takes more setup than a single function call, but it can reduce ambiguity and support more useful validation. For a one-time internal utility, InputBox may be sufficient; for a workflow used regularly by others, the additional structure is often worthwhile.

Common Problems and Practical Fixes

  • The response is always treated as text: Declare a string variable for the return value, validate it, and then convert it. Do not assume typing digits makes the returned value numeric.
  • Cancel seems to be accepted as a blank value: Cancel and an empty submission both return an empty string. If that distinction is required, use a custom form.
  • The prompt accepts invalid values: The dialog itself does not enforce a data type or range. Add validation in VBA and tell the user what is acceptable.
  • The procedure stops on a conversion error: Check the response before using conversion functions, and handle conversion failures where input could be out of range.
  • The value does not appear in a table: InputBox only returns text. Your procedure must explicitly update a control or save a record, using an appropriate database operation.

Also make prompts concrete. “Enter a date in your regional date format” is more informative than “Date?” when the expected format could be unclear. If the input will be used in a search, say whether partial matches are allowed. Clear wording prevents avoidable retries, but it does not replace code-level checks.

Frequently Asked Questions

How do I use an InputBox in Access VBA?
Assign the result of InputBox("Your prompt", "A title") to a string variable. Then test and validate that string before using it. The function displays a dialog and returns text; it does not save the response automatically.

What does Access InputBox return when the user clicks Cancel?
The standard VBA InputBox function returns an empty string when the user clicks Cancel. It also returns an empty string if the user submits a blank response, so the return value alone cannot distinguish those actions.

Can InputBox return a number in Access?
It returns a string, even if the user types digits. Check that the response is numeric and within the expected range, then convert it with a suitable VBA conversion function. Keep values such as account codes as text if leading zeroes or nonnumeric characters matter.

Can I set a default value in an Access InputBox?
Yes. Supply the third argument, for example InputBox("Enter a quantity:", "Quantity", "1"). The text appears initially, but the user can change or erase it, so code must still validate the result.

Should I use InputBox or an Access form?
Use InputBox for a brief, single text response when simple validation is enough. Choose a form when you need multiple fields, controlled choices, clearer error handling, saved data entry, or a reliable distinction between Cancel and a blank answer.

Related

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.