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: