Validation Rules for a Control on a Form (?)

[croy]

I was trying to set a validation rule for a control on a
subform, conditionally, determined by a value on the main
form--but I couldn't seem to make it work.

The validation rule I was trying to set was like, "if
cboMain, on the main form, has a value of 2, then the v rule
for txtSub should be, "Is Not Null".

Apparently, I've never used v rules on forms before!

So I set the validation rule manually, directly in the
properties of the textbox on the subform, just to make sure
that it would work that way. It didn't seem to work that
way, either!

Then I found that I could tab (or <Enter>) right thru the
txtSub field without a peep... BUT, if I entered anything
in txtSub, and then tried to delete it, the v rule would
kick in and slap me.

Does this seem as odd to those here as it does to me?

 

[John W. Vinson]

I was trying to set a validation rule for a control on a
subform, conditionally, determined by a value on the main
form--but I couldn't seem to make it work.

Well, Forms don't have validation rules per se. Tables do; Forms have
BeforeUpdate events where you can use VBA code to enforce data validation, but
this code isn't referred to as a "validation rule".

The validation rule I was trying to set was like, "if
cboMain, on the main form, has a value of 2, then the v rule
for txtSub should be, "Is Not Null".

Apparently, I've never used v rules on forms before!

So I set the validation rule manually, directly in the
properties of the textbox on the subform, just to make sure
that it would work that way. It didn't seem to work that
way, either!

Field validation rules don't let you reference other fields. Where did you
enter this? What code did you actually enter?

Then I found that I could tab (or <Enter>) right thru the
txtSub field without a peep... BUT, if I entered anything
in txtSub, and then tried to delete it, the v rule would
kick in and slap me.

Sure. A BeforeUpdate event fires only when you update something, not when you
just look at it on the screen (you wouldn't want it to!!!)

Does this seem as odd to those here as it does to me?

Not as you've phrased it, no; it may just be a matter of expectation.

Please post more details of what you've tried.

Note that Microsoft no longer maintains this forum, and there are only a very
few folks monitoring it. See my .sig for alternative venues which may get you
some more attention.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[croy]

On Sun, 14 Jul 2013 19:55:02 -0600, John W. Vinson

Thanks for the response, John. Replies inline...

Well, Forms don't have validation rules per se. Tables do; Forms have
BeforeUpdate events where you can use VBA code to enforce data validation, but
this code isn't referred to as a "validation rule".


Field validation rules don't let you reference other fields. Where did you
enter this? What code did you actually enter?

I think my first shot at it was in the Enter event for the
subform control. Something like (from stale memory):

If Me.cboMain=2 then
Me.SubformObj.Form.txtControl1.ValidationRule = "Is Not
Null"
Else Me.txtControl.ValidationRule = ""
End If

That doesn't look exactly right to me now, but it's past my
bedtime. but it gives an idea of how I was thinking. I'll
have to look it up at work in the morning.

Sure. A BeforeUpdate event fires only when you update something, not when you
just look at it on the screen (you wouldn't want it to!!!)


Not as you've phrased it, no; it may just be a matter of expectation.

Please post more details of what you've tried.

After the above didn't seem to work, along with numerous
other attempts, I eventually cobbled together something on
the Exit event for the subform control that I want the
validation on. I don't like that much, as it seems, well,
inelegant (probably not very foolproof), but it seems to
work, so far.

Note that Microsoft no longer maintains this forum, and there are only a very
few folks monitoring it.

Yes, I'm aware of that--and stubborn! And you still show
here--what more could I ask for?!

 

[John W. Vinson]

Well, neither the Enter nor Exit event are ideal for what you are trying to
accomplish. They both fire if you are simply tabbing through the controls on
the form not doing anything at all.

If you want to conditionally set the Validation Rule property of a form
control (which, by the way, I never use!) then the form's Current event would
be the best choice. But my preference would be to use the subform's Form (not
the Control's) BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Parent!cboMain = 2 Then
If Len(Me!txtSub & "" ) = 0 Then
Cancel = True
MsgBox "You need to put something into txtSub!", vbOKOnly
Me!txtSub.SetFocus
End If
End if
End Sub
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[croy]

Well, neither the Enter nor Exit event are ideal for what you are trying to
accomplish. They both fire if you are simply tabbing through the controls on
the form not doing anything at all.

Good point!

If you want to conditionally set the Validation Rule property of a form
control (which, by the way, I never use!) then the form's Current event would
be the best choice.

That's were I wound up when still trying to set the
Control's Validation Rule, but it never worked. Even just
setting the validation rule manually in the control's
properties didn't work--even when the record was dirty, it
let me sail on thru that field without a murmur--it only
yelled at me if I first entered something and then tried to
delete it.

But my preference would be to use the subform's Form (not
the Control's) BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Parent!cboMain = 2 Then
If Len(Me!txtSub & "" ) = 0 Then
Cancel = True
MsgBox "You need to put something into txtSub!", vbOKOnly
Me!txtSub.SetFocus
End If
End if
End Sub

Ah, much better than my use of the control's Exit event.
Sturdier.

Thanks for your thoughts!