Synchronize TWO bombo boxes to One

[ajetrumpet]

I want to input the data by Form.

Combo boxes cannot cascade if they don't have any data to do it with.

I would like to select the League from a combo box (done "cboLeague"). I would like to select the Home Team and Away Team by combo box but both filtered by League selection.

Can only be done with data in the table (you don't have any in there yet, right??).

I would like the "Form Difference" box to fill itself using the difference between the values that I enter in the "Home Form", "Away Form" boxes . Same goes for those named; Index Difference, Current Index Difference, Trend Difference, Score Difference and Total Score.

This is no problem. This is probably what you meant by calculated fields. Populate those calculated values on any form event of your choice (using "scores" as an example here)...

Private Sub On_FormEventOfYourChoice

  Me.TotalScoreControl = Me.HomeTeamScoreControl + Me.AwayTeamScoreControl

Good to go now??

 

[jdncoke99]

I started this thread with 2 tables, tblResults, which hasn't changed and tblLeagues which had all 278 team names in one column and the League they play in, in the second column. It was suggested that I should get a primary key and to normalize my table. This I did and now tblLeagues is now two tables call tblLeague and tbl team, both are fully populated and will not be added to, they are for the sole purpose of filling the combo boxes. The League combo box works fine using the code I've entered in the Row Source as above, I just need the code to filter the other two combo boxes which are currently filled with all 278 teams, regardless of my league selection.

 

[ajetrumpet]

The League combo box works fine using the code I've entered in the Row Source as above, I just need the code to filter the other two combo boxes which are currently filled with all 278 teams, regardless of my league selection.

OK. I guess I should apologize then...

To write the SQL statement for both of the boxes, use the query builder. It will pop up when you click the 3 little dots (...) that you see on the right side of the rowsource line in the properties window of the form. You will need an inner join in the statement, so on the grid (query builder screen), make sure both the "teams" table and "leagues" table are showing. This will automatically create the join. All you have to do from that point is select the "teams" field from the "teams" table and write

tableLeague.League = Forms!YourForm!cboLeague

in the criteria line of the "teams" field. After you close the query builder, the SQL statement will appear in the Rowsource of your form's properties window.

Do this for both "team" combo boxes, and you should be ready to go.

 

[jdncoke99]

Fantastic!!!! Thank You so much )

 

[jdncoke99]

When I open the form, the last selection is still in cboLeague and cboHomeTeam. cboAwayTeam is blank. I can make a new selection from cboLeague, then select from the correct list of teams in cboHomeTeam. But when I drop-down cboAwayTeam it is populated by teams from the league that was in cboLeague when I first opened the form. Also if I select a League in cboLeague Then select a team in cboHomeTeam and I then change the league, the team list in cboHomeTeam remains that of the previously selected League.

 

[jdncoke99]

private sub cboLeague_onchange()
cboHomeTeam.Requery
cboAwayTeam.Requery
end sub

I'm Learning!!
Thanks anyway
kev

 

[ajetrumpet]

Congrats Kev. That's the way to do it.

Oh, and by the way, the method that you used is listed in the FAQ under method 4. Looks like you learned it on your own, but all the information you need is still there for you. Are you sure you can't gain the knowledge from it?