Query Based on Combo Box - ARGH!

[mkallover]

I have a form called "frmMain" and within that form there is a simple combo box (only one column) called "CustID". I'm trying to write a query that will use the selected CustID in the WHERE clause:

TRANSFORM Count(EXTERNAL_TRANSACTION_ID) AS [COUNT]
SELECT CUSTOMER_ID
FROM ARGUS_B51
WHERE FIR_TRANSACTION_CD In ("F1","F2","F3") AND CUSTOMER_ID = Forms!frmMain!CustID
GROUP BY CUSTOMER_ID
PIVOT FIR_TRANSMISSION_STATUS_CD;

When I try to run that query I get the message:

"The Microsoft Office Access database engine does not recognize 'Forms!frmMain!CustID'as a valid field name or expression.'

I've checked for typos in the form and combo box names and everything else I can think of. What the heck am I doing wrong here? I'm using Access 2007.

 

[dhookom]

You must specify the data type of your form control value in the Query -> Parameters.

Duane
Hook'D on Access
MS Access MVP

 

[mkallover]

Man, that did it.

Thanks.

 

[dhookom]

You might also want to change your query to use the Column Headings property rather than the where criteria. Also, I don't think you need to GROUP BY CUSTOMER_ID since you are selecting only one.

TRANSFORM Count(EXTERNAL_TRANSACTION_ID) AS [COUNT]
SELECT CUSTOMER_ID
FROM ARGUS_B51
WHERE CUSTOMER_ID = Forms!frmMain!CustID
PIVOT FIR_TRANSMISSION_STATUS_CD IN ("F1","F2","F3");

This will assure the three columns will always be generated even if on of the status codes is missing. IIRC, you can remove the PARAMETER(s) declaration if you use Column Headings however I prefer the inclusion of the parameters.

Duane
Hook'D on Access
MS Access MVP