I have 5 form fields named cbVendo, cbVendor2 ... cbVendor5. I am trying to create a dynamic sql in which I use the name of the control to append the number at the end. Something like the below but I can't get it to work.
I am putting the SQL as the rowsource of another combo box.
"SELECT tblMarketData.Survey, tblMarketData.Vendor
FROM tblmarketData
WHERE tblMarketData.Vendor='"& [forms]![frmMarketHayEval]!cbVendor & Right(Me.ActiveControl.Name,1) & "'"
I have highlighted the piece in red that is what I am trying to do.