Filter with multiple Combo boxes

[dotcanada]

Good day.

I search high and low, tried various examples I had found, but I can't seem to accomplish this.

I have my main form (frmTeamRoster) with two combo boxes:
- Name of 1st box = "Team", row source is a list of teams from tblTeams (SELECT tblTeams.Team FROM tblTeams ORDER BY Team)
- Name of 2nd box = "Season", row source is a list of teams from tblSeasons (SELECT tblSeasons.Season FROM tblSeasons ORDER BY Season)

On the main form, is a sub form (subfrmTeamRoster) which is in datasheet view where its Record Source is from qryTeamRoster. In this query, both "Team" and "Season" fields come from tblPlayerStats. Other fields within this query are (Player) "Name", (Jersey) "Number", and "Position" which comes from tblPlayerInfo.

I'm able to filter the sub form by Team using the Team combo box, but I can't break down the datasheet further by Season. The datasheet results give me the Team roster according to what the Team combo box indicates, but gives me all the players regardless of the Season (ex. 2015-16, 2016-17, etc).

Any help is appreciated.

[ranman256]

subfrmTeamRoster form should be connected to the master form via cboTeam and cboSeason
subfrmTeamRoster properties:
LINK MASTER FIELDS: cboTeam;cboSeason
LINK CHILD FIELDS:cboTeam;cboSeason

once you change the combos, the subform data should change automatically.

[dotcanada]

This didn't do it. But I did change your suggestion to this:
Link Master Fields: Team;Season
Link Child Fields: Team;Season

But this also didn't work. One thing to point out is I have nothing in After Update events in the combo boxes. If this is required, what would I put?

[dotcanada]

I got it!!!

I left the Master and Child Fields both as "Team".

Then in the Season combo box's After Update event, I used the following:

Private Sub Season_AfterUpdate()
Me.subfrmTeamRoster.Form.Filter = "Season=""" & Me.Season & """"
Me.subfrmTeamRoster.Form.FilterOn = True
End Sub

Works like a charm.