If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combo Cascades
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 |
#2
|
|||
|
|||
Combo Cascades
|
#3
|
|||
|
|||
Combo Cascades
On Feb 25, 8:49 am, "Rick Brandt" wrote:
wrote: 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 |
Thread Tools | |
Display Modes | |
|
|