Confused about how to make subforms visible/invisible

[Robert Landers via AccessMonster.com]

Hi all,

I have a main form called "Contacts". This form has a combo-box on it,
called "PersonTypeID", which contains a lookup field with the available
values being "Individual" and "Company".

I also have two subforms called "Individuals" and "Companies".

At present, I have set the "visible" properties of both subforms to "No".

I want to set up the form so that when I select "Individual" from the combo-
box, it sets the visible property of the "Individuals" subform to "Yes".

I have tried the following steps but keep running into errors:

1. Select the combo box
2. Go to Events properties
3. Select OnChange event ...
4. Select Expression Builder
5. Go to Forms, Loaded Forms, Contacts, Individuals
6. Doubl-click on the "visible" property and set it to "False", giving me
the following expression: [Individuals].Form.Visible=True

But this results in the following error when I select from the drop-down
menu: "Microsoft Access can't find the macro 'Individuals'".

As, an alternative, I follow steps 1 to 3 above, and select "Code Builder"
instead of "Expression Builder". This opens a code window which I alter to
read as follows:

Private Sub PersonTypeID_Change()
Me.Individuals.Visible = True
End Sub

At this stage, I'm leaving out the If/Then statements just to see if I can
change the visible property value. Even so, I keep running into this error:

"The expression On Change you entered as the event property setting
produced the following error: A problem occurred while Microsoft Access was
communicating with the OLE server or ActiveX Control."

To be honest, I would have thought what I'm trying to achieve would be very
simple. Can anyone give this newbie some guidance?

TIA
Robert

 

[tina]

This form has a combo-box on it,

called "PersonTypeID", which contains a lookup field with the available
values being "Individual" and "Company".

first, if you have a lookup field in a *table*, suggest you get rid of the
lookup *in the table* - they cause a lot of problems. for more info, see
http://www.mvps.org/access/lookupfields.htm

to get rid of the lookup, open the table in design view, select the lookup
field, click on the Lookup tab under Field Properties, and change Display
Control to Text Box.

next, to hide/unhide a subform control in a mainform, you need to make sure
you're refering to the name of the subform *control* in the code, not the
name of the subform object - sometimes they're the same, but sometimes not.
to find the subform control name: open the main form in design view, click
on the subform (within main form design view) to select it, open the
Properties box, click on the Other tab, and look at the Name property.
that's the subform control name.

to show/hide a subform control based on the entry in a combo box on the main
form: do your steps 1 and 2, but select the AfterUpdate event in step 3,
and select Code Builder in step 4. a standard If...Then statement should
work fine.

one point, though: the comparison expression in the If...Then statement
must look at the *bound* column of the combo box. if your combo box has only
one column with the values "Individuals" and "Companies", no problem. but a
multi-column combobox usually (not always) has the bound column hidden, and
shows a value from another column in the droplist. (if you do have a
multi-column combo box, and have trouble getting the code to work, post back
with the RowSource, ColumnCount, ColumnWidths, and BoundColumn values from
your combo box. i'll help you fix it.)

hth

Hi all,

I have a main form called "Contacts". This form has a combo-box on it,
called "PersonTypeID", which contains a lookup field with the available
values being "Individual" and "Company".

I also have two subforms called "Individuals" and "Companies".

At present, I have set the "visible" properties of both subforms to "No".

I want to set up the form so that when I select "Individual" from the combo-
box, it sets the visible property of the "Individuals" subform to "Yes".

I have tried the following steps but keep running into errors:

1. Select the combo box
2. Go to Events properties
3. Select OnChange event ...
4. Select Expression Builder
5. Go to Forms, Loaded Forms, Contacts, Individuals
6. Doubl-click on the "visible" property and set it to "False", giving me
the following expression: [Individuals].Form.Visible=True

But this results in the following error when I select from the drop-down
menu: "Microsoft Access can't find the macro 'Individuals'".

As, an alternative, I follow steps 1 to 3 above, and select "Code Builder"
instead of "Expression Builder". This opens a code window which I alter to
read as follows:

Private Sub PersonTypeID_Change()
Me.Individuals.Visible = True
End Sub

At this stage, I'm leaving out the If/Then statements just to see if I can
change the visible property value. Even so, I keep running into this error:

"The expression On Change you entered as the event property setting
produced the following error: A problem occurred while Microsoft Access was
communicating with the OLE server or ActiveX Control."

To be honest, I would have thought what I'm trying to achieve would be very
simple. Can anyone give this newbie some guidance?

TIA
Robert

 

[Robert Landers via AccessMonster.com]

Thanks for taking the time to respond Tina.

In the end, I resolved this problem simply by re-saving my form under a new
name. I have no idea what was going wrong.

I've left everything the way it was before, but replaced the code in the
onchange event with the following:

Private Sub PersonTypeID_Change()
If Me.PersonTypeID.Value = 1 Then
Me![Companies].Visible = False
Me![Individuals].Visible = True
Else
Me![Individuals].Visible = False
Me![Companies].Visible = True
End If
End Sub


Thanks again
Rob