Make subform visible when a field has a particular value

[JJ1109]

Hi,

I have a field in my form that is a text field, but will only be entered as
Y or N. If the field is Y, I want a subform to be displayed, if it's N the
subform can stay hidden. I can't work out exactly how / what to have the
subform bound to to make this work.

thanks
JJ

 

[Jeanette Cunningham]

Use the link master field and link child field to link the subform to the
main form.
Use the after update event of the textbox to show or hide the subform.
Use the current event to show or hide the subform when opening an existing
record for editing.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[JJ1109]

Thanks Jeanette,

So in the subform I need the same field that's the master field in the main
form, to be the child?

How do I write the expression to make the subform be visible or not? I have
no idea how to write them

thanks again
JJ

 

[Jeanette Cunningham]

If you use the wizard to put the subform on the main form, access will give
you a list of choices for the master and child link fields.

To show the subform, the code is-->
Me.[NameOfSubformControl].Visible = True

To hide it
Me.[NameOfSubformControl].Visible = False


Replace NameOfSubformControl with the correct name for your form.
Note that the name of the subform inside the subform control may not be the
same as the name of the subform control.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[BruceM via AccessMonster.com]

To expand a little on what Gina wrote, you need both expressions at once:

If Me.[YourField] = "Y" Then
Me.[NameOfSubformControl].Visible = True
Else
Me.[NameOfSubformControl].Visible = False
End If

To simplify the expression, maybe something like:
Me.[NameOfSubformControl].Visible = (Me.[YourField] = "Y")

Since (Me.[YourField] = "Y") evaluates either to True or False, it takes the
place of the word True or False in the expression. One line of code covers
both possibilites. This assumes there are just two possibilites for
YourField.

To place the code in the text box After Update event, in form design view
click the text box to select it, then click View >> Properties. This will
display the Property Sheet (if it was not already displayed), with tabs for
Format, Data, Event, Other, and All. Click the Event tab, click After Update,
click Code Builder >> OK. This will open the VBA editor, with Private Sub
and End Sub lines. Place the code between the two. Same for the form's
Current event. To switch to the Property Sheet for the form, click the
little square on the top left where the rulers meet in design view (if the
Property Sheet is displayed). If it is not displayed, double click that
little square, or click it once and click View >> Properties.

In any case, the Property Sheet title bar will tell you what properties you
are seeing.

Thanks Jeanette,

So in the subform I need the same field that's the master field in the main
form, to be the child?

How do I write the expression to make the subform be visible or not? I have
no idea how to write them

thanks again
JJ

Use the link master field and link child field to link the subform to the
main form.

[quoted text clipped - 16 lines]

.