469,282 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Combo box based on another combo box

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
6 9762
Tanis
143 100+
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
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
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
tara99
106 100+
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
14,534 Expert Mod 8TB
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
jamesnkk
134 100+
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.

Similar topics

2 posts views Thread by Rob | last post: by
1 post views Thread by meganrobertson22 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.