Data Validation Useing BeforeUpdate

  • Thread starter Thread starter Herschel Elkins
  • Start date Start date
H

Herschel Elkins

I am trying to validate the data entered into a text box
on a form. I want to allow the following data to be
entered into the field: The word UNKNOWN or the word
PATERNITY or the word FOSTERCARE or a 10 digit number
beginning with the number 7.

Here's what I have so far:

Private Sub SETSCASE_BeforeUpdate(Cancel As Integer)
Dim str As String

If Not IsNull(Me!SETSCASE) Then
str = Me!SETSCASE

If str <> "UNKNOWN" Or str <> "PATERNITY" Or
str <> "FOSTERCARE" Or str <> "7#########" Then

MsgBox "You must enter a 10 digit number beginning
with 7 or UNKNOWN or PATERNITY or FOSTERCARE.",
vbExclamation
Cancel = True
End If
End If
End Sub

The problem is that this code will not allow any value to
be entered.

Any help would be appreciated.

Thanks
 
It all looks good except you last validity check <Or str
<> "7#########"> this is asking it to look for literally a
7 and 9 pound symbols. It looks like you are trying to
simulate an input mask. You need to do 3 tests for you
number.
1. Does it start with a 7?
2. is it a valid number?
3. are there 10 digits in total?
I would do this check separately first then include the
results in your fianl validity check.

Dim bolNumCheck As Boolean
If Not (IsNumeric(str)) Or (Left(str, 1) <> 7) Or )Len
(str) <> 10) Then
bolNumCheck = True
Else
bolNumCheck = False
End If
If str <> "UNKNOWN" Or str <> "PATERNITY" Or str
<> "FOSTERCARE" Or (bolNumCheck = True) Then

-Cameron Suterland
 
Back
Top