Is it possible to have a different subform appear when a value is chosen on a combo box. Let's say we have these values in a field called Type: Application, Evaluation, Complaint. When I select Application, I would like to see the application form fields in the subform. If I select Evaluation, I want it to load a different form in the subform. Is this possible? If so, then how?

Why not just use tabbed subforms. The user can select the tab they want instead of using the other selection method. Put a different subform on each tab.

Did that help?

mmcdonal

I tried the tabbed forms. When I put the entry in the form I placed on one tab, it also tried to put an entry on the other form that I had on the other tab. I guess that's because both forms are joined to the same mother table. Is there a way I can tell Access to put the entry only into the one form and not the other?

Just to be clear, I do need the form/subform structure. Simply putting forms on different tabs will not work.

This sounds like an underlying table structure issue. Can you post the table structures, and the record source for the main and sub forms?

mmcdonal

On the previous messages, I had used other field names to try to simplify things. Here are the actual field names and forms I am using.

[u]This is in my main form.</u>
Record Source - TblTrainingNew
Name of Table: TblTrainingNew
TrainingNumber - PK - Autonumber - Long integer
Type - Text

[u]This is in my first subform.</u>
Record Source - tblZone
Name of Table: TblZone
ZoneNumber - PK - Number - Long integer
Zone - Text
TrainingNumber - FK - Number - Long integer

[u]This is my second subform with the two tabs.</u>
On the first tab is this subform.
RecordSource of subform - tblInstitutional
Name of Table: TblInstitutional
ZoneNumber - FK - Number - Long integer
Field 1
Field 2, etc.
On the second tab is this subform.
Record Source of subform- tblInstitutional
Name of Table: TblRisk Inventory
ZoneNumber - FK - Number - Long integer
ID - Number
Zone - Text
CreationDate - Date, etc.

Just thinking out loud, but should I be going to each individual field and giving it the correct [u]control </u>source? I can't figure out how I can give the subform two different [u]record </u>sources, one for each tab.

From what you've posted above the simplest solution i can think of is an unbound subform with an option group as the source. The main form will load alot faster, the most important part is to ensure that the master and child fields are going to be the same for each; other wise it gets a little bit more fun to correct.

The form which the copied code below belongs to (experts feel free to suggest improvements to make it run faster) there is a tab control and a subform sized to exactly the same size and position on the form. As the user presses on of the buttons (toggle, option, check) the subform changes to the relevant one. Each of the tables running the sources has adviserid as a link to the adviser table and adviserid is set as the child and master link fields in the unbound subform. The tab control simply contains information from the adviser table itself.

as you can see option 2 hasn't been written yet

Private Sub optadvisers_AfterUpdate()

    On Error GoTo errorhandler

    Select Case Me.optadvisers

    Case 1
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        'switch to the ar history form
        If Me.subarform.SourceObject <> "fsubadviserfirmhistory" Then
            Me.subarform.SourceObject = "fsubadviserfirmhistory"
        End If
        Me.subarform.Requery

    Case 2
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True

    Case 3
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        'switch to the ar assessments form
        If Me.subarform.SourceObject <> "fsubarassessments" Then
            Me.subarform.SourceObject = "fsubarassessments"
        End If
        Me.subarform.Requery

    Case 4
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        If Me.subarform.SourceObject <> "fsubadvisercaseslist" Then
            Me.subarform.SourceObject = "fsubadvisercaseslist"
        End If
        Me.subarform.Requery

    Case 5
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True

    Case 6
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True

    Case 7
        'hide the tabcontrol
        Me.tbadvisers.Visible = False
        'make the subform visible
        Me.subarform.Visible = True
        'switch to development plans
        If Me.subarform.SourceObject <> "fsubadviserdevelopmentplans" Then
            Me.subarform.SourceObject = "fsubadviserdevelopmentplans"
        End If
        Me.subarform.Requery

    Case 8
        'make the tab control visible
        Me.tbadvisers.Visible = True
        'empty the subform source
        Me.subarform.SourceObject = ""
        'hide the subform
        Me.subarform.Visible = False

    End Select

exitopt:
    Exit Sub

errorhandler:
    generalerrorhandler Err.Number, Err.Description, fadv, "optadvisers_afterupdate"
    Resume exitopt
    Resume

End Sub