How to limit Combo Box depending upon other Combo Box

[irsmalik]

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

[C CSR]

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

A1    s1
B2    s1
B2    s2
B2    s3
C3    s1
C3    s2
C3    s3
C3    s4
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:

SQL1 = "SELECT ForCombos.fArea FROM ForCombos GROUP BY ForCombos.fArea;"
 
Me.Combo1.RowSource =  SQL1
 

Then when an item is selected from Comb1(Onclick event) :

AREA_SEL = Me.Combo1
 
SQL2 = "SELECT ForCombos.fArea, ForCombos.fSystem FROM ForCombos WHERE (((ForCombos.fArea)='" & AREA_SEL & "'))"
 
Me.Combo2.RowSource =  SQL2
 

[C CSR]

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

[NeoPa]

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.

[irsmalik]

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.

[C CSR]

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).

[irsmalik]

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.

[NeoPa]

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

[ChrisPadgham]

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

combo2.rowsource = "SELECT ID, F2 FROM T2 WHERE (((T2.T1ID)=[Forms]![Form1]![Combo1]));"

in the click event of combo1 requery combo2

Private Sub Combo1_Click()
Me.Combo2.Requery
End Sub