How refer to combo box column in query?

[Dorian]

I need to refer to column(0) of a combo box in a query. This is NOT the bound
column.
I have tried everything I can think of but all fail.
WHERE M.TeamID = [Forms]![frmHDTicket]![cmbTeam].column(0)
WHERE M.TeamID = [Forms]![frmHDTicket]![cmbTeam].[column](0)
WHERE M.TeamID = [Forms]![frmHDTicket]
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Marshall Barton]

I need to refer to column(0) of a combo box in a query. This is NOT the bound
column.
I have tried everything I can think of but all fail.
WHERE M.TeamID = [Forms]![frmHDTicket]![cmbTeam].column(0)
WHERE M.TeamID = [Forms]![frmHDTicket]![cmbTeam].[column](0)
WHERE M.TeamID = [Forms]![frmHDTicket]

It would be a lot easier if it were the bound column ;-)

SQL syntax does not recognize the .xxx(y) construct. You
need to find another way to get the column 0 value to the
query. IMO, the easiest is to add a hidden text box and use
VBA code in the combo box's AfterUpdate event to set the
text box's Value. Then the query can refer to the text box
instead of the combo box:
WHERE M.TeamID = Forms!frmHDTicket![the hidden text box]

An alternative is to add create a public function in a
standard module that returns the combo box's column 0:

Public Function MyColumn0()
MyColumn0 = Forms!frmHDTicket!cmbTeam.column(0)
End Function

WHERE M.TeamID = MyColumn0()