If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Validation rule problem
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! |
#3
|
|||
|
|||
Validation rule problem
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! |
#4
|
|||
|
|||
Validation rule problem
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 |
#5
|
|||
|
|||
Validation rule problem
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! |
#6
|
|||
|
|||
Validation rule problem
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! |
#7
|
|||
|
|||
Validation rule problem
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 |
#8
|
|||
|
|||
Validation rule problem
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 |
#9
|
|||
|
|||
Validation rule problem
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 |
#10
|
|||
|
|||
Validation rule problem
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 |
|
Thread Tools | |
Display Modes | |
|
|