Add "All" to combo box, based on form criteria 1

[TimTDP]

In Access 2000 I use the following SQL statement to add an "All" record to the results of a combo box:

SELECT ProductTypeId, ProductType, AgencyId FROM tblProductType UNION SELECT Ast, Desc,"" FROM tblAsterisk
ORDER BY tblProductType.ProductType;


I know need to limit the results, based on the value of a field on the form. I have modified the above SQL statement to:

SELECT ProductTypeId, ProductType, AgencyId FROM tblProductType UNION SELECT Ast, Desc,"" FROM tblAsterisk
WHERE AgencyId = [Forms]![frmOrganisation]![cboCurrentAgency]
ORDER BY tblProductType.ProductType;

When I run the code I am prompted for "AgencyId"

Where have I gone wrong?

 

[Remou]

I think that should go:

SELECT ProductTypeId, ProductType, AgencyId FROM tblProductType WHERE AgencyId = [Forms]![frmOrganisation]![cboCurrentAgency]
UNION SELECT Ast, Desc,"" FROM tblAsterisk
ORDER BY tblProductType.ProductType;

There is a possibility you will have trouble with Desc as a field name as it is a reserved word.