Solved Trouble with SQL in VBA when I try and change the Rowsource property of a combo box (1 Viewer)

AJJJR

Registered User.
Local time
Today, 01:47
Joined
Mar 19, 2018
Messages
56
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.

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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:47
Joined
May 7, 2009
Messages
19,170
there shouldn't be a "blank" line for the Continuation of line and add a "space" at the end:
Code:
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
 

AJJJR

Registered User.
Local time
Today, 01:47
Joined
Mar 19, 2018
Messages
56
there shouldn't be a "blank" line for the Continuation of line and add a "space" at the end:
Code:
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
Hi ArnelGP Thanks for your help. You've assisted me on a few occasions. I'm not sure what you mean by blank lines. I think this may be due to the fact that I put code containers around SQL

This is what it looks like without them

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;"
 

AJJJR

Registered User.
Local time
Today, 01:47
Joined
Mar 19, 2018
Messages
56
Now I am getting "Missing operator in query expression"
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:47
Joined
May 7, 2009
Messages
19,170
add "space" at the end (before &):

...
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) " & _
..

do it on all lines except the last line.
 

AJJJR

Registered User.
Local time
Today, 01:47
Joined
Mar 19, 2018
Messages
56
add "space" at the end (before &):

...
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) " & _
..

do it on all lines except the last line.

Bingo! I had never heard you needed a space there. Thanks so much for the help.
 

Users who are viewing this thread

Top Bottom