Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 4th, 2007, 09:43 PM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?

Reply With Quote
  #2 (permalink)  
Old September 5th, 2007, 10:18 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old September 5th, 2007, 10:46 AM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old September 5th, 2007, 11:19 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Foreign Key, that is, a column in the table that looks up to a PK in a Foreign table.

mmcdonal
Reply With Quote
  #5 (permalink)  
Old September 5th, 2007, 12:12 PM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old September 6th, 2007, 06:27 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old September 6th, 2007, 10:26 AM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old September 6th, 2007, 10:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #9 (permalink)  
Old September 6th, 2007, 12:33 PM
Authorized User
 
Join Date: Sep 2007
Location: , , .
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

:( 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.

Reply With Quote
  #10 (permalink)  
Old September 7th, 2007, 06:33 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You have to click on my username, then send me a message, and when I reply, you can send the database (zipped).

mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Help creating a combo-box for emailing URLs John_Galt Word VBA 0 March 14th, 2008 10:23 AM
Different subforms depending on combo box choice stevensj5 Access 5 October 3rd, 2007 08:54 PM
Find Record Combo Box Wizard Won't Show 3rd Choice HenryE Access 0 February 2nd, 2004 11:52 PM
Creating custom Combo box control nazran VB How-To 2 January 21st, 2004 02:30 PM



All times are GMT -4. The time now is 02:56 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.