Validation rule problem

[Cathleen]

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 Boyce]

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]

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

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

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!

 

[Jeanette Cunningham]

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]

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.
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

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]

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.
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

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!

 

[Cathleen]

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

 

[Cathleen]

I finally got everything working perfectly after importing all of my objects
into a new database in Access 2003. Thanks again for all of your help with
the code for validation and the ensuing problems.

Cathleen

Cathleen:

Sounds like there's a problem either with the file on your system at work, or
with the Access installation at your office.

I don't use Access 2007 myself, but I imagine that it has a facility for
converting a file to an earlier format. Try creating a new copy of your home
file in 2003 format and see if that works on your system at work. You'll
presumably need to convert your office copy up to 2007 first and then back
down again to 2003. If it still doesn’t work at the office then try creating
a new database at work with the simple form and 'OK' message. If you have
problems with that it looks like the Access installation has problem, and a
fresh install of Access may be needed. If not then try creating a new empty
file at work and importing all the objects from your current file.

Ken Sheridan
Stafford, England

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

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

[quoted text clipped - 20 lines]

Ken Sheridan
Stafford, England