By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Combo box based on another combo box

P: 11
I've got two combo boxes on a form:
1. Division Name - "_" (Blank), "Division1", "Division2" etc
2. Members of a selected Division

What I want to do is select a division and then i want the 2nd combo box to show only the members of the selected Division. However, if i select the blank division "_", i want it to show me all members of all divisions.

At the moment, if i select a Division, it comes up with only the members in the selected Division. I don't know how to get it so that if i choose the blank division, all names come up.

i tried putting in the criteria for the second combo box as follows:
IIf([Forms]![FormName]![DivisionName]=" ", <>[Forms]![FormName]![DivisionName], [Forms]![FormName]![DivisionName])

But the result is the same as if i just limited the criteria just to be [Forms]![FormName]![DivisionName], - the result only achieves the first part of what i want getting done.

Sorry if i sounded too confusing for you to follow.
Dec 19 '06 #1
Share this Question
Share on Google+
6 Replies


100+
P: 143
You need to look at cascading combo boxes. Try this site. Look under cascading combo box.

http://www.utterangel.com/pages/access_downloads.aspx
Dec 19 '06 #2

P: 11
You need to look at cascading combo boxes. Try this site. Look under cascading combo box.

http://www.utterangel.com/pages/access_downloads.aspx
Thanks Tanis but i find that the cascading combo boxes was too complicated for me to follow, any more ideas?
Dec 19 '06 #3

P: 11
Thanks, i'm just going to look up cascading combo boxes to work this one out. i thought it would be a simple solution, without much usage of vba, but it doesnt appear to be so.

Thanks anyway.
Dec 19 '06 #4

100+
P: 106
Thanks, i'm just going to look up cascading combo boxes to work this one out. i thought it would be a simple solution, without much usage of vba, but it doesnt appear to be so.

Thanks anyway.
When you find the solution, let me know, becasue I have the same problem
Thanks
Dec 19 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
OK guys this isn't that complicated but you will need some VBA code.

In the After Update Event of Combo1 put ...

Expand|Select|Wrap|Line Numbers
  1. Private Sub Division_Name_AfterUpdate()
  2.  
  3.    SELECT CASE Division_Name
  4.  
  5.    CASE " " ' I'm assuming this is the value in the blank row
  6.       Me.Members.RowSource = "SELECT Member FROM TableName;"
  7.  
  8.    CASE ELSE
  9.       Me.Members.RowSource = "SELECT Member FROM TableName WHERE DivisionName = [Froms]![FormName]![DivisionName];"
  10.  
  11.    END SELECT
  12.  
  13.    Me.Members.Requery
  14.  
  15. End Sub
  16.  

Mary
Dec 20 '06 #6

100+
P: 134
Let assume your 1st combo box called cboDivision, in the After event, you add this statement, I just show an example, you need to fill up the other column.

I am just coping my coding over here.

Dim sManagerSource As String

sManagerSource = "SELECT [tablename].[DivisonID] as divsionID,......." & _
"WHERE tablename.divsionID = " & Me.cboDivision.Column(0)


2ndCombobox.RowSource = sManagerSource

Try this, just 2 line of code,I have this problem before, now for me it solved.
Also try to put those statement in the Form Current, so the next time when you edit the form, just click the 2nd combo box it will remember the selection from the first combo box, this way you don't have to select 1st combo box and then select 2nd combo box, unless you want to make another selection on your 1st combo box.

Happy coding
Dec 20 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.