cascadingcombos 1

[Greg553]

Hello, I know this question has been asked and I have search all the posts on
the subject.I just can't seem to get this right.
What i need to do is store the values chosen from each combo box.
This Is what I have.

tbl_player information ( where all information on player is stored )
tbl_sportsidentification (list of sports )
tbl_sportspositions (list of all sport positions linked to
tbl_sportsidentification)

tbl_sportidentification has fields of
sports_ID
Sports

tbl_sportspositions has fields of
position_ID
sport_ID
positions

the tables are linked by the sport_ID

I have two combo boxes

cbxsport1
cbxposition1

I want to pick a sport in cbxsport1 ( and store that value in player
information under sport)
and have the second combo box cbxposition1 show only the positions under the
sport chosen in the first combo box.

I have tried all the examples, using tables and query's and still can not get
it right.

any help would be most appreciated.

this is an example of a query example i used.

Private Sub Sport1_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!Sport1
strSQL = strSQL & " from sport "
Me!Position1.RowSourceType = "table/query"
Me!Position1.RowSource = strSQL
Me!Position1.Requery

End Sub

 

[dhookom]

Assuming Sport_ID is numeric, try:

Private Sub Sport1_AfterUpdate()   
    Dim strSQL As String   
    strSQL = "Select PositionID, Positions " 
    strSQL = strSQL & " from tbl_sportspositions " 
    strSQL = strSQL & "WHERE  sport_ID = " & Me.Sport1
    strSQL = strSQL & " ORDER BY Positions"  
    Me!Position1.RowSourceType = "table/query" 
    Me!Position1.RowSource = strSQL  
End Sub

Do you actually have field names like Sport1 and Position1? If so, you might need to read up on normalization.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

thanks,
this is the way it is set up...

I have a tbl_sportsidentification
with fields of sports_ID and Sports
EX.. sport_ID Sports
1 football
2 basketball
3 Baseball
4 Track
and so on


2nd table is tbl_sportspositions
Position_ID sport_ID Positions
1 1 QB
2 1 RB
3 1 WR
4 2 Forward
5 2 Center

And so on.

each sport has many positions and depend on the sport ID to know which sport they belong to.

 

[Greg553]

What i'm trying to do is in the first combo box pick a sport and the second combo box show only the related positions to that sport only

 

[dhookom]

Did you try the code I suggested?

I wasn't concerned about normalization of the two tables you provided the structure for. I am more interested in tbl_playerinformation and its structure.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

the structure for that is

tbl_playerinformation

Player_ID
Lastname
Firstname
Date entered
height
weight
Grade
sport1
position1
sport2
position2
sport3
position3


the sports and positions are for multi sport athletes

 

[dhookom]

You still didn't answer my question about the code. Did I waste my time typing it in?

Your table structure is not normalized. A normalized table structure would have a PlayerSport table with one record per athlete per sport. If a player played 3 sports, there would be three records in the PlayerSport table.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

Yes I did type it in. second combo will not come up with choices.
I'm going over everything to try and learn before asking to many questions.
I know there is one thing i'm missing some where.

I understand what you are saying now on that table on sports and normalization.

thanks for the help, i 'm going to work on it and see if i can get it now.

 

[dhookom]

I would try to place a break point in the code and see how the value of strSQL changes.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

Thanks dhookom,

Got it to work, i took your code and just changed the tables to a query ( just for my sake) table code worked also..
Believe it or not came down to me miss spelling one word

thanks for all your help.

here is the code....

Dim strSQL As String
strSQL = "Select Sport_ID, Positions "
strSQL = strSQL & " from qrysportspositions "
strSQL = strSQL & "WHERE sport_ID = " & Me.Sport1
strSQL = strSQL & " ORDER BY Positions"
Me!Position1.RowSourceType = "table/query"
Me!Position1.RowSource = strSQL