Changing Form's displayed Sub-Form by Combo-Box selection.

[IncidentalProgrammer]

I'm starting to think there's not a lot that Access can't do, but I can't find a clear answer on this one. Here's the scenario:



Among other things, my database tracks different "entities" that we do business with; clients, agencies, companies, etc. Because they all have a number of data fields in common, I combined the shared data needs into one main "Entities" table, and then made a table for each of them to have their individual data fields that aren't common to the others. In my forms to add/edit/view them, I want to use the same main form with their Entity data, and then use a sub-form to show the data that's specific to their entity type.

To use the "add new" form as an example, I have the Entity data all set up in the form, with an opening for the sub-form set up at the bottom. I want the user to select the Entity type from a combo-box above, and to have that determine which sub-form is displayed. So if they choose "Client" on the drop-down menu, the Client sub-form appears in the sub-form area, and so on. Can that be done?

I'm thinking VBA, but hoping there's an easier way built in already.

[ranman256]

In your combo have 2 columns EntityName, SubForm
when the user chooses the entity, cboEnt.value will be the subform to use

cboEnt_afterupdate()
childSub.sourceobject = cboEnt
end sub

[IncidentalProgrammer]

I love that. It looks SO much easier than what I thought I had to do.

I'm having some difficulties with it though. Still VERY new to all of this. Can you see what I'm doing wrong?

I went into the table that feeds the "Entity Type" field, and added the name of the appropriate subform to the column after the type name. So that only the type name would appear in the combo box, I then went and changed the sizes to 0;1;0. Then I entered this code:

Option Compare Database
Private Sub ctlEntType_AfterUpdate()
 'Make appropriate subform appear based on entity type selected.
subfrmSpecifics.SourceObject = ctlEntType
End Sub

My naming system is probably weird. "ctlEntType" is the combo box, and "subfrmSpecifics" is the subform space on the form.

Is it maybe a problem that my subform space is unbound? Since I want it to switch between a number of different subforms, I didn't think I should bind anything, but is that wrong maybe?

[ranman256]

make sure the combo box (usu named cboEntType) has the correct BOUND COLUMN.
and
you are assigning the name of the object on the form ,NOT the sub form name.
the subform is subfrmSpecifics
but the box containing the subform is usually named CHILDxx

[IncidentalProgrammer]

The bound column was wrong! It works! Thank you SO MUCH!!!

I had a feeling my naming structure was weird. subfrmSpecifics is what I named the space; the forms are subfrmClient, subfrmAccount, etc.