Cascading CboBox Filtering

  • Thread starter Thread starter DubboPete
  • Start date Start date
D

DubboPete

Hi all,

I have four tables, [TblClients],[TblProjects],[TblAssy],[TblSubAssy]

I am successfully filtering the first two of four combo boxes, but getting
stuck on the progressive filtering for the third, and subsequently fourth
combo box!

Combo0 - ClientID = 1000; G-Force
Combo100 - This Client's Projects = Angel; Bravo
Combo200 - Project Assemblies = (Angel) 050020; (Bravo) 060020
Combo300 - Sub-Asssemblies
050020; ISO; 050021;TIE-OUT
060020; ISO2; 060021; TIE-IN

Here's what I do:
Select a client from Combo0, then after update move to Combo100, which
correctly filters projects for Client 1000. The projects I see are Angel
and Bravo

I then wanted the third combo (Combo200) to only display the assemblies for
either Angel or Bravo, so if I selected Angel in Combo100, I should only see
050020 in Combo200; if I selected Bravo in Combo100, I should see 060020 in
Combo200.

The progression halts here, as this code does not work for Combo200. This
is what I have:

SELECT TblAssy.Assy, TblAssy.Project_
FROM TblProjects INNER JOIN TblAssy_
ON TblProjects.Project=TblAssy.Project_
WHERE (((TblAssy.Project)=Forms!FrmBOMCreate!combo100));

If someone can help me correct this code, I feel confident to go on and
manage the code for Combo300!

thanks in anticipation

Pete
 
You need tables that look like:
TblClient
ClientID
Client

TblProject
ProjectID
ClientID
Project

TblAssy
AssyID
ProjectID
Assy

TblSubAssy
SubAssyID
AssyID
SubAssy

1. In your first Cbx you slect a client from TblClient and this gives you
ClientID in TblProject which returns projects for the selected client.
2. In your second Cbx you select a project from the list of projects
returned in 1. This gives you ProjectID in TblAssy which returns Assys for
the selected project.
3. In your third Cbx you select an assy from the list of assys returned in
2. This gives you AssyID in TblSubAssy which returns SubAssys for the
selected assy.
4. Your fourth cbx then selects a specific subassy to do with as you wish.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room
reservations scheduling? Need employee work scheduling? Contact me!
 
oops - I changed the bound column to 2, and it worked fine!

thanks for the help anyway!

Pete

PC Datasheet said:
You need tables that look like:
TblClient
ClientID
Client

TblProject
ProjectID
ClientID
Project

TblAssy
AssyID
ProjectID
Assy

TblSubAssy
SubAssyID
AssyID
SubAssy

1. In your first Cbx you slect a client from TblClient and this gives you
ClientID in TblProject which returns projects for the selected client.
2. In your second Cbx you select a project from the list of projects
returned in 1. This gives you ProjectID in TblAssy which returns Assys for
the selected project.
3. In your third Cbx you select an assy from the list of assys returned
in 2. This gives you AssyID in TblSubAssy which returns SubAssys for the
selected assy.
4. Your fourth cbx then selects a specific subassy to do with as you
wish.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room
reservations scheduling? Need employee work scheduling? Contact me!


DubboPete said:
Hi all,

I have four tables, [TblClients],[TblProjects],[TblAssy],[TblSubAssy]

I am successfully filtering the first two of four combo boxes, but
getting stuck on the progressive filtering for the third, and
subsequently fourth combo box!

Combo0 - ClientID = 1000; G-Force
Combo100 - This Client's Projects = Angel; Bravo
Combo200 - Project Assemblies = (Angel) 050020; (Bravo) 060020
Combo300 - Sub-Asssemblies
050020; ISO; 050021;TIE-OUT
060020; ISO2; 060021; TIE-IN

Here's what I do:
Select a client from Combo0, then after update move to Combo100, which
correctly filters projects for Client 1000. The projects I see are Angel
and Bravo

I then wanted the third combo (Combo200) to only display the assemblies
for either Angel or Bravo, so if I selected Angel in Combo100, I should
only see 050020 in Combo200; if I selected Bravo in Combo100, I should
see 060020 in Combo200.

The progression halts here, as this code does not work for Combo200.
This is what I have:

SELECT TblAssy.Assy, TblAssy.Project_
FROM TblProjects INNER JOIN TblAssy_
ON TblProjects.Project=TblAssy.Project_
WHERE (((TblAssy.Project)=Forms!FrmBOMCreate!combo100));

If someone can help me correct this code, I feel confident to go on and
manage the code for Combo300!

thanks in anticipation

Pete
Click to expand...
Click to expand...
 
PC Datasheet said:
If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee.
Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room
reservations scheduling? Need employee work scheduling? Contact me!
Click to expand...

These 1000 (if at all a real figure..) is only the result of
-- 4 years abusing the newsgroups.
-- 4 years blatantly advertising and job hunting.

You only care about making money, and you act as if the groups are your private hunting ground.
So why would ANYBODY ever trust a person like you and hire you?
********************************************************

Explanation and more on this answer to Steve:
http://home.tiscali.nl/arracom/stopsteve.html

Arno R
 
Back
Top