how to fetch data from a combobox using select?

[hedub]

I want to retrieve data from another combobox using a select statement. E.g you have a table for teams, lets say football. By using two comboboxes you select the teams that are going to play (registered in a table called match), and by doing so I want to the players (retrived from table players) that are playing on those two teams to appear in another combobox. How do I do this? ..

 

[GHolden]

Assuming your two tables were tblPlayer and tblTeam

tblTeam
TeamID
Team

tblPlayer
PlayerID
TeamID
Player

If you have a combo box on your form with tblTeam as the rowsource (eg. cboTeam).

And a listbox with 2 cols (eg. lstPlayer)

In the on click event of your combobox

lstPlayer.RowSource = "SELECT PlayerID,Player FROM tblPlayer WHERE TeamID = " & cboTeam
There are two ways to write error-free programs; only the third one works.

 

[scriverb]

Assuming your three tables tblMatchs, tblPlayers and tblTeams
tblMatchs
HomeTeam
AwayTeam
HomeTeamID
AwayTeamID
tblTeam
TeamID
TeamName
tblPlayer
PlayerID
TeamID
Player
On your form is a combobox cboMatch with 4 columns. The RowSource would be tblMatchs or a query with the 4 columns. If a query the 4 fields should be in the same order but the records can be sorted in whatever order you determine possibily DateOFGame(just add another column). Also, just make visible the first two columns which would be the team names. The teamID's would be there but the column widths set to 0 so they would not be visible in the dropdown.

In the AfterUpdate of the Combobox:
me.lstPlayers.requery

Your ListBox (lstPlayers) should have the following as the RowSource:
"SELECT tblPlayers.PlayerName, tblTeams.TeamName FROM tblPlayers INNER JOIN tblTeams ON tblPlayers.TeamID = tblTeams.TeamID WHERE tblPlayers.TeamID = " & cboMatch.column(2) & " tblPlayers.TeamID = " & cboMatch.column(3) & "ORDER BY tblPlayers.PlayerName;"

Let me know if this is what you were looking for. Bob Scriver