Combo Cascades

[d9pierce]

Hi all,
I do not understand why this is so complicated for me? I am a novice
but this should be extremely simple.

I have a form "Projects" which is built on a Qry which is taken from
three tables.
Tbl 1 = "Company_Main
Tbl 2 = "Cities"
Tbl 3 = "Projects"

Company_Main stores information about companies... Names, addresses
and so forth!
Cities containes information about cities such as City, State, and
Zip...
Projects stores infromation about my projects. Project name, address,
and so on.

Qry "Projects_Main" is related to Tbl "Cities" by Cities.CityID and
Projects.ZipCodeID which is a look up on my form as to a zipcode.
Also "Projects_Main" is related to Company_Main.CompanyID and
Projects.ClientID as to look up a company.

What I am trying to do is to cascade two combo boxes on my form
"Projects".

I have a cbo ClientID on my form and a cbo(Unbound) on my form called
Divisions. I have also tried this (Bound) in a look up. This comes
from a query from tbl Divisions.DivisionName

When I select a company from the cbo "ClientID" I want to select a
Division from cbo "Division" and have that show only division names
that are in that company previously selected.

I am using the following SQL and it works with the exception it shows
all divisions for all companies, not just the selected company.
SELECT Client_Division.ClientDivisionID, Client_Division.DivisionName,
Client_Division.CompanyID, Projects.ClientID
FROM Client_Division INNER JOIN Projects ON Client_Division.CompanyID
= Projects.ClientID
WHERE (((Client_Division.CompanyID) Like IIf([CompanyID] Is Not
Null,"*")))
ORDER BY Projects.ClientID;

I have tried many examples, many different ways and I am just flat out
frustraited!
Any suggestions would be so apriciated!!!!!

Thanks,
Dave

 

[Rick Brandt]

Hi all,
I do not understand why this is so complicated for me? I am a novice
but this should be extremely simple.

I have a form "Projects" which is built on a Qry which is taken from
three tables.
Tbl 1 = "Company_Main
Tbl 2 = "Cities"
Tbl 3 = "Projects"

Company_Main stores information about companies... Names, addresses
and so forth!
Cities containes information about cities such as City, State, and
Zip...
Projects stores infromation about my projects. Project name, address,
and so on.

Qry "Projects_Main" is related to Tbl "Cities" by Cities.CityID and
Projects.ZipCodeID which is a look up on my form as to a zipcode.
Also "Projects_Main" is related to Company_Main.CompanyID and
Projects.ClientID as to look up a company.

What I am trying to do is to cascade two combo boxes on my form
"Projects".

I have a cbo ClientID on my form and a cbo(Unbound) on my form called
Divisions. I have also tried this (Bound) in a look up. This comes
from a query from tbl Divisions.DivisionName

When I select a company from the cbo "ClientID" I want to select a
Division from cbo "Division" and have that show only division names
that are in that company previously selected.

I am using the following SQL and it works with the exception it shows
all divisions for all companies, not just the selected company.
SELECT Client_Division.ClientDivisionID, Client_Division.DivisionName,
Client_Division.CompanyID, Projects.ClientID
FROM Client_Division INNER JOIN Projects ON Client_Division.CompanyID
= Projects.ClientID
WHERE (((Client_Division.CompanyID) Like IIf([CompanyID] Is Not
Null,"*")))
ORDER BY Projects.ClientID;

I have tried many examples, many different ways and I am just flat out
frustraited!
Any suggestions would be so apriciated!!!!!

Thanks,
Dave

What is [CompmayID] in the "IIf([ComnpanyID]..." snippet? Is that supposed to
be referrring to a control on your form? If so that will not work. SQL Strings
in RowSources cannot refer to other objects on the form directly like that.
What your doing with the IIF() is also incorrect and would not work even if you
had the control reference right.

Try...

SELECT Client_Division.ClientDivisionID,
Client_Division.DivisionName,
Client_Division.CompanyID,
Projects.ClientID
FROM Client_Division INNER JOIN Projects
ON Client_Division.CompanyID = Projects.ClientID
WHERE Client_Division.CompanyID = Forms!Projects![CompanyID]
OR Forms!Projects![CompanyID] Is Null
ORDER BY Projects.ClientID;

 

[d9pierce]

Hi all,
I do not understand why this is so complicated for me? I am a novice
but this should be extremely simple.

I have a form "Projects" which is built on a Qry which is taken from
three tables.
Tbl 1 = "Company_Main
Tbl 2 = "Cities"
Tbl 3 = "Projects"

Company_Main stores information about companies... Names, addresses
and so forth!
Cities containes information about cities such as City, State, and
Zip...
Projects stores infromation about my projects. Project name, address,
and so on.

Qry "Projects_Main" is related to Tbl "Cities" by Cities.CityID and
Projects.ZipCodeID which is a look up on my form as to a zipcode.
Also "Projects_Main" is related to Company_Main.CompanyID and
Projects.ClientID as to look up a company.

What I am trying to do is to cascade two combo boxes on my form
"Projects".

I have a cbo ClientID on my form and a cbo(Unbound) on my form called
Divisions. I have also tried this (Bound) in a look up. This comes
from a query from tbl Divisions.DivisionName

When I select a company from the cbo "ClientID" I want to select a
Division from cbo "Division" and have that show only division names
that are in that company previously selected.

I am using the following SQL and it works with the exception it shows
all divisions for all companies, not just the selected company.
SELECT Client_Division.ClientDivisionID, Client_Division.DivisionName,
Client_Division.CompanyID, Projects.ClientID
FROM Client_Division INNER JOIN Projects ON Client_Division.CompanyID
= Projects.ClientID
WHERE (((Client_Division.CompanyID) Like IIf([CompanyID] Is Not
Null,"*")))
ORDER BY Projects.ClientID;

I have tried many examples, many different ways and I am just flat out
frustraited!
Any suggestions would be so apriciated!!!!!

Thanks,
Dave

What is [CompmayID] in the "IIf([ComnpanyID]..." snippet? Is that supposed to
be referrring to a control on your form? If so that will not work. SQL Strings
in RowSources cannot refer to other objects on the form directly like that.
What your doing with the IIF() is also incorrect and would not work even if you
had the control reference right.

Try...

SELECT Client_Division.ClientDivisionID,
Client_Division.DivisionName,
Client_Division.CompanyID,
Projects.ClientID
FROM Client_Division INNER JOIN Projects
ON Client_Division.CompanyID = Projects.ClientID
WHERE Client_Division.CompanyID = Forms!Projects![CompanyID]
OR Forms!Projects![CompanyID] Is Null
ORDER BY Projects.ClientID;

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

Thanks so much Rick!
I had to add a requery to form on current and after update to ClientID
but worked greeat!
Thanks so much!
Dave