asked on

Reference Subform Control Value from Main Form

I want to reference a value on a subform from the main form so that I can make the subform not visible when empty. I have the following code but I get a runtime error #2427 "You entered an expression that has no value. The name of the Main Form is [2008 Joy Class Roster] the name of the subform is [Children]. What is wrong with how I've done this. I am using MS Windows Professional and MS Office Professional 2003.

If Forms![2008 Joy Class Roster].[Children]!ID = Me.ID Then
Me.Children.Visible = False
Else
Me.Children.Visible = True
End If

Open in new window

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Emil_Gray

ASKER

I need the value of the main forms ID field to be compared with the value of the subforms ID field. The subform can contain more than one record each with a different ID value.

Mike Eghtebas

re:> I can make the subform not visible when empty.

From the main form (in its current event for example)

MsgBox Me![Children].Form!ID

will give the value of ID in the subform. If ID in the subform doesn't exist, Me![Children].Form!ID is not seen as null or empty; it will be seen as an objevt (vartype 9).

Does this help?

Emil_Gray

ASKER

I tried your solution. The problem is that there can be multiple ID numbers in the subform since a set of parents can have more than one child. I need to reference the ID field in the subform. VarType doesn't work for me in this instance. The Main Form has all of the attendees for a Sunday School class. They also may be father, mother, spouse, son, daughter, brother, sister. I'm not worried about cousins thank goodness. I so far have the spouse subform working properly. When the father or mother of a child is the current record on the Main Form the Spouse subform becomes visible and shows the other spouse. I want the Children subform to become visible when either the father or mother is the current record on the Main Form but not when any child is the current record on the Main Form unless they also happen to be a parent in which case their children would be on a visible Children subform. I can write the code myself to accomplish this but I can't figure out how to reference the Children subform ID field directly..

Emil_Gray

ASKER

eghtebas,
I have decided to award you the points even though your solution doesn't exactly do what I wanted. The subform remains visible but no child records appear when the child is the main form current record.
Thanks for your help and have a great and happy Thanksgiving.

Emil_Gray

ASKER

This is the first time I've graded a question using this particular interface. I have commented on the question page and won't repeat that here.

Mike Eghtebas

If VarType(Me![Children].Form!ID) = 9 Then 'in case there was not child record for a giving parent record.
Me!Children.Visible = False
Else
IF me!ID = Me![Children].Form!ID then
Me!Children.Visible = True
Else
Me!Children.Visible = False
End IF
End If

I hope this takes care of your concern.

Thank you for the points and the grade.

Have a wonderfull Thanksgiving.

Mike

Emil_Gray

ASKER

eghtebas,
Thanks for the followup. I've got errands to run now to get ready for Thanksgiving tomorrow at my cousins. We'll have a little gathering of oh say 75 to 100 relatives for a get together. I'm making them all wear nametags otherwise I'm just calling everybody, "hey you".
LOL

Mike Eghtebas

It sounds you are going to have lots of fun. Enjoy it.

Mike

Emil_Gray

ASKER

eghtebas,
The code worked after I changed the Allow Additions and Allow Deletions from Yes to No. The problem was that the extra record at the bottom had an ID of 0 which caused the recordset to not meet the criteria of the code.