cascading combobox problem

[Jaymin]

Hi All,
To me this problem should be easy but its got me at the moment.
I have a form with two combo boxes, one has staff names the other has portfolios, the normal action is when you select the staff name the second combo only shows the portfolios that are associated with them.
But what I want to do is when you select the staff name it only shows the portfolios that are not associated with them.
hope someone can give an idea what to do,
Peter :banghead:

 

[CJ_London]

what is the rowsource to your second combo?

 

[arnelgp]

change the rowsource of your second combo to something:

"select portfolio from table_portfolio where staff_name <> " & me.combo1.staff_id

then on the after update event of your first combo:

private sub combo1_afterupdate()
me.combo2.requery
end sub

 

[Jaymin]

Hi Guys,
Sorry for the delayed response but had to go out of internet access for a few days.
But this is my second combo SQL view

SELECT DISTINCT qryStaffPortfolioAllocation.IDPortfolio, qryStaffPortfolioAllocation.PortfolioName
FROM qryStaffPortfolioAllocation
WHERE (((qryStaffPortfolioAllocation.IDStaff)=[forms]![frmAddPortfolioAllocation]![cboPortfolioHolder]))
GROUP BY qryStaffPortfolioAllocation.IDPortfolio, qryStaffPortfolioAllocation.PortfolioName;

arnelgp i tried your response but I could not get it to work !
I can get it to work if I want to know what portfolios I have been allocated, but not for it to show only the ones that I have not been allocated.

 

[arnelgp]

to show portfolio not associated with staff (on combo):

SELECT DISTINCT qryStaffPortfolioAllocation.IDPortfolio, qryStaffPortfolioAllocation.PortfolioName
FROM qryStaffPortfolioAllocation
WHERE (((qryStaffPortfolioAllocation.IDStaff)<>[forms]![frmAddPortfolioAllocation]![cboPortfolioHolder]))
GROUP BY qryStaffPortfolioAllocation.IDPortfolio, qryStaffPortfolioAllocation.PortfolioName;

 

[CJ_London]

if your second combo sql is in the rowsource, you do not refer to controls on the form in that way - remove

[forms]![frmAddPortfolioAllocation]!

 

[Jaymin]

Hi Guys,
thanks for your replies,
this is the SQL that i entered into the form combo box

SELECT DISTINCTROW tblPortfolio.IDPortfolio, tblPortfolio.PortfolioName
FROM tblStaff INNER JOIN (tblPortfolio INNER JOIN tblPortfolioAllocation ON tblPortfolio.IDPortfolio = tblPortfolioAllocation.PortfolioID) ON tblStaff.IDStaff = tblPortfolioAllocation.StaffID
WHERE (((tblStaff.IDStaff)<>[cboPortfolioHolder]))
GROUP BY tblPortfolio.IDPortfolio, tblPortfolio.PortfolioName;

using this SQL it comes back with all the portfolios available for all staff, not the portfolios that have not been allocated to then as yet,
but if i enter this

SELECT DISTINCTROW tblPortfolio.IDPortfolio, tblPortfolio.PortfolioName
FROM tblStaff INNER JOIN (tblPortfolio INNER JOIN tblPortfolioAllocation ON tblPortfolio.IDPortfolio = tblPortfolioAllocation.PortfolioID) ON tblStaff.IDStaff = tblPortfolioAllocation.StaffID
WHERE (((tblStaff.IDStaff)=[cboPortfolioHolder]))
GROUP BY tblPortfolio.IDPortfolio, tblPortfolio.PortfolioName;

it returns only the portfolio that has been allocated to them which is what it should do,
This is the query builder view

I cannot see where I am going wrong :banghead: :banghead:

 

[CJ_London]

I think you need a completely different query

 SELECT PF.IDPortfolio, PF.PortfolioName
 FROM tblPortfolio PF LEFT JOIN tblPortfolioAllocation PH ON PF.IDPortfolio =PH.PortfolioID
 WHERE PH.StaffID<>[cboPortfolioHolder] OR PH.StaffID is Null

or another way

 SELECT PF.IDPortfolio, PF.PortfolioName
 FROM tblPortfolio PF LEFT JOIN (SELECT PortfolioID FROM tblPortfolioAllocation WHERE StaffID=[cboPortfolioHolder]) PH ON PF.IDPortfolio = PH.PortfolioID
 WHERE PH.PortfolioID is Null

Sorry - I prefer to use Aliases to make things easier to read and reduce typing

 

[arnelgp]

SELECT Distinct tblPportfolio.IDPortFolio, tblPortFolio.PortfolioName FROM tblPortfolio WHERE IDPortFolio NOT IN
(SELECT DISTINCTROW tblPortfolio.IDPortfolio
FROM tblStaff INNER JOIN (tblPortfolio INNER JOIN tblPortfolioAllocation ON tblPortfolio.IDPortfolio = tblPortfolioAllocation.PortfolioID) ON tblStaff.IDStaff = tblPortfolioAllocation.StaffID
WHERE (((tblStaff.IDStaff)=[cboPortfolioHolder]))
GROUP BY tblPortfolio.IDPortfolio, tblPortfolio.PortfolioName);