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

Restricting Data in a Microsoft Access Form

Preventing Numeric Data from being entered into a Microsoft Access Text field:

There may be times when you wish to prevent numeric values from being entered into a text data type field.

When you have set the data type to Text you will have the following settings: Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.

If you wish to prevent numeric values from being entered you can try the following example.

Copy the following into a new module in the Microsoft Access code window:

Public Function TextOnly(ByVal strText As String) As Boolean


'Prevent the insertion of numeric values when
'entering data into text field.


    Dim intCounter As Integer
    For intCounter = 1 To Len(strText)
        If IsNumeric(Mid(strText, intCounter, 1)) Then
            TextOnly = False
            Exit Function
        End If
    Next intCounter
    TextOnly = True
End Function

This module can then be called from the Before_Update event of a form when a user attempts to enter a numeric value into a text field where you would wish to prevent this from occurring. Use the following code in the forms Before_Update event procedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Used to prevent numeric values being entered
'into text data type field.

    If Len(Me.strReviewedBy & vbNullString) = 0 Then
        Exit Sub
    Else
        If Not TextOnly(Me.strReviewedBy) Then
            MsgBox "Please enter Only Text in the Reviewed By field", _
            vbExclamation, "Invalid Data Entry"
            Cancel = True
        End If
    End If
End Sub

This checks the field that data has been entered into (in this example strReviewedBy - change this to the name of your control on the form) to ensure that no numeric values are entered. If a numeric value has been entered the following message box will be presented and the update/entry will not be made:

Message Box informing user of incorrect data entry when attempting to enter numeric values into a text field.