Tricky validations

[Leslie Isaacs]

Hello All

I have a form with 24 fields that all require the same validation, which is
that the value entered is either a number >0 and < 10 or it is a number >0
and < 10 followed by the letter h (with or without a space between the
number and the h). I have tried using isnumber, and the left (...) function,
but can't seem to get it right.

As there are so many fields requiring ther same validation it would be
helpful if the validation rule didn't have to include the field name (then I
could highlight them all at once and write the validation rule once!), but
if this isn't possible I can obviously copy the validation rule for each
field individually.

Hope someone can help
Many thanks
Les

 

[Klatuu]

One way would be to write a function that would validate the entry. Then
call it in the Before Update event of each of the controls. Have it return
False if the value is correct (sounds backwards, but there is a reason) and
True if the value is wrong.

Now in the Before update event of each control:

Cancel = TestGoodNumber(Me.MyControlName)

So if the value is wrong, the before update event is canceled.

 

[Leslie Isaacs]

Klatuu

Thanks for your reply.
I understand the logic of what you suggest, but I really don't know how to
write the function. Please could you help me with that?

Thanks again
Les

 

[Klatuu]

Private Function GoodNumber(varValue as Variant) As Boolean
Dim intNumPart as Integer
Dim strStringPart as String
If IsNull(varValue) Then
GoodNumber = True
ElseIf IsNumeric(varValue)
If varValue < 1 Or varValue > 10 Then
GoodNumber = True
End If
Else
intNumPart = Val(varValue)
strStringPart = right(varValue,1)
If intNumPart < 1 Or intNumPart > 10 Then
GoodNumber = True
End If
If strString Part <> "h" Then
GoodNumber = True
End If
End Function

 

[Leslie Isaacs]

Klatuu (or is it Dave?!)

Thanks for this.

I'm still struggling to get it to work though!
..
I have used the function you gave below (although I had to add a "then" to
the line ElseIf IsNumeric(varValue), and I had to remove the space between
"strString" and "Part" in the line If strString Part <> "h" near the end),
but when I try to add a value to one of the form fields I get a message that
the Sub or Function is not defined. I had pasted the function below into a
new module, and the Before Update event for the form field called "Ann B" is
Private Sub Ann_B_BeforeUpdate(Cancel As Integer)
Cancel = GoodNumber(Me.[Ann B])
End Sub

What have I done wrong?

Thanks for your continuing help
Les

 

[Klatuu]

Sorry about the typos.
If you pasted the function into a module other than the form module, you
need to change it from Private to Public.
--
Dave Hargis, Microsoft Access MVP

Klatuu (or is it Dave?!)

Thanks for this.

I'm still struggling to get it to work though!
..
I have used the function you gave below (although I had to add a "then" to
the line ElseIf IsNumeric(varValue), and I had to remove the space between
"strString" and "Part" in the line If strString Part <> "h" near the end),
but when I try to add a value to one of the form fields I get a message that
the Sub or Function is not defined. I had pasted the function below into a
new module, and the Before Update event for the form field called "Ann B" is
Private Sub Ann_B_BeforeUpdate(Cancel As Integer)
Cancel = GoodNumber(Me.[Ann B])
End Sub

What have I done wrong?

Thanks for your continuing help
Les

 

[Leslie Isaacs]

Klatuu

Many thanks for that - and now I understand the difference between Private
and Public!
How would I have pasted the function into a form module though?

Thanks again
Les

Sorry about the typos.
If you pasted the function into a module other than the form module, you
need to change it from Private to Public.
--
Dave Hargis, Microsoft Access MVP

Klatuu (or is it Dave?!)

Thanks for this.

I'm still struggling to get it to work though!
..
I have used the function you gave below (although I had to add a "then"
to
the line ElseIf IsNumeric(varValue), and I had to remove the space
between
"strString" and "Part" in the line If strString Part <> "h" near the
end),
but when I try to add a value to one of the form fields I get a message
that
the Sub or Function is not defined. I had pasted the function below into
a
new module, and the Before Update event for the form field called "Ann B"
is
Private Sub Ann_B_BeforeUpdate(Cancel As Integer)
Cancel = GoodNumber(Me.[Ann B])
End Sub

What have I done wrong?

Thanks for your continuing help
Les

Private Function GoodNumber(varValue as Variant) As Boolean
Dim intNumPart as Integer
Dim strStringPart as String
If IsNull(varValue) Then
GoodNumber = True
ElseIf IsNumeric(varValue)
If varValue < 1 Or varValue > 10 Then
GoodNumber = True
End If
Else
intNumPart = Val(varValue)
strStringPart = right(varValue,1)
If intNumPart < 1 Or intNumPart > 10 Then
GoodNumber = True
End If
If strString Part <> "h" Then
GoodNumber = True
End If
End Function
--
Dave Hargis, Microsoft Access MVP


:

Klatuu

Thanks for your reply.
I understand the logic of what you suggest, but I really don't know
how
to
write the function. Please could you help me with that?

Thanks again
Les


One way would be to write a function that would validate the entry.
Then
call it in the Before Update event of each of the controls. Have it
return
False if the value is correct (sounds backwards, but there is a
reason)
and
True if the value is wrong.

Now in the Before update event of each control:

Cancel = TestGoodNumber(Me.MyControlName)

So if the value is wrong, the before update event is canceled.

--
Dave Hargis, Microsoft Access MVP


:

Hello All

I have a form with 24 fields that all require the same validation,
which
is
that the value entered is either a number >0 and < 10 or it is a
number
0
and < 10 followed by the letter h (with or without a space between
the
number and the h). I have tried using isnumber, and the left (...)
function,
but can't seem to get it right.

As there are so many fields requiring ther same validation it would
be
helpful if the validation rule didn't have to include the field
name
(then I
could highlight them all at once and write the validation rule
once!),
but
if this isn't possible I can obviously copy the validation rule for
each
field individually.

Hope someone can help
Many thanks
Les

 

[Klatuu]

The same way. Just open the form's module and paste it in. I usually put my
non event functions and subs at the top before any event subs, but below the
Option statments.
--
Dave Hargis, Microsoft Access MVP

Klatuu

Many thanks for that - and now I understand the difference between Private
and Public!
How would I have pasted the function into a form module though?

Thanks again
Les

Sorry about the typos.
If you pasted the function into a module other than the form module, you
need to change it from Private to Public.
--
Dave Hargis, Microsoft Access MVP

Klatuu (or is it Dave?!)

Thanks for this.

I'm still struggling to get it to work though!
..
I have used the function you gave below (although I had to add a "then"
to
the line ElseIf IsNumeric(varValue), and I had to remove the space
between
"strString" and "Part" in the line If strString Part <> "h" near the
end),
but when I try to add a value to one of the form fields I get a message
that
the Sub or Function is not defined. I had pasted the function below into
a
new module, and the Before Update event for the form field called "Ann B"
is
Private Sub Ann_B_BeforeUpdate(Cancel As Integer)
Cancel = GoodNumber(Me.[Ann B])
End Sub

What have I done wrong?

Thanks for your continuing help
Les



Private Function GoodNumber(varValue as Variant) As Boolean
Dim intNumPart as Integer
Dim strStringPart as String
If IsNull(varValue) Then
GoodNumber = True
ElseIf IsNumeric(varValue)
If varValue < 1 Or varValue > 10 Then
GoodNumber = True
End If
Else
intNumPart = Val(varValue)
strStringPart = right(varValue,1)
If intNumPart < 1 Or intNumPart > 10 Then
GoodNumber = True
End If
If strString Part <> "h" Then
GoodNumber = True
End If
End Function
--
Dave Hargis, Microsoft Access MVP


:

Klatuu

Thanks for your reply.
I understand the logic of what you suggest, but I really don't know
how
to
write the function. Please could you help me with that?

Thanks again
Les


One way would be to write a function that would validate the entry.
Then
call it in the Before Update event of each of the controls. Have it
return
False if the value is correct (sounds backwards, but there is a
reason)
and
True if the value is wrong.

Now in the Before update event of each control:

Cancel = TestGoodNumber(Me.MyControlName)

So if the value is wrong, the before update event is canceled.

--
Dave Hargis, Microsoft Access MVP


:

Hello All

I have a form with 24 fields that all require the same validation,
which
is
that the value entered is either a number >0 and < 10 or it is a
number
0
and < 10 followed by the letter h (with or without a space between
the
number and the h). I have tried using isnumber, and the left (...)
function,
but can't seem to get it right.

As there are so many fields requiring ther same validation it would
be
helpful if the validation rule didn't have to include the field
name
(then I
could highlight them all at once and write the validation rule
once!),
but
if this isn't possible I can obviously copy the validation rule for
each
field individually.

Hope someone can help
Many thanks
Les

 

[Leslie Isaacs]

Got it!
Thanks
Les

The same way. Just open the form's module and paste it in. I usually put
my
non event functions and subs at the top before any event subs, but below
the
Option statments.
--
Dave Hargis, Microsoft Access MVP

Klatuu

Many thanks for that - and now I understand the difference between
Private
and Public!
How would I have pasted the function into a form module though?

Thanks again
Les

Sorry about the typos.
If you pasted the function into a module other than the form module,
you
need to change it from Private to Public.
--
Dave Hargis, Microsoft Access MVP


:

Klatuu (or is it Dave?!)

Thanks for this.

I'm still struggling to get it to work though!
..
I have used the function you gave below (although I had to add a
"then"
to
the line ElseIf IsNumeric(varValue), and I had to remove the space
between
"strString" and "Part" in the line If strString Part <> "h" near the
end),
but when I try to add a value to one of the form fields I get a
message
that
the Sub or Function is not defined. I had pasted the function below
into
a
new module, and the Before Update event for the form field called "Ann
B"
is
Private Sub Ann_B_BeforeUpdate(Cancel As Integer)
Cancel = GoodNumber(Me.[Ann B])
End Sub

What have I done wrong?

Thanks for your continuing help
Les



Private Function GoodNumber(varValue as Variant) As Boolean
Dim intNumPart as Integer
Dim strStringPart as String
If IsNull(varValue) Then
GoodNumber = True
ElseIf IsNumeric(varValue)
If varValue < 1 Or varValue > 10 Then
GoodNumber = True
End If
Else
intNumPart = Val(varValue)
strStringPart = right(varValue,1)
If intNumPart < 1 Or intNumPart > 10 Then
GoodNumber = True
End If
If strString Part <> "h" Then
GoodNumber = True
End If
End Function
--
Dave Hargis, Microsoft Access MVP


:

Klatuu

Thanks for your reply.
I understand the logic of what you suggest, but I really don't know
how
to
write the function. Please could you help me with that?

Thanks again
Les


One way would be to write a function that would validate the
entry.
Then
call it in the Before Update event of each of the controls. Have
it
return
False if the value is correct (sounds backwards, but there is a
reason)
and
True if the value is wrong.

Now in the Before update event of each control:

Cancel = TestGoodNumber(Me.MyControlName)

So if the value is wrong, the before update event is canceled.

--
Dave Hargis, Microsoft Access MVP


:

Hello All

I have a form with 24 fields that all require the same
validation,
which
is
that the value entered is either a number >0 and < 10 or it is a
number
0
and < 10 followed by the letter h (with or without a space
between
the
number and the h). I have tried using isnumber, and the left
(...)
function,
but can't seem to get it right.

As there are so many fields requiring ther same validation it
would
be
helpful if the validation rule didn't have to include the field
name
(then I
could highlight them all at once and write the validation rule
once!),
but
if this isn't possible I can obviously copy the validation rule
for
each
field individually.

Hope someone can help
Many thanks
Les