Hi
I would like to change the RowSource of a Combo box based on another combobox.
I'm having some problems, so I tried to change the SQL in the RecordSource property of the ComboBox to what I actually want to reset it to. This SQL runs fine.
When I try and put it in the OnGotFocus event of the ComboBox I get a syntax error so I am assuming it has something to do with the Quotes.
This is what the exact same SQL looks like there:
End Select
End Sub
I'm getting very frustrated with this as I've spent a lot of time learning how to put quotes in SQL for VBA lately and felt like I had it mostly figured out.
Really Appreciate any assistance
Thanks
I would like to change the RowSource of a Combo box based on another combobox.
I'm having some problems, so I tried to change the SQL in the RecordSource property of the ComboBox to what I actually want to reset it to. This SQL runs fine.
SELECT tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company, tblInvEqSectors.Sector, tblEquity.SectorID, Sum(tblCCTransaction.Quantity) AS SumOfQuantity
FROM (tblEquity INNER JOIN tblInvEqSectors ON (tblEquity.SectorID = tblInvEqSectors.SectorID) AND (tblEquity.SectorID = tblInvEqSectors.SectorID) AND (tblEquity.SectorID = tblInvEqSectors.SectorID)) LEFT JOIN tblCCTransaction ON tblEquity.EquityID = tblCCTransaction.EquityID
GROUP BY tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company, tblInvEqSectors.Sector, tblEquity.SectorID
HAVING (((Sum(tblCCTransaction.Quantity))>0))
ORDER BY tblEquity.Ticker;
When I try and put it in the OnGotFocus event of the ComboBox I get a syntax error so I am assuming it has something to do with the Quotes.
This is what the exact same SQL looks like there:
Private Sub cboTicker_GotFocus()
'Set Record Source To Show only owned equity for an equity Sell
Select Case Me.cboActionID
Case 2 'Change Rowsource to show only equities held when selling
cboTicker.RowSource = "SELECT tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company, tblInvEqSectors.Sector, tblEquity.SectorID, tblCCTransaction.Quantity" & _
"FROM (tblEquity INNER JOIN tblInvEqSectors ON (tblEquity.SectorID = tblInvEqSectors.SectorID) AND (tblEquity.SectorID = tblInvEqSectors.SectorID)" & _
"AND (tblEquity.SectorID = tblInvEqSectors.SectorID)) LEFT JOIN tblCCTransaction ON tblEquity.EquityID = tblCCTransaction.EquityID" & _
"GROUP BY tblEquity.EquityID, tblEquity.Ticker, tblEquity.Company, tblInvEqSectors.Sector, tblEquity.SectorID, tblCCTransaction.Quantity" & _
"ORDER BY tblEquity.Ticker;"
End Select
End Sub
I'm getting very frustrated with this as I've spent a lot of time learning how to put quotes in SQL for VBA lately and felt like I had it mostly figured out.
Really Appreciate any assistance
Thanks