Validation rule problem

I'm having a problem using a validation rule with a numeric field on a form.
Ideally, I'd like to require the value entered in the control [depth_secchi]
to be either null or less than or equal to the value in the control
[MaxDepth_lookup], in which I'm using DLookup to display a value from another
table.

So, when that didn't work, I tried a simpler validation rule for the
[depth_secchi] field. I've tried (no records violate this rule):
200
200 or is null

but, anytime I put anything in the validation rule property, I can't exit
the control after typing a valid number. The only thing I can do is close the
form. There are no error messages.

I'd appreciate any insight into what may be going wrong - I have no idea
where the problem might be.

Thank you!

Cathleen

Is there a chance there's a 'competing' property set on the underlying
field, perhaps one that would disallow nulls?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Cathleen" wrote in message
...
I'm having a problem using a validation rule with a numeric field on a
form.
Ideally, I'd like to require the value entered in the control
[depth_secchi]
to be either null or less than or equal to the value in the control
[MaxDepth_lookup], in which I'm using DLookup to display a value from
another
table.

So, when that didn't work, I tried a simpler validation rule for the
[depth_secchi] field. I've tried (no records violate this rule):
200
200 or is null

but, anytime I put anything in the validation rule property, I can't exit
the control after typing a valid number. The only thing I can do is close
the
form. There are no error messages.

I'd appreciate any insight into what may be going wrong - I have no idea
where the problem might be.

Thank you!

Jeff,
The properties for the depth_secchi field in the underlying table are set to
number and Double. It is not a required field and is not indexed. No
validation rule or default value either.

By the way, this is on a subform, if that makes a difference. And, there is
conditional formatting in place on the control (disabling it based on the
value of another field), but I tried removing that and it did not solve the
problem, so...any other ideas?

Thanks so much for your help.
Cathleen


"Jeff Boyce" wrote:

Cathleen

Is there a chance there's a 'competing' property set on the underlying
field, perhaps one that would disallow nulls?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Cathleen" wrote in message
...
I'm having a problem using a validation rule with a numeric field on a
form.
Ideally, I'd like to require the value entered in the control
[depth_secchi]
to be either null or less than or equal to the value in the control
[MaxDepth_lookup], in which I'm using DLookup to display a value from
another
table.

So, when that didn't work, I tried a simpler validation rule for the
[depth_secchi] field. I've tried (no records violate this rule):
200
200 or is null

but, anytime I put anything in the validation rule property, I can't exit
the control after typing a valid number. The only thing I can do is close
the
form. There are no error messages.

I'd appreciate any insight into what may be going wrong - I have no idea
where the problem might be.

Thank you!

As this is in a form you could use the control's BeforeUpdate event procedure
to validate it:

Const conMESSAGE = _
"Value cannot be greater than maximum depth."

Dim ctrl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
If ctrl Me.MaxDepth_lookup Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If

Ken Sheridan
Stafford, England

Cathleen wrote:
I'm having a problem using a validation rule with a numeric field on a form.
Ideally, I'd like to require the value entered in the control [depth_secchi]
to be either null or less than or equal to the value in the control
[MaxDepth_lookup], in which I'm using DLookup to display a value from another
table.

So, when that didn't work, I tried a simpler validation rule for the
[depth_secchi] field. I've tried (no records violate this rule):
200
200 or is null

but, anytime I put anything in the validation rule property, I can't exit
the control after typing a valid number. The only thing I can do is close the
form. There are no error messages.

I'd appreciate any insight into what may be going wrong - I have no idea
where the problem might be.

Thank you!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

Hi Cathleen,
try using code to check for acceptable values.
Put the code in the Before Update event of the form, which gives you the
chance to cancel if an incorrect value is entered.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.[depth_secchi]) Then
If Me.[depth_secchi] Me.[Maxdepth_lookup] Then
Cancel = True
MsgBox "Invalid value for depth"
End If
End If
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"Cathleen" wrote in message
...
I'm having a problem using a validation rule with a numeric field on a
form.
Ideally, I'd like to require the value entered in the control
[depth_secchi]
to be either null or less than or equal to the value in the control
[MaxDepth_lookup], in which I'm using DLookup to display a value from
another
table.

So, when that didn't work, I tried a simpler validation rule for the
[depth_secchi] field. I've tried (no records violate this rule):
200
200 or is null

but, anytime I put anything in the validation rule property, I can't exit
the control after typing a valid number. The only thing I can do is close
the
form. There are no error messages.

I'd appreciate any insight into what may be going wrong - I have no idea
where the problem might be.

Thank you!

Thanks for all of the suggestions, but I'm still having problems. I'm
getting an error message that says "object or class does not support this set
of events".

I've tried the following in the subform's Before Update event (also tried
without the IsNull If statement):
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.depth_secchi) Then
If Me.[depth_secchi] Me.[MaxDepth_lookup] Then
Cancel = True
MsgBox "Secchi depth cannot be greater than waterbody maximum depth"
End If
End If
End Sub

Also tried this in the depth_secchi control's Before Update event:
Private Sub depth_secchi_BeforeUpdate(Cancel As Integer)
Const conMESSAGE = "Secchi depth cannot be greater than waterbody maximum
depth"

Dim ctl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
If ctrl Me.MaxDepth_lookup Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If
End Sub

Then tried this in control's BeforeUpdate instead:
Private Sub depth_secchi_BeforeUpdate(Cancel As Integer)
If Me.[depth_secchi] Me.[MaxDepth_lookup] Then
MsgBox "Secchi depth cannot be greater than waterbody maximum depth"
Cancel = True
End If
End Sub

Any ideas as to what might be going on?

Thanks again!
Cathleen




"Jeanette Cunningham" wrote:

Hi Cathleen,
try using code to check for acceptable values.
Put the code in the Before Update event of the form, which gives you the
chance to cancel if an incorrect value is entered.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.[depth_secchi]) Then
If Me.[depth_secchi] Me.[Maxdepth_lookup] Then
Cancel = True
MsgBox "Invalid value for depth"
End If
End If
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



"Cathleen" wrote in message
...
I'm having a problem using a validation rule with a numeric field on a
form.
Ideally, I'd like to require the value entered in the control
[depth_secchi]
to be either null or less than or equal to the value in the control
[MaxDepth_lookup], in which I'm using DLookup to display a value from
another
table.

So, when that didn't work, I tried a simpler validation rule for the
[depth_secchi] field. I've tried (no records violate this rule):
200
200 or is null

but, anytime I put anything in the validation rule property, I can't exit
the control after typing a valid number. The only thing I can do is close
the
form. There are no error messages.

I'd appreciate any insight into what may be going wrong - I have no idea
where the problem might be.

Thank you!

I've tested the code I sent you in a form of my own set up to simulate yours,
and it works as expected. All the variations you've posted look OK to me.
The MaxDepth_lookup control is in the subform I take it, not in the parent
form? If it were the latter you'd reference it with Parent.[MaxDepth_lookup]
rather than Me.[MaxDepth_lookup].

At what stage are you getting the error. If its a compilation error rather
than a runtime error it would occur either when the code is first executed
(Access attempts to compile it then), or if you attempt to compile it
beforehand from the VBA menu or toolbar (always a good idea when writing code)
. In either case the offending code should be shown highlighted when the
error occurs.

If it’s a runtime error then it would occur when the code is executed, and
the error message would usually have a Debug button which takes you to the
offending line. Or you can debug the code by setting a breakpoint early in
the procedure and then, once the breakpoint is reached in normal execution of
the procedure, stepping into the code line by line with the F8 key until the
error occurs. You'd then be able to see on which line it occurs.

Ken Sheridan
Stafford, England

Cathleen wrote:
Thanks for all of the suggestions, but I'm still having problems. I'm
getting an error message that says "object or class does not support this set
of events".

I've tried the following in the subform's Before Update event (also tried
without the IsNull If statement):
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.depth_secchi) Then
If Me.[depth_secchi] Me.[MaxDepth_lookup] Then
Cancel = True
MsgBox "Secchi depth cannot be greater than waterbody maximum depth"
End If
End If
End Sub

Also tried this in the depth_secchi control's Before Update event:
Private Sub depth_secchi_BeforeUpdate(Cancel As Integer)
Const conMESSAGE = "Secchi depth cannot be greater than waterbody maximum
depth"

Dim ctl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
If ctrl Me.MaxDepth_lookup Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If
End Sub

Then tried this in control's BeforeUpdate instead:
Private Sub depth_secchi_BeforeUpdate(Cancel As Integer)
If Me.[depth_secchi] Me.[MaxDepth_lookup] Then
MsgBox "Secchi depth cannot be greater than waterbody maximum depth"
Cancel = True
End If
End Sub

Any ideas as to what might be going on?

Thanks again!
Cathleen

Hi Cathleen,
try using code to check for acceptable values.
[quoted text clipped - 35 lines]

Thank you!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

When using the depth_secchi control's Before Update event, the error occurs
anytime I attempt to change its value. The full text of the message is: The
expression Before Update you entered as the event property setting produced
the following error: Object or class does not support the set of events.

There is not a "debug" button, only a "show help" button, which takes me to
a help window with the following information:

Visual Basic for Applications (VBA) encountered a problem while attempting
to access a property or method. The problem may be one of the following:

A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base
article 283806.

An Expression is misspelled.
Check all expressions used in event properties for correct spelling.

A user-defined function is declared as a sub or as a private function in a
module.
Expressions can resolve a user-defined function only if the function is
declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8
update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function
properly when security is set to Medium or High. To obtain the latest version
of Microsoft Jet, go to Windows Update


I've checked spelling of control names, etc. but I'm not sure what to do to
investigate the other potential problems listed.

Thanks,
Cathleen

"KenSheridan via AccessMonster.com" wrote:

I've tested the code I sent you in a form of my own set up to simulate yours,
and it works as expected. All the variations you've posted look OK to me.
The MaxDepth_lookup control is in the subform I take it, not in the parent
form? If it were the latter you'd reference it with Parent.[MaxDepth_lookup]
rather than Me.[MaxDepth_lookup].

At what stage are you getting the error. If its a compilation error rather
than a runtime error it would occur either when the code is first executed
(Access attempts to compile it then), or if you attempt to compile it
beforehand from the VBA menu or toolbar (always a good idea when writing code)
. In either case the offending code should be shown highlighted when the
error occurs.

If it’s a runtime error then it would occur when the code is executed, and
the error message would usually have a Debug button which takes you to the
offending line. Or you can debug the code by setting a breakpoint early in
the procedure and then, once the breakpoint is reached in normal execution of
the procedure, stepping into the code line by line with the F8 key until the
error occurs. You'd then be able to see on which line it occurs.

Ken Sheridan
Stafford, England

Cathleen wrote:
Thanks for all of the suggestions, but I'm still having problems. I'm
getting an error message that says "object or class does not support this set
of events".

I've tried the following in the subform's Before Update event (also tried
without the IsNull If statement):
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.depth_secchi) Then
If Me.[depth_secchi] Me.[MaxDepth_lookup] Then
Cancel = True
MsgBox "Secchi depth cannot be greater than waterbody maximum depth"
End If
End If
End Sub

Also tried this in the depth_secchi control's Before Update event:
Private Sub depth_secchi_BeforeUpdate(Cancel As Integer)
Const conMESSAGE = "Secchi depth cannot be greater than waterbody maximum
depth"

Dim ctl As Control

Set ctrl = Me.ActiveControl

If Not IsNull(ctrl) Then
If ctrl Me.MaxDepth_lookup Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End If
End If
End Sub

Then tried this in control's BeforeUpdate instead:
Private Sub depth_secchi_BeforeUpdate(Cancel As Integer)
If Me.[depth_secchi] Me.[MaxDepth_lookup] Then
MsgBox "Secchi depth cannot be greater than waterbody maximum depth"
Cancel = True
End If
End Sub

Any ideas as to what might be going on?

Thanks again!
Cathleen

Hi Cathleen,
try using code to check for acceptable values.
[quoted text clipped - 35 lines]

Thank you!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

To check for a missing or broken reference open the VBA window at any code
and select Tools | References on the VBA menu bar. See if any are marked as
missing or broken. If so then first removing the reference by unchecking it
and then recreating it more often than not cures this.

The Before Update event property is built in of course, so we can assume that
the error doesn't arise from a user defined function. To test whether the
problem is with this particular form try creating a simple unbound form with
an unbound text box on it and in its BeforeUpdate event procedure put MsgBox
"OK". Then enter anything in the control and press the Enter key. If you
get the 'OK' message then it suggests the problem is with the original form.
If so copy the form and paste it back under a new name, and see if the new
form works. Doing this often cures any corruption in the form.

If you get the same error with your temporary unbound form when you enter
something in the control it suggests the problem is more fundamental.
Compacting and repairing the database is the obvious first step, but if the
problem persists then try the BeforeUpdate event of a control in a form in
another database. If it works OK in that then create a new empty database
and import all the objects from your current one into it. Then see if they
work in the new database.

Ken Sheridan
Stafford, England

Cathleen wrote:
When using the depth_secchi control's Before Update event, the error occurs
anytime I attempt to change its value. The full text of the message is: The
expression Before Update you entered as the event property setting produced
the following error: Object or class does not support the set of events.

There is not a "debug" button, only a "show help" button, which takes me to
a help window with the following information:

Visual Basic for Applications (VBA) encountered a problem while attempting
to access a property or method. The problem may be one of the following:

A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base
article 283806.

An Expression is misspelled.
Check all expressions used in event properties for correct spelling.

A user-defined function is declared as a sub or as a private function in a
module.
Expressions can resolve a user-defined function only if the function is
declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8
update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function
properly when security is set to Medium or High. To obtain the latest version
of Microsoft Jet, go to Windows Update

I've checked spelling of control names, etc. but I'm not sure what to do to
investigate the other potential problems listed.

Thanks,
Cathleen

I've tested the code I sent you in a form of my own set up to simulate yours,
and it works as expected. All the variations you've posted look OK to me.
[quoted text clipped - 69 lines]

Thank you!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

Ken,
Thanks for all of your assistance. I discovered a bit of new information
when I tried to implement the steps you recommended. I am now working on my
home computer, using Access 2007 and the code runs perfectly (same form and
database as earlier).

So, from this I'll infer that the code is just fine. So, I'll need to get
this running on my work computer in Access 2003 - do you still recommend
trying the new form, new database steps you outlined below? Or, is there some
other type of problem that could be occurring?

Thank you again,
Cathleen

"KenSheridan via AccessMonster.com" wrote:

To check for a missing or broken reference open the VBA window at any code
and select Tools | References on the VBA menu bar. See if any are marked as
missing or broken. If so then first removing the reference by unchecking it
and then recreating it more often than not cures this.

The Before Update event property is built in of course, so we can assume that
the error doesn't arise from a user defined function. To test whether the
problem is with this particular form try creating a simple unbound form with
an unbound text box on it and in its BeforeUpdate event procedure put MsgBox
"OK". Then enter anything in the control and press the Enter key. If you
get the 'OK' message then it suggests the problem is with the original form.
If so copy the form and paste it back under a new name, and see if the new
form works. Doing this often cures any corruption in the form.

If you get the same error with your temporary unbound form when you enter
something in the control it suggests the problem is more fundamental.
Compacting and repairing the database is the obvious first step, but if the
problem persists then try the BeforeUpdate event of a control in a form in
another database. If it works OK in that then create a new empty database
and import all the objects from your current one into it. Then see if they
work in the new database.

Ken Sheridan
Stafford, England