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

How to limit Combo Box depending upon other Combo Box

P: 53
Hello friends,I am again stuck & need your help.
I have a a table with 2 columns
Column1 (Area) like 3A, 3U, 4A, 4U,5A, 5U, 6A, 6U
COLUMN2 (Systems) like AY, FC, HD, IA, LC, LS, P, SY, UA,etc

Some Areas are link with some system. Not All Area to All system.

I want to select one Area from Combo Box like 3A
and I want only system display from other Combo Box which are associated with Area 3 (Not All)

So this easy task I can not perform and requesting for help. Either to perform it via Query or VBA Code..
Thanks in advance..
Irshad Ahmed
Jan 21 '12 #1
Share this Question
Share on Google+
8 Replies

P: 144
I assume you have a relation between Area and System like this:

Expand|Select|Wrap|Line Numbers
  1. A1    s1
  2. B2    s1
  3. B2    s2
  4. B2    s3
  5. C3    s1
  6. C3    s2
  7. C3    s3
  8. C3    s4
  9. C3    s5
The basic steps are to Query data for first Combo, then use the selected item to populate the second Combo.

Get list for first Combo (EXAMPLE)Onopening the form:

Expand|Select|Wrap|Line Numbers
  1. SQL1 = "SELECT ForCombos.fArea FROM ForCombos GROUP BY ForCombos.fArea;"
  3. Me.Combo1.RowSource =  SQL1
Then when an item is selected from Comb1(Onclick event) :

Expand|Select|Wrap|Line Numbers
  1. AREA_SEL = Me.Combo1
  3. SQL2 = "SELECT ForCombos.fArea, ForCombos.fSystem FROM ForCombos WHERE (((ForCombos.fArea)='" & AREA_SEL & "'))"
  5. Me.Combo2.RowSource =  SQL2
Jan 21 '12 #2

P: 144
Make sure Combo2's "Bound Column" in properties is set to 2 for "ForCombos.fSystem," or just take out the first "ForCombos.fArea" in the SQL2
Jan 21 '12 #3

Expert Mod 15k+
P: 31,186
Are you going to explain how one could tell which systems are associated with which area? The question makes no sense without that important information.
Jan 24 '12 #4

P: 53
Please find attached a test.mdb containing data. One Table "System" have 2 fileds Area and System. Now I want to make a form containing 2 combo box for Area & System. When select one area from first combo, its associated systems must be in the second combo / list box.
There is an other table "Process" when both combo box are selected, it will run query / report which shows record from Process depending upon the selection of combo box.
Jan 24 '12 #5

P: 144
Did you try the suggestions I posted (#2 & #3)? They will work.

After someone selects the System in the 2nd combo, you just need to decide how and where the report pops up.

Also, I would disable the 2nd combo box until a selection is made from the first (that's gonna be a design issue). Use the "OnChange" event of the 1st combo to enable the 2nd combo and fill it with the query run using as criteria the selected Area from the 1st.

Then, when a selection is made from the 2nd combo, use the 1st and 2nd combo selections as criteria for narrowing down to a specific "Process." So you'd have a final query with both selections in it. Not knowing whats in the table, I would hope that it would return one record.

Your attachment didn't show up. Let me know if you've tried the queries yet. If the queries give you what you want, then you can assign them to the appropriate combo box(s).
Jan 24 '12 #6

P: 53
Dear CSR
As I am new to Access / SQL, Im sorry I could not understand your idea.
This attachment is for Mr Neopa.
If U please look into this table, U will have a better idea what I want.
Thanks for your pain.
Jan 24 '12 #7

Expert Mod 15k+
P: 31,186
Please find attached a test.mdb containing data
No. No. No. No. No.

You please ask your question properly and don't expect others to work it out for you by looking through your database. That's disrespectful and means that you still haven't posted an adequate question (I should have deleted it before it got to this stage but it's probably too late now).

When asked for basic information that should have been included in the original post I suggest you supply that information.

As it happens you didn't even attach any database, which is probably just as well.
Jan 24 '12 #8

P: 11
set the rowsource of the second combo to include a WHERE statement referencing the first combo

Expand|Select|Wrap|Line Numbers
  1. combo2.rowsource = "SELECT ID, F2 FROM T2 WHERE (((T2.T1ID)=[Forms]![Form1]![Combo1]));"
in the click event of combo1 requery combo2

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo1_Click()
  2. Me.Combo2.Requery
  3. End Sub
Jan 24 '12 #9

Post your reply

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