Input Validation
Input validation is a great place to begin learning about error handling and bug fixing. This is because a good portion of program errors come from unexpected user input or responses.
For example, what do you think would happen if a user entered a letter or character as an operand into a math quiz game? A better question is, "How do I prevent a user from entering a letter into a text box intended for numbers?" What about a game that prompts a user for a level; would testing that the input is a number be enough? Probably not, as most games have only a few levels, so you would also need to test for a range of numbers. In a nutshell, the art of input validation depends on a talented programmer with enough foresight to prevent errors before they happen.
In Microsoft Access, developers can create input validation for forms, tables, and queries with an input mask. In this section, build input validation using VBA.
IsNumeric
Sometimes preventing input errors can be as easy as determining whether a user has entered a number or a string. There are times when you will want the user to enter his or her name, or maybe you are looking for a number such as age. Either way, Visual Basic provides the IsNumeric function for testing such scenarios.
The IsNumeric function takes a variable or expression as a parameter and returns a Boolean value of True if the variable or expression is a number; False if it is not.
Private Sub cmdCheckForNumber_Click()
If IsNumeric(txtNumbersOnly.Value) = False Then
MsgBox "Enter numbers only please."
Else
MsgBox "Thank you for entering numbers."
End If
End Sub
In the preceding example, you can see that by testing the Value of the text box with the IsNumeric function, the user to enter a number. If the IsNumeric function returns the Boolean value of False,the user has entered something other than a number.
Conversely, you could use the IsNumeric function to check for a string value. Simply change the conditional expression in the If statement.
Private Sub cmdCheckForNumber_Click()
If IsNumeric(txtStringDataOnly.Value) = True Then
MsgBox "Enter string data only please."
Else
MsgBox "Thank you for entering non-numeric data."
End If
End Sub
Tip: Remember that Access 2007 VBA returns a Null value for an empty text box.
When testing for numeric or nonnumeric data, it is also common to test for an empty text box using the IsNull function, as the next procedure demonstrates.
Private Sub cmdCheckForNumber_Click()
If IsNull(txtStringDataOnly.Value) Then
MsgBox "Please enter a string value into the text box."
Exit Sub
End If
If IsNumeric(txtStringDataOnly.Value) = True Then
MsgBox "Enter string data only please."
Else
MsgBox "Thank you for entering non-numeric data."
End If
End Sub
The IsNull function takes an expression as a parameter (in my example, the Value property of a text box), and returns a Boolean value (True or False), depending on whether or not the expression is Null.