subform reference not working

[Mary]

I've tried using this chart (http://www.mvps.org/access/forms/frm0031.htm)
without succcess and need to ask for help.

1. I have a main form with two subforms. sub1 and sub2 both have Status
fields. If there is a value in the sub1 Status, I don't want to allow an
entry in the sub2 Status. I'd like to have a MsgBox letting users know a
value exists in Sub1 Status.

2. sub2 can have multiple records and some can have Status and some may
not. How can I get the same type of validation as in number 1 only reverse.
If a user tries to put a Status on Sub1 and there is at least one Status on
Sub2, they should get a similar MsgBox.

Any suggestions?

 

[Graham Mandeno]

Hi Mary

That article is a very good reference, but it refers to child and grandchild
subforms, and it appears you have "siblings".

To refer to the Status control on Sub1 from Sub2, you need to go up one
level to the parent form first:

Me.Parent!Sub1.Form!Status

Problem 2 is a little trickier. You need to have a control on Sub2 that
contains zero only if ALL the Status fields are Null.

To do this, add a textbox named txtStatusCount to the header or footer of
Sub2. Set its Visible property to False and its ControlSource to:
=-Sum(Not IsNull([Status]))
This will contain a count of the non-null statuses on the form.

[Explanation: IsNull([Status]) returns a boolean: 0=false, -1=true. "Not"
inverts this, so Not IsNull([Status]) gives 0 if the Status is Null, or -1
if it is not. Adding these up (Sum) gives a negative count of the non-Null
statuses, and the "-" in front of "Sum" negates this to give a positive
count.]

Now, from Sub1 you can check:
If Me.Parent!Sub2.Form!txtStatusCount = 0 then ...