 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

September 4th, 2007, 09:43 PM
|
Authorized User
|
|
Join Date: Sep 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Combo box choice creating filtered combo box
Let's say we have a field called league and another field called teams. The league field has two values: National and American. The teams field has all the teams in both leagues. What I want to happen is that when I select American in the league combo box, the team combo box will only show me the American League teams.
Any idea of how you do this?
|

September 5th, 2007, 10:18 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
These are called cascading combo boxes, and this technique is also used for cascading list boxes. The first issue is, what is the underlying table structure?
Ideally, in your example, you would have two tables:
tblLeague
LeagueID = PK integer
LeagueName - text
tblTeam
TeamID - PK integer
TeamName - text
LeagueID - FK
The first combo box (cboLeague) control source would be a query:
SELECT LeagueID, LeagueName FROM tblLeague ORDER BY LeagueName
The On Change event of cboLeague would have this code:
Dim sSQL As String
Dim iLeague As Integer
iLeague = Me.cboLeague
sSQL = "SELECT TeamID, TeamName FROM tblTeam WHERE [LeagueID] = " & iLeague
Me.cboTeam.RowSource = sSQL
Me.cboTeam.Requery
Be sure to set up the cboTeam with the following:
Bound Column: 1
Column Count: 2
List Width: 0";2"
This will make sure that the meaningful data is displayed, but that the PK TeamID is bound for future operations.
Did that help?
mmcdonal
|

September 5th, 2007, 10:46 AM
|
Authorized User
|
|
Join Date: Sep 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I figured out that PK means primary key. What does FK mean?
Quote:
quote:Originally posted by mmcdonal
These are called cascading combo boxes, and this technique is also used for cascading list boxes. The first issue is, what is the underlying table structure?
Ideally, in your example, you would have two tables:
tblLeague
LeagueID = PK integer
LeagueName - text
tblTeam
TeamID - PK integer
TeamName - text
LeagueID - FK
The first combo box (cboLeague) control source would be a query:
SELECT LeagueID, LeagueName FROM tblLeague ORDER BY LeagueName
The On Change event of cboLeague would have this code:
Dim sSQL As String
Dim iLeague As Integer
iLeague = Me.cboLeague
sSQL = "SELECT TeamID, TeamName FROM tblTeam WHERE [LeagueID] = " & iLeague
Me.cboTeam.RowSource = sSQL
Me.cboTeam.Requery
Be sure to set up the cboTeam with the following:
Bound Column: 1
Column Count: 2
List Width: 0";2"
This will make sure that the meaningful data is displayed, but that the PK TeamID is bound for future operations.
Did that help?
mmcdonal
|
|

September 5th, 2007, 11:19 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
Foreign Key, that is, a column in the table that looks up to a PK in a Foreign table.
mmcdonal
|

September 5th, 2007, 12:12 PM
|
Authorized User
|
|
Join Date: Sep 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
OK. I tried it and it doesn't work. My understanding is that the FK is the many in the one to many relationship with LeagueID in tblLeague.
What I found confusing was the iLeague. Is this referring to tblLeague or League ID?
Not knowing Visual Basic, I'm not sure about what I am typing, but I checked it all and it is accurate, but I wasn't sure what this iLeaguewas.
|

September 6th, 2007, 06:27 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
iLeague is a variable name. I prefaced the name with "i" to indicate that it was an integer type. It referes to LeagueID. It is always better to take a value in a variable, and then put it into a string, like I did, rather than:
SELECT * FROM tblLeague WHERE [LeagueID] = " & Me.LeagueID
Access does not like that.
If your table structure is as I posted, this will work. If your table structure is different, this may not work. Remember I am using values from your example, so if you have different field names, etc, you need to substitute them where appropriate.
mmcdonal
|

September 6th, 2007, 10:26 AM
|
Authorized User
|
|
Join Date: Sep 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
 What am I doing wrong???? Here is what I typed:
Private Sub cboleague_Change()
Dim sSQL As String
Dim ileague As Integer
ileague = Me.cboleague
sSQL "SELECT teamid, teamname FROM tblteam WHERE [leagueid] = " & ileague
Me.cboteam.RowSource = sSQL
Me.cboteam.Requery
End Sub
In my second combo box, it wouldn't let me put a second number in the list width property. Did you mean column width?
Does there need to be a control source for the second combo box?
I wish I could send you what I have done. Thank you for your patience. The variables are in lower case because that's the way I created them, just in case it is case sensitive.
When you create the combo boxes, there are some options to choose. Does what you choose affect the outcome? Again, thank you for your patience.
|

September 6th, 2007, 10:41 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
Column Widths is correct.
No Record source for the second combo.
The first combo box you can use the wizard for, the second one, do not use the wizard.
This line: sSQL "SELECT teamid, teamname FROM tblteam WHERE [leagueid] = " & ileague
Should be:
sSQL = "SELECT teamid, teamname FROM tblteam WHERE [leagueid] = " & ileague
Did that help?
mmcdonal
|

September 6th, 2007, 12:33 PM
|
Authorized User
|
|
Join Date: Sep 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
:( No! I have checked and rechecked. I've tried every combination. Unfortunately, this message board doesn't let me send you the little database. When I click on the combo box in the form, not the subform, it is blank. Same goes for the combo box on the subform - blank.
|

September 7th, 2007, 06:33 AM
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
You have to click on my username, then send me a message, and when I reply, you can send the database (zipped).
mmcdonal
|
|
 |