Using "All" in combo box

[robina]

I looked at the other postings for adding "all" as a choice in a combo box on a form and I have successfully added this choice to the combo box. My problem is how to return records for the all selection. The combo box is in the detail section of the main form "DataEntry" and is used to populate the subform based on a the [Assignee] field. This situation is unique in that the Assignee field is populated by a query the subform is based on called "DataEntrySub". In this query Assignee is determined by this:

Assignee: IIf([Join_tbl]![TaskAssignee] Is Null,[Join_tbl]![Administrator],[Join_tbl]![TaskAssignee])

My query for the combo box is

SELECT DISTINCT ComboQry.Assignee, ComboQry.Assignee FROM ComboQry UNION SELECT "All" AS Assignee, "*" as AllChoice FROM ComboQry
ORDER BY ComboQry.Assignee;

I can't figure out how to return all records. My first thought is in the criteria of the Assignee field in the subfrm query but I can't seem to get it correct.
All help is appreciated.

 

[MarkK]

Presumably you construct a SQL statement after the selection occurs in the combo. To return all the record you remove the WHERE clause from that SQL.

dim sql as string
dim where as string
sql = "SELECT * FROM Table "
if me.cboMyCombo <> "All" then 
   where = "WHERE SomeField = '" & me.cboMyCombo & "'"
end if
sql = sql & where

See how this code only tags on a where clause if the combe does not equal ALL?
hth

 

[robina]

I'm not grasping where to put this code. If its in the on change event of the combo I would need to reference the child field on the subform [assignee], that is linked to the parent field/combo box on the main form cboassignee I would think.