Changing the subform bassed on a combo box.

[mjones]

I know that this has been addressed before, but for the life of me, I cannot get it to work!

I am building and DB to record scores for a sporting club. We have a match each week, but the actual discipline is different, and so is the scoring system. I have a table (tblMatches) that contains the date, the discipline and comments. I then have different tables to record scores for each discipline (tblActionScores, tblServiceScores etc.)

I have a form that will be used to enter scores. The main form contains the fields for the tblMatches data. I want a subform that will change, depending on what MatchType is selected (frmActionScores when the combo box is "Action Metallic" for example.)

I have the following code under the AfterUpdate event on the combobox:

UG added code tags

Private Sub MatchType_AfterUpdate()
    Select Case Me.MatchType
        Case "Service"
    Me.subfrmScores.SourceObject = "frmServiceScores"
        Case "[Action Metallic]"
    Me.subfrmScores.SourceObject = "frmActionScores"
    End Select
End Sub

I just can't get the subform to actually change!

Thanks for any help

Matt

 

[June7]

Is the type "Action Match" or "Action Metallic"?

Do you actually have the [] characters in the combobox item?

 

[isladogs]

June has already covered my first point:
This is one place where you don't need the square brackets as the case statement is just checking what is inside the "" marks.
It can't find what you have so the second case event doesn't trigger
You could replace that line with Case Else.

I very rarely use the SourceObject code though it does work.
As an alternative which may load faster/more smoothly consider having 2 subforms only one of which is ever visible.
Set both hidden by default.
Then use code similar to this

Private Sub MatchType_AfterUpdate()

Me.subfrmActionScoresControlName.Visible=False
Me.subfrmServiceScoresControlName.Visible=False

Select Case Me.MatchType
Case "Service"
[INDENT]Me.subfrmServiceScoresControlName.Visible=True[/INDENT]
Case Else
[INDENT]Me.subfrmActionScoresControlName.Visible=True[/INDENT]
End Select
End Sub

Or possibly use a single subform whose record source is changed depending on the select case result

 

[theDBguy]

Hi Matt. Welcome to AWF! I am just curious to see your table structure. Can you please post a screenshot of your Relationships window? Thanks.

 

[Uncle Gizmo]

consider having 2 subforms only one of which is ever visible.

I don't agree with Colin in that you should use two subforms, it's usually a bad idea to duplicate similar forms. My guess is your subform(s) are practically identical but with a different record source. I agree with theDBguy's observation, I think much more information is required before for you can receive a useful answer.

 

[isladogs]

I don't agree with Colin in that you should use two subforms, it's usually a bad idea to duplicate similar forms. My guess is your subform(s) are practically identical but with a different record source.

Actually I agree with you. If that is the case I would definitely go with my other suggestion from my previous post

Or possibly use a single subform whose record source is changed depending on the select case result