Cascading Combo Boxes Problem

[pinkpanther6666]

Good Day to All,

Im having a problem in getting the value in combo box 2 depending what i select in combo box 1

The database info is listed below:

Form Name frmMain
Combo Box 1 Name Team
Combo Box 2 Name Name


Combo Box 1
This pulls data from a table called Team
This has 2 fields with the table
They are called
ID
TeamName

I want to select TeamName

Combo Box 2
I want to pull data in to this combox from another table called Player
This table has 4 fields
ID
Team
PlayerName
Sex


What i want to do is to select the team in combo box 1 and in combo box 2 i want all the players names that play for that team


i Hope i have explained this as best as i can can

Any problems please dont hestitate to contact me



Many Thanks




Steve

 

[ranman256]

To do the multi-combo box
each combo box has a query for its recordsource.
Here the user picks a Team, then in the next combo picks a Player (in that team)

The 1st combo,(say cboTeams)... User picks a Team, then picks a Player from the cboPlayers box.
The cboTeams AfterUpdate event will trigger when the user picks it, and this will update the next combo.

sub cboTeams_AfterUpdate()
  cboPlayers.requery
end sub

The cboPlayers query (say qsPlayersInTeam) will reference the cboTeams in the query sql
Select [players] from Teams where [team] ='" & forms!frmMain!cboTeams & "'"

The Player combo must be refreshed (cboPlayers.requery action) after user picks the team so it can deliver the resulting dataset.

If there is another combo after this then the cboX must be refreshed after user picks cboPlayers.

sub cboPlayers_AfterUpdate()
 cboXXX.requery
end sub

 

[pinkpanther6666]

Ranman256


Many thanks for your reply,
i will have a go at what you have recomended and let you know

many thanks


steve

 

[pinkpanther6666]

ranman256

I dont know if its me but i cant get this to work at all

grrrrrrrrrrrr


Steve

 

[pinkpanther6666]

Ranman256

Access keeps popping up syaing theres a problem with

[team] ='" & forms!frmMain!cboTeams & "'"



Regards


Steve

 

[alansidman]

This link may be helpful

Access Tips: Cascading Lists for Access Forms

 

[pinkpanther6666]

All,
Many thanks for the replies and this is now sorted



Steve