Testing a value using a validation string

Good evening

Sorry for my non-perfect english writing. I usually post in It.Comp.Appl.Access (an italian
newsgroup).

I need help about testing a control value using a validation string by VBA code

Example:

I have a control named "IDcustomer"

Ex. 1

Me!IDcustomer.Value=12
Me!IDcustomer.ValidationRule="Between 1 and 200"

input: 12 , "Between 1 and 200"
output: True (-1)

Ex. 2
Me!IDcustomer.Value=10
Me!IDcustomer.ValidationRule="10"

input: 10 , "10"
output: False (0)

How can I know in advance if the IDcustomer.Value is a valid value for the validation string
"Between 1 and 200" without calling the AfterUpdate event that's generate the 2107 runtime error?

I'm trying to use the Undo method but the OldValue property is only correctly available before the
AfterUpdate Event.

Thank you so much
have a good weekend

---------------------------
Michele (Giò)

Michele,

As far as I know, you can't directly use the Validation Rule property to
evaluate your data until after the event. If you want to evaluate the
data as it is being entered, it would be better to write code on the
control's Before Update event instead. For example...

If Me.IDcustomer 1 Or Me.IDcustomer 200 Then
MsgBox "Invalid entry"
Cancel = True
End If

--
Steve Schapel, Microsoft Access MVP

Michele (Giò) wrote:
Good evening

Sorry for my non-perfect english writing. I usually post in It.Comp.Appl.Access (an italian
newsgroup).

I need help about testing a control value using a validation string by VBA code

Example:

I have a control named "IDcustomer"

Ex. 1

Me!IDcustomer.Value=12
Me!IDcustomer.ValidationRule="Between 1 and 200"

input: 12 , "Between 1 and 200"
output: True (-1)

Ex. 2
Me!IDcustomer.Value=10
Me!IDcustomer.ValidationRule="10"

input: 10 , "10"
output: False (0)

How can I know in advance if the IDcustomer.Value is a valid value for the validation string
"Between 1 and 200" without calling the AfterUpdate event that's generate the 2107 runtime error?

I'm trying to use the Undo method but the OldValue property is only correctly available before the
AfterUpdate Event.

Thank you so much
have a good weekend

---------------------------
Michele (Giò)

Dear Steve, thanks in advance for your answer.

I'm thinking (about this problem) to create a VBA function that accepts in input (byval or byref,
I'm again thinking about) the .activecontrol Me property.
This function will set a clone of the control in a hidden form and will simulate the validation. In
a negative return case, the function in the form BeforeUpdate event will launch the "Cancel=true"
and a "Err.raise 2107", captured and handled by Form_Error Event (where there's a standard self-made
error handling function).

How do you think about?

Thanks

Michele

"Steve Schapel" ha scritto nel messaggio
...
Michele,

As far as I know, you can't directly use the Validation Rule property to evaluate your data until
after the event. If you want to evaluate the data as it is being entered, it would be better to
write code on the control's Before Update event instead. For example...

If Me.IDcustomer 1 Or Me.IDcustomer 200 Then
MsgBox "Invalid entry"
Cancel = True
End If

--
Steve Schapel, Microsoft Access MVP

Michele (Giò) wrote:
Good evening

Sorry for my non-perfect english writing. I usually post in It.Comp.Appl.Access (an italian
newsgroup).

I need help about testing a control value using a validation string by VBA code

Example:

I have a control named "IDcustomer"

Ex. 1

Me!IDcustomer.Value=12
Me!IDcustomer.ValidationRule="Between 1 and 200"

input: 12 , "Between 1 and 200"
output: True (-1)

Ex. 2
Me!IDcustomer.Value=10
Me!IDcustomer.ValidationRule="10"

input: 10 , "10"
output: False (0)

How can I know in advance if the IDcustomer.Value is a valid value for the validation string
"Between 1 and 200" without calling the AfterUpdate event that's generate the 2107 runtime error?

I'm trying to use the Undo method but the OldValue property is only correctly available before
the AfterUpdate Event.

Thank you so much
have a good weekend

---------------------------
Michele (Giò)

Michele,

I sort of follow what you are thinking here. But I don't think I
understand "why". Maybe I'm missing something, but this would seem to
be unnecessarily complicated. Why not just set the validation procedure
directly on the Before Update event of the control itself? What really
is the purpose of the hidden form?

--
Steve Schapel, Microsoft Access MVP

Michele (Giò) wrote:
Dear Steve, thanks in advance for your answer.

I'm thinking (about this problem) to create a VBA function that accepts in input (byval or byref,
I'm again thinking about) the .activecontrol Me property.
This function will set a clone of the control in a hidden form and will simulate the validation. In
a negative return case, the function in the form BeforeUpdate event will launch the "Cancel=true"
and a "Err.raise 2107", captured and handled by Form_Error Event (where there's a standard self-made
error handling function).

"Steve Schapel" ha scritto nel messaggio
...
Michele,

I sort of follow what you are thinking here. But I don't think I understand "why". Maybe I'm
missing something, but this would seem to be unnecessarily complicated. Why not just set the
validation procedure directly on the Before Update event of the control itself? What really is
the purpose of the hidden form?

Good Morning Steve

Here, in italy, it's 12 o'clock.

After 2 hours of MSDN website search, I found the problem solution.

The Eval function (member of Application) accepts in input a string expression and returns a variant
value that's the result of the string expression

Example

n = Eval ("1=0") ---------- n = 0 (False)

n = Eval ("1=1")----------n = -1 (True)

n = Eval ("5 + 7")----------n = 12

Now, I'm writing a function that, with the Control Value and the ValidationRule string, will compose
the evaluation string to passing to the Eval function.

Thank you very much and have a good day.
Sorry again for my english.

Michele