CODEWITHSUNDEEP
ms-access
derek
derek

Reputation: 1055

Error handling user input

Hi i am using an access form which asks the user to enter a number. I am trying to handle errors which include; 1. If value entered = null, 2. If the value entered is not an integer. i have tried with my code below but still get an "type mismatch error" 

 Dim refNum As Integer


 refNum = InputBox("Please enter the Reference Number")

 If IsNumeric(refNum) Then

 MsgBox ("ok")

 ElseIf refNum = Null Then

 MsgBox (" Field is empty , enter a number")

 Else


 MsgBox (" Please enter a number")


End If

Upvotes: 2

Views: 1472

Answers (2)

HansUp
HansUp

Reputation: 97131

With these 2 lines ...

Dim refNum As Integer
refNum = InputBox("Please enter the Reference Number")

If the user types any of the following into the InputBox, the code will throw a type mismatch error.

  • letters, eg "abc"
  • a number larger than 32767
  • nothing

The reason for that is because it attempts to store the InputBox value to a variable, refNum, which was declared as Integer. The user could however enter a floating point number, and the assignment to refNum will discard the decimal places. For example, if the user enters 1.2, the value of refNum will be 1.

An InputBox offers limited direct control of the users' inputs. Since you're doing this from a form, consider an unbound textbox, txtRefNum, to collect the value from the user. Check the value In the text box's before update event, display your message for unacceptable values and Cancel the update.

In a comment, you indicated you want "an error handler to trap the mismatch error when assigning to refNum, notify the user, and throw up the InputBox again." In that case, this code does what you want.

    Dim refNum As Integer
    Dim strMsg As String

On Error GoTo ErrorHandler

    refNum = InputBox("Please enter the Reference Number")
    MsgBox "ok"

ExitHere:
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 13 ' type mismatch
        MsgBox "Reference NUMBER!"
        Resume ' present InputBox again
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description & ")"
        MsgBox strMsg
        GoTo ExitHere
    End Select

Upvotes: 1

Zaider
Zaider

Reputation: 2013

Your refNum variable needs to be a variant or a string. When you assign your InputBox to refNum as an integer your InputBox expects only integer values and so breaks before getting to your if statement.

If you use a string you would also need to change your elseif.

ElseIf refNum = Null Or refNum = "" Then

Upvotes: 0

Related Questions