Combining combo's

[BadScript]

I have a single table and a form to filter out and display the correct record.
To select a unique record I would have to create 4 cascading combo's which would look like this:

cbo_method - cbo_un - cbo_psn - cbo_pg
My columns are also named method, un, psn and pg

I would like to reduce my form to 3 combo's by combining cbo_un and cbo_psn, on my form it looks like this:

cbo_method - cbo_un + psn - cbo_pg

how do I configure my last combo (cbo_pg) so that it only displays values based on the selection of the method and un + psn in the first 2 combo's?

 

[BadScript]

Never mind I found a way by displaying psn in a textbox and by adding [Forms]![frmChecklist]![cbo_un] and [Forms]![frmChecklist]![txt_psn]to the query in cbo_pg...

 

[DCrake]

Simple Software Solutions

Hi

Correct me if I am wrong but you have 3 combos and you want the combos to react to the previous combos value.

Combo 1
This will contain the Grandfather recordset.
RowSource = "Select * From MyTable Where GrandParent Is Not Null"

Step 1
Select a Grandparent for the first combo
On the AfterUpdate Property of Combo1 enter the following:
Me.Combo2.Rowsource = "Select * From MyTable Where GrandParent='" & me.combo1.Column(x) & "'" - Where column x contains the key value
This will create a filter in the second combo

Combo 2
Step 2
When you click on the second combo you will only see the relevant parents
On the AfterUpdate Property of Combo2 enter the following:
Me.Combo2.Rowsource = "Select * From MyTable Where GrandParent='" & me.combo1.Column(x) & "' AND Parent =' & Me.Combo2.Column(x) & "'" - Where column x contains the key value

Combo 3
Step 3
Click on the last combo and you will only see the grandchildren records. only children of the Grandparent and the Parent




Combo 1
Company A
Company B
Company C


Combo 2 - Company 2
Purchasing
Sales
R & D
Stores

Combo 3 - Sales
Rep 1
Rep 2
Rep 3
Rep 4

In the above I have selected Company 2 which refreshed the second combo to show me all the main departments for that Company 2. I then clicked on Sales for Company 2. Then in the final combo it listed the Sales Reps for Company 2.

A bit long winded but I think you will get the gist

Remember to set the RowSource or all siblings to "" when a combo receives the focus. This prevents the user from selecting a wrong item for a combo as the rowsource is refreshed after the parent is updated.

Code Master

 

[BadScript]

Thanks, alot..
I'll try both methods, I got really stuck in this part..

 

[BadScript]

I have tried really hard to understand and try out what you post above but I'm afraid i'm still too much of a rookie.

Maybe it helps if I display what I have right now and I know what causes my problem, just don't know how to fix it..

Combo 1 = cbo_method:

Rowsource: "PAX";"CAO";"LQ"

Afterupdate cbo_method:
DoCmd.Requery "cbo_un"

Combo 2 = cbo_un:
Rowsource: SELECT DISTINCT tblPSN.method, tblPSN.un, tblPSN.psn, tblPSN.tech, tblPSN.class, tblPSN.sub1, tblPSN.sub2, tblPSN.sp FROM tblPSN WHERE (((tblPSN.method)=Forms!frmChecklist!cbo_method)) ORDER BY tblPSN.un;

Afterupdate cbo_un:
DoCmd.Requery "cbo_pg"
[cbo_pg] = ""
txt_un = cbo_un.Column(1)
txt_psn = cbo_un.Column(2)
txt_tech = cbo_un.Column(3)
txt_class = cbo_un.Column(4)
txt_sub1 = cbo_un.Column(5)
txt_sub2 = cbo_un.Column(6)
txt_sp = cbo_un.Column(7)
End Sub

Here I display psn column(2) in a textbox called txt_psn

Combo 3 = cbo_pg:
Rowsource: SELECT DISTINCT tblPSN.method, tblPSN.un, tblPSN.psn, tblPSN.pg, tblPSN.pi, tblPSN.qty FROM tblPSN WHERE (((tblPSN.method)=Forms!frmChecklist!cbo_method) And ((tblPSN.un)=Forms!frmChecklist!cbo_un) And ((tblPSN.psn)=Forms!frmChecklist!txt_psn)) ORDER BY tblPSN.pg DESC;

Here I refer to the value displayed in this textbox called txt_psn, it works in 2003 but creates problems in 2007. What would be the proper way to do this?

afterupdate cbo_pg:
txt_pg = cbo_pg.Column(3)
txt_pi = cbo_pg.Column(4)
txt_qty = cbo_pg.Column(5)
End Sub

 

[pondlife]

easy - just base the last combo on a query which has the other combos as criteria

 

[BadScript]

But in the 2nd combo I select 2 values; un and psn... how do i make the 3rd combo know it must also query the 2nd column (psn) of the selection made in the 2nd combo? Right now I display the 2nd column (psn) in a textbox by adding this in the afterupdate:

txt_psn = cbo_un.Column(2)

Then I added this query in the rowsource of the 3rd combo:

WHERE ((tblPSN.un)=Forms!frmChecklist!cbo_un) AND ((tblPSN.psn)=Forms!frmChecklist!txt_psn))

How do i do this properly? This method locks up my form in design view in 2003 and in 2007 it doesn't even work at all..

 

[pondlife]

have you tried:

... AND ((tblPSN.psn) = Forms!frmChecklist!cbo3.Column(1)

 

[BadScript]

I'll try that this evening... many thanks in advance...

 

[BadScript]

I tried this but then my combo3 stays empty...
help

 

[BadScript]

I attached my form, can someone please help me with this?

 

[BadScript]

this is giving me a mild headache, I'm so close yet I can't figure it out...