databasedev.co.uk - database solutions and downloads for microsoft access

Validating with the KeyPress Event

Validate A Microsoft Access Form Textbox To Ensure Only Text Is Entered:

Whilst entering data into textboxes via your Microsoft Access forms, you may need to validate certain data against that which is acceptable. When setting the data types of your database fields one of the data types available is the Text type.

Using Text as your data type will allow: Text or combinations of text and numbers, such as addresses. Also numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.

As the database administrator, you may want to further expand on what is allowed or not allowed to be entered into this type of field and validate against anything other than Text being entered.

We can do this using various methods, and the example below shows a solution using the On Key Press event of a textbox control on the form. What we have to do, is check what value is being entered from the keyboard, using the ASCII code associated with the Key Press event.

In our Microsoft Access form, we may have FirstName and Surname fields, for entering information into the database tables. This information should be entered as alphabetical information only, and should not contain any numeric values or any other special characters.

For the KeyPress event of both the FirstName and Surname field, we can use the following code sample, which will allow us to enter only certain values:

Private Sub FirstName_KeyPress(KeyAscii As Integer)
Select Case KeyAscii ' 65 To 90 and 97 To 122: These are all alphas, upper and lowercase ' 8 Backspace, 9 Tab Key, 32 Space Key Case 65 To 90, 97 To 122, 8, 9, 32 Case Else 'Setting KeyAscii to zero cancels the key input KeyAscii = 0 MsgBox ("Only Alphabetical Characters Allowed") End Select
End Sub

Now, when the user is entering data, and presses anything other than alphabetical characters (upper or lowercase), the Backspace key, the Tab key or the Spacebar, the statement checks this and performs the appropriate action:

Error message when entering data that is not valid for the control.
Error message when entering data that is not valid for the control.