Cascading Combo Boxes

[zappa007]

I have 4 Combo Boxes on my form (cmbo1, cmbo2, cmbo3, cmbo4). I have successfully managed to get the cascading down as long as the user starts with cmbo1.

What I would like to be able to do is have the user select any combo box on the form and have the rest of the boxes filter automatically, e.g., a user selects a value in cmbo2, the afterupdate would then filter/lookup the values of cmbo1, cmbo3, cmbo4 based on the value selected in cmbo2.

Is this possible and if so, does anyone know how to do this?

Thanks.

 

[Len Boorman]

If you are going to have cascading combos then would it not be easier to control their use than cater for random use.

I would set Visible property of 2, 3 and 4 to No
On Click of 1 then
Cbo_2.requery (based on cbo_1
Cbo_2.visible =True
Cbo_2.setfocus
Cbo_2.dropdown.

etc down the combos

User is thus controlled and applies selection on a logical manner

Len

 

[zappa007]

Hi Len,

Thanks for taking the time to reply. I do have one question, maybe problem, with the your idea.

In some cases, cmbo1 will be null, i.e., users may not wish to filter based on that value. They may just want to see all records pertaining to the value selected in cmbo2.

I've read that a nested Dlookup would work. What do you think? Or is there a way to adapt your idea.

Thanks again

 

[Len Boorman]

Search the Forum for All in Combo Box.
Its there cos I have used it. If you have problem finding it let me know and I will drag it out of whichever database I used it in

Include this in first combo

Always control the User if you can

Len

 

[zappa007]

I agree about controlling the user...I am having a hard time finding the example you referenced...if you could get it from the database you mentioned, i'd appreciate it...

thanks

 

[Len Boorman]

don't do this for everybody

SELECT DISTINCT [Vendor Classification].Classification, [Vendor Classification].Description FROM [Vendor Classification] UNION Select null, "<All>" as Bogus FROM [Vendor Classification]
ORDER BY [Vendor Classification].Classification;


vendor classification is my table

classification, Description are fields within that table

The rest is about the <All>

Works a treat

L

 

[zappa007]

Hey Len,

This was my first post to this forum and if you are representative of the people that lend their help to people like me, then this site is far superior to any comparable site.

So far, the advice you gave has worked perfectly. I'm still playing with the ALL portion of the code but it looks promising.

Thanks again,

ed

 

[Len Boorman]

There are lots pf people on this forum who are much better than me. It is the best forum you will find.

Things to remember
The all do it for free
The all are trying to help
They are not there simply to write code for you
They are not simply to provide answers

What you need to do
Use the search
Ask specific questions
When starting a thread use a meaningful title
It is sometimes very difficult to assess somebodies particular needs so be as clear as possible
Apply yourself to implementing a potential solution. Coming back saying it did not work is like saying my car is broke without saying exactly which bit didn't work.
If somebody helps give them feedback as you have. It can be frustrating to offer a solution to somebody and never hear back that your advice was helpful or whatever.

Learning is painful so expect some pain. No gain without pain is very true but the satisfaction of achieving has no equal

Len

 

[Len Boorman]

Duplicated
line playing up

L

 

[Len Boorman]

Another duplicate

 

[zappa007]

Hey Len,

I've been playing with the code and can't seem to get the ALL part right. Could you have a look at it and maybe point me in the right direction.

**Note: This is the code from the first combo box. It works perfectly, however, when I put in the UNION...etc, it bombs. May just be the position of it..

thanks..

Dim sSQL As String
Dim strSQL As String
Dim strSQLSF As String

EmployeeCmbo = Null


strSQL = "SELECT DISTINCT qry_update.prj_gss_name1 FROM qry_update"
strSQL = strSQL & " WHERE qry_update.group = '" & GroupCmbo & "'"
strSQL = strSQL & " ORDER BY qry_update.prj_gss_name1;"

EmployeeCmbo.RowSource = strSQL

strSQLSF = "SELECT * FROM qry_update"
strSQLSF = strSQLSF & " WHERE qry_update.group = '" & GroupCmbo & "'"


Me.RecordSource = strSQLSF
Me.Requery

Me.EmployeeCmbo.Requery
Me.EmployeeCmbo.Visible = True
Me.EmployeeCmbo.SetFocus
Me.EmployeeCmbo.Dropdown

 

[Len Boorman]

Paste the complete sql string as the Row source of the combo box

SELECT DISTINCT [Vendor Classification].Classification, [Vendor Classification].Description FROM [Vendor Classification] UNION Select null, "<All>" as Bogus FROM [Vendor Classification]
ORDER BY [Vendor Classification].Classification;

Change the Tale names and fields as required.

Len

 

[zappa007]

Hey Len,

I'm getting closer. Thanks for the point in the right direction. It works with 2 exceptions.

1. I get repetitive values in the combo box. (I think it has to do with the format of the table/query)

2. When I select <all>, no values appear in the next combo box. That one has me stumped. Though I think it lies in the code of the after update command. I bet I can fix it with an IF statement?

If you can offer any suggestions, let me know. If I make any progress, I'll pass it along.

ed

 

[Len Boorman]

1. I get repetitive values in the combo box. (I think it has to do with the format of the table/query)

Are you sure you left the DISTINCT in

2. When I select <all>, no values appear in the next combo box. That one has me stumped. Though I think it lies in the code of the after update command. I bet I can fix it with an IF statement?

Rather than use the after update use the On Click. All you need there is basically and event procedure holding

cbo_2.requery

where cbo_2 is the second combo box in the series feeding from combo 1

You could tart this up with

cbo_2.requery
cbo_2.setfocus
cbo_2.dropdown

Let me know how you get on

never actually used the All as part of a cascading combo set myself but figure it should evaluate to a null so query supporting cbo_2 should select all values.

The query supporting cbo_2 of course must have the criteria of cbo_1 in it. Again generally I use the Forms![Form name].[cbo_1]syntax direct inot the criteria

Len

 

[zappa007]

Hey Len,

I'm getting very close....

I fixed #2 below...that works fine...just had a little code issue...

But I still can't seem to figure out #1...repeating values....

Here's my code...

SELECT DISTINCT [list:group].group, [qry_update].group FROM [qry_update], [list:group] UNION Select null, "<All>" as Bogus FROM [list:group]
ORDER BY [list:group].group;


Any insight....

 

[Len Boorman]

But I still can't seem to figure out #1...repeating values....

Here's my code...

SELECT DISTINCT [list:group].group, [qry_update].group FROM [qry_update], [list:group] UNION Select null, "<All>" as Bogus FROM [list:group]
ORDER BY [list:group].group;

Why selecting from List and qry _update with no Join specified
Do you not have a single source for group
If not, Database design ? make a single source first

Select group.group from group
UNION
Select qry_Update.group from qry_Update;

Use this in main combo box source
SELECT DISTINCT union_qry_name.group from union_qry_name
UNION Select null, "<All>" as Bogus FROM [union_qry_name]
ORDER BY [union_qry_name].group ASC;

Len

 

[zappa007]

Hey Len

We're close....

I made the changes but still get this error....

The number of columns in the two selected tables or queries of a union query do not match...

here's the code...

SELECT DISTINCT union_qry_update.group FROM union_qry_update UNION Select null, "<All>" as Bogus FROM [union_qry_update]
ORDER BY [union_qry_update].group;

 

[Len Boorman]

is your group reference a Number ?.

No its not that

The union query update has a single field, the Union bit is calling for 2
try
SELECT DISTINCT union_qry_update.group FROM union_qry_update UNION Select "<All>" as Bogus FROM [union_qry_update]
ORDER BY [union_qry_update].group;

L



L

 

[zappa007]

No.....how about i give a little more info...I inherited the database, so please forgive the naming convention. I know it isn't the best...

The groups are R&D, CSS, M&S (and several others)...they were put in a table called List:Group

when I created the union query (union_wry_update) between List:Group and Qry_update, it produced the correct results, i.e., <ALL>, R&D, CSS...etc.

the qry_update is a query containing two tables. when run, the qry_update works fine too.

so it seems I have 2 and 2 but when I add them, i don't get 4...i get a fraction...

 

[zappa007]

here is the union qry code...may or may not help

Select group from list:group
UNION Select qry_Update.group from qry_Update;