Input Box Error Capture in MS Access

I find input boxes quite useful for getting data from users, especially if it is just a single number, but input boxes can present a problem if the user cancels or exits the input box, or clicks “Okay” without entering anything in the input box. In all three of these cases the input box returns a zero-length string. You not only need to capture this error, but you need to discover the intent of the user in order to redirect the code in the right way. If the user meant to exit or cancel the input box you’ll want to exit the sub. If the user simply clicked “Okay” before entering the data you’ll want to offer them the chance to enter the data.

The initial error will be a “type mismatch” if the variable for the input data is dimensioned as an integer. Instead, use a string variable, then latter pass the value to an integer variable if needed.

Here is the error capture code for an input box where the user tells how many folders an overfilled folder should be divided into.

strSplit = InputBox("Split Folder " & intFolder & " into how many folders?")

'If the user fails to specify how many folders the folder is to be split into display an error

'message and re-present the input box.

If strSplit = "" Then

     'Since VBA input boxes do not differentiate between "Cancel", "Exit", and "Okay (with no data entered)"

     'the error capture needs a mechanism to allow the user to exit or cancel if they wish or enter data

     'if they had forgotten to enter it.

     If MsgBox("You have not specified the number of folders or have clicked 'Cancel'" & vbNewLine & "Do           you wish to Cancel?", vbYesNo) = vbYes Then

          'The user meant to exit or cancel, so exit the sub

          Exit Sub

     Else:

          'The user did not mean to exit or cancel, so re-present the input box.

          Call cmdSave_Click

     End If

Else:

     intSplit = strSplit

     [rest of the code to split the folder]

End If