Query runs in designer fails at runtime

[Dorian]

This query runs fine in the designer but fails at runtime with "This
expression is typed incorrectly, or is too complex to be evaluated etc etc."
Any ideas? I'm clueless.

SELECT C.LN, C.FN
FROM tblClient AS C INNER JOIN tblTeamMembers AS M ON C.ID = M.DevID
WHERE C.ActiveSW = True AND M.ActiveSW = True AND M.TeamID =
[Forms]![frmHDTicket]![cmbTeam]
ORDER BY C.LN, C.FN;

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Allen Browne]

The 'too complex' error is a generic message JET gives if it can't match
things up. In this case, it is most likely a data type mismatch.

If you open tblTeamMembers in design view what is the Data Type of the
TeamID field. Number? Text?

Next, open frmHDTicket in design view, and look at the properties of
cmbTeam. Typically, you might find:
Bound Column 1
Column Count 2
Column Widths 0
RowSource {some query that yields 2 fields}
Whichever is the bound column of the combo (even if it's unbound), it needs
to be the same data type as the TeamID. Otherwise JET won't be able to match
it and will give the data type mismatch.

If it does match (say they are both Number), but you still give the error,
open the query in design view. Choose Parameters on the Query menu. Access
opens a dialog. Enter the exact same name, and the correct data type, e.g.:
[Forms]![frmHDTicket]![cmbTeam] Long
This should help Access understand the intended type.