Subsubform -- How do I programmatically show and hide fields?

[painterz]

Hi All,

I have code that's not working and decided maybe it's me (and not the code). Based on the result of a combo box on a subsubform (Non child/child), I'd like to show and hide fields. i.e. I'd show an anniversary date for an adult rather than a birth date for a child.

This worked when my form was a subform but now that it's buried one layer deeper into another subform--it no longer works.

Do I refer to the subsubform as a form or control to change the fields? Neither seems to be working. I would think the fields exist with the subsubform rather than it's container the control.

Also, can I refer to the subsubform directly,

forms("Copy of Neighborhood Input Form").Control("Family).form.control("Neighbors").form

or do I need to dimension a form and control and set them?

I've tried all of the above in different arrangements and nothing seems to work. I've been able to recognize my form and it's recordsource but then I get an error message that the field doesn't exist (none of the fields are recognized). If I'm on the right form at the right level, why wouldn't I be able to see the fields? Like I said earlier, this worked until I buried my subform one layer deeper.

Thanks!

 

[CJ_London]

Do I refer to the subsubform as a form or control to change the fields?
...
...
Also, can I refer to the subsubform directly,

yes, but the how depends on where from - another form, a main form to one of its subforms? a subform to another subform on the main form? vba code?

this link may help

http://access.mvps.org/access/forms/frm0031.htm

 

[painterz]

Hi CJ,

Thanks, I've been to that link--it seems to be a popular reference. I referred to it trying to solve my problem. I'm using a VBA function so I can call it from different areas on the main and subsubform.

 

[CJ_London]

because you have spaces in your form names, you need to use square brackets

you are missing quotation marks

and you need to refer to collections (forms, controls) in the plural

 

[painterz]

Hi CJ,

I've tried this as well:

Forms![Copy of Neighborhood Input Form]!Family.Form!Neighbors.Form

and it didn't work. Do I need quotes within the brackets?

Thanks

 

[CJ_London]

the code is still not as advised on the link. You have

Forms![Copy of Neighborhood Input Form]!Family.Form!Neighbors.Form
the nearest I can see on the link is
Forms!Mainform!Subform1.Form!Subform2.Form!ControlName

as previous advised what you use depends on where the subform is in relation to where the code is. So even when you get the above right it may not work if you are not clear about that.

 

[painterz]

Hi CJ,

I'm using this in an IF statement. Someone suggested I wrap it with a WITH statement. My intended syntax is the control names are within the IF statement so I can set the values depending on whether the person shown is an adult or child:

With Forms![Copy of Neighborhood Input Form]!Family.Form!Neighbors.Form
If !PKRelationship = 3 Then 'here are my control names
!lblBirthday.Visible = True
!txtBirthday.Visible = True
…

End If
End With

If what I have above is correct, it's still failing when the subsubform loads. The code is in a public function called ControlsVisible().

The error message is "Neighborhood Database can't find the field 'PKRelationship' referred to in your expression."

PKRelationship is the control source for the cboRelationship. If I comment out the IF structure and go to the next line, it fails as well saying it can't find the next field and so on.

Before I added the subform to a subform, I wasn't using the WITH statement, everything worked fine. I had dimensioned my variables set the main form as a form and the subform as a control. When I tried that with the new structure, I can't get it to work.

Ideas?

Thanks

 

[painterz]

I don't know if this helps are not. This was my latest attempt. I've tried sfrmFamily as both a control and a form.

Public Function ControlsVisible()

Dim frmNeighborhood As Form
Dim sfrmFamily As Form
Dim sfrmNeighbors As Control

'Set frmNeighborhood = Forms("Copy of Neighborhood Input Form")
'Set sfrmFamily = Forms("family subform")
'Set sfrmNeighbors = frmNeighborhood!sfrmFamily.Form![Neighbors]

'Forms!frmNeighborhood!sfrmFamily.Form![Neighbors].Form
'    With forms("Copy of Neighborhood Input Form").Control("Family).form.control("Neighbors").form
'       With Forms![Copy of Neighborhood Input Form]!Family.Form!Neighbors.Form
With Forms![Copy of Neighborhood Input Form]!Family.Form!Neighbors.Form
If !PKRelationship = 3 Then 'viewing a child
    !lblBirthday.Visible = True
    !txtBirthday.Visible = True
    !lblanniversary.Visible = False
'    sfrmNeighbors!Anniversary.Visible = False
'    sfrmNeighbors!Phone.Form.Visible = False
'    sfrmNeighbors!TeenJobs.Form.Visible = True
'    sfrmNeighbors!ckParentOK.Visible = True
'    sfrmNeighbors!lblConsent.Visible = True
'    sfrmNeighbors!txtchildphone.Visible = True
'    sfrmNeighbors!lblchildphone.Visible = True
'    sfrmNeighbors!lblList.Visible = False
'    sfrmNeighbors!ckList.Visible = False
'    sfrmNeighbors!lblOwner.Visible = False
'    sfrmNeighbors!ckowner.Visible = False
'    sfrmNeighbors!lblRental.Visible = False
'    sfrmNeighbors!ckRental.Visible = False
'    sfrmNeighbors!lblOwes.Visible = False
'    sfrmNeighbors!ckowes.Visible = False
'    sfrmNeighbors!lblemailaddress.Visible = False
'    sfrmNeighbors!EmailAddress.Visible = False
'    sfrmNeighbors!lblmaritalstatus.Visible = False
'    sfrmNeighbors!cboMaritalStatus.Visible = False
'    sfrmNeighbors!lblvolunteer.Visible = False
'    sfrmNeighbors!ckvolunteer.Visible = False
'    sfrmNeighbors!lblTT.Visible = False
'    sfrmNeighbors!ckTT.Visible = False
'    sfrmNeighbors!lblnewsletter.Visible = False
'    sfrmNeighbors!ckNewsletter.Visible = False
'    sfrmNeighbors!lblListEmail.Visible = False
'    sfrmNeighbors!ckListEmail.Visible = False
'    sfrmNeighbors!lblOptin.Visible = False
'    sfrmNeighbors!ckOptin.Visible = False
'    sfrmNeighbors!lblSource.Visible = False
'    sfrmNeighbors!cboSource.Visible = False
'    sfrmNeighbors!lblSourceYear.Visible = False
'    sfrmNeighbors!txtsourceyear.Visible = False
'    sfrmNeighbors!lblWelcomeComm.Visible = False
'    sfrmNeighbors!ckwelcomecomm.Visible = False
'

Else
'    sfrmNeighbors!lblList.Visible = True
'    sfrmNeighbors!ckList.Visible = True
'    sfrmNeighbors!lblOwner.Visible = True
'    sfrmNeighbors!ckowner.Visible = True
'    sfrmNeighbors!lblRental.Visible = True
'    sfrmNeighbors!ckRental.Visible = True
'    sfrmNeighbors!lblOwes.Visible = True
'    sfrmNeighbors!ckowes.Visible = True
'    sfrmNeighbors!lblemailaddress.Visible = True
'    sfrmNeighbors!EmailAddress.Visible = True
'    sfrmNeighbors!lblmaritalstatus.Visible = True
'    sfrmNeighbors!cboMaritalStatus.Visible = True
'    sfrmNeighbors!lblBirthday.Visible = False
'    sfrmNeighbors!txtBirthday.Visible = False
'    sfrmNeighbors!lblanniversary.Visible = True
'    sfrmNeighbors!Anniversary.Visible = True
'    sfrmNeighbors!TeenJobs.Form.Visible = False
'    sfrmNeighbors!Phone.Form.Visible = True
'    sfrmNeighbors!ckParentOK.Visible = False
'    sfrmNeighbors!lblConsent.Visible = False
'    sfrmNeighbors!txtchildphone.Visible = False
'    sfrmNeighbors!lblchildphone.Visible = False
'    sfrmNeighbors!lblConsent.Visible = False
'    sfrmNeighbors!lblvolunteer.Visible = True
'    sfrmNeighbors!ckvolunteer.Visible = True
'    sfrmNeighbors!lblTT.Visible = True
'    sfrmNeighbors!ckTT.Visible = True
'    sfrmNeighbors!ckNewsletter.Visible = True
'    sfrmNeighbors!lblnewsletter.Visible = True
'    sfrmNeighbors!lblListEmail.Visible = True
'    sfrmNeighbors!ckListEmail.Visible = True
'    sfrmNeighbors!lblOptin.Visible = True
'    sfrmNeighbors!ckOptin.Visible = True
'    sfrmNeighbors!lblSource.Visible = True
'    sfrmNeighbors!cboSource.Visible = True
'    sfrmNeighbors!lblSourceYear.Visible = True
'    sfrmNeighbors!txtsourceyear.Visible = True
'    sfrmNeighbors!lblWelcomeComm.Visible = True
'    sfrmNeighbors!ckwelcomecomm.Visible = True

End If
    End With

'Set sfrmNeighbors = Nothing
'Set frmNeighborhood = Nothing

'txtAddress.SetFocus
End Function

 

[MarkK]

I think code that modifies the appearance of a user interface element should reside on the object it modifies. If a form needs to modify it's appearance, it should know how to do so for itself, and if external consumers need to be able to trigger that methodology, then it should be exposed as a public method. This approach will simplify your life considerably, and make your code much more robust.

By reaching into an object from the outside, as you are doing with your long chained references to named members of various collections (Forms, Controls), you are removing the ability of the compiler to type-check your objects, and adding unnecessary complexity to your task, IMO.

 

[jdraw]

painterz,

Do you have a good description of WHAT you are trying to accomplish? If so, can you share it with readers. It seems to me, and I have often misunderstood a poster's intent, that you are using more of a trial and error approach to something than a defined strategy.

 

[painterz]

Hi Mark,

Thanks for you opinion. Would you please dumb it down for me? You're the professor and I'm the student in the back of the class :0

 

[CJ_London]

this

With Forms![Copy of Neighborhood Input Form]!Family.Form!Neighbors.Form
If !PKRelationship = 3

is correct - as far as syntax goes - I presume family and neighbors are the names of the subform controls (not the subform sourceobject, although they may be the same)

so the issue is around the calling function - you say it is a public function, but despite several suggestions you have still not said where that function is (form module, general module, another form) - and now I see more of the code when that function is run.

take a look at this link

https://support.office.com/en-us/ar...ce86553682f9?ui=en-US&rs=en-US&ad=US&fromAR=1

and find the relevant section for forms and reports. It may be that when you are running it, the data is not there

 

[painterz]

Hi J,

I'm basically throwing spaghetti at the wall and seeing what sticks. I had to retrofit my original design to accommodate a needed change and nothing's worked since then. My goad is to see all of the people, in all of the families, living in one address. Within those families I have info on the children (teen jobs) and adults (phone numbers, notes, etc). The point of the massive IF statement is so when I look at a record for a child, I'm not seeing the fields used for the adults and vice versa. Here is a screen shot of what my form used to look like until I went through this upheaval.

Thanks

 

[jdraw]

Can you post a jpg of your relationships window showing your tables and relationships?

 

[MarkK]

This code . . .

!lblBirthday.Visible = True

. . . should reside on the form that contains the object "lblBirthday".

That form should expose a public method, maybe . . .

Public Sub ControlsVisible
   If Me.PKRelationship = 3 Then
      Me.lblBirthday.Visible = True
      Me.txtBirthday.Visible = True
      Me.lblanniversary.Visible = False
   Else
      Me.lblBirthday.Visible = False
      Me.txtBirthday.Visible = False
      Me.lblanniversary.Visible = True
   End If
End Sub

. . . which is simple, robust, and type-checked by the compiler. If this code compiles, it is very very very likely to run without error.

Then, if that form is ever used as a subform, the parent form can simply call the ControlsVisible method, and get the subform to do the work FOR ITSELF. So the parent form would do . . .

Public Sub ControlsVisible
[COLOR="Green"]   'calls the public method of the subform in the control[/COLOR]
   Me.MySuformControl.Form.ControlsVisible
End SUb

Now, if that form is ever used as a subform, its Parent Form (yeah you guessed it) can just call the subform's ControlsVisible method. This way each object is responsible for its own presentation.

This way you can snap your subforms in and out without dependencies. Is that clearer?

 

[painterz]

Lightbulb!

Thanks CJ!!!

I'm still not clear about your question of 'where' my function is stored. It's in the VBA section of my db. I have it in a Utilities module, so that means outside of the form--right?

However, looking over your question--you're right! I'm calling the function from the main form's onload, onopen, and onactivate properties, so my subsubform isn't even loaded yet. I'll make the change and see if it solves my problem.

YAY YOU!

 

[painterz]

Mark--Much, much clearer and I think it will resolve the issue CJ brought to my attention too.

J, I don't have relationships set between my tables but I have queries. I've posted the query underlying subsubform Neighbors.

I might actually make head way today!

 

[painterz]

Yay! No error messages but my subsubform won't load so I don't know which fields are visible. I've already posted about this issue in Forms forum.

Ya'll rock!

 

[jdraw]

I'm calling the function from the main form's onload, onopen, and onactivate properties, so my subsubform isn't even loaded yet.

From M$oft:
When you open a form that contains a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form.

 

[painterz]

Scratching my head…

J, then why didn't anything work? I was calling the function from the main form which means the subsubform should have been loaded.

Before, I could see the form and subform loaded, but the function bombed on the subsubform. When I can get the subsubform to load, I'll be able to see whether I can call this a success or continuing saga (more like epic drama).