Hi, I have a problem query. It looks at a text box on a form to get the value it needs for a where clause. A simple thing that I have set up many times before. Unfortunately, this query is not working because it cannot seem to resolve the value of the text box. If I remove the form reference from the crieria pane of the query, the query brings back lots of records. If I replace the form reference with the value that I know is in the text box on the form, the query works (i.e., it brings back the data that I am expecting). The query is called from code. At the point where it is called I have stopped the code and used the immediate window to show the value returned by the form reference that I am using in the criteria pane (? [Forms]![frm_EnterPickResults]![BatchIDx]) and the expected data (in this case a batch ID) comes back. If I copy this batch ID, that I have acquired in the immediate window, back into the query - the query works.
As far as I can tell, the form control reference is not being resolved by the query, even though it is resolved if typed into the immediate window. A very similar query, that is run from code in the previous line, which uses the same form control as a parameter, works fine.
Is this a bug?
This is the query that does not work:
INSERT INTO tbl_Packaging ( BatchID, OrderID, apar_id, LineNum, PackType, ProdCode, QtyToPick, PickDate )
SELECT tbl_PickAndPackBatchDetail.BatchID, tbl_PickAndPackBatchDetail.order_id, tbl_PickAndPackBatchDetail.apar_id, tbl_PickAndPackBatchDetail.line_no, "Pouches" AS PackType, qry_Relation_Pouches.rel_value, Sum(tbl_PickAndPackBatchDetail.QtyPicked) AS SumOfQtyPicked, First(Date()) AS PickDate
FROM (tbl_PickAndPackBatchDetail LEFT JOIN qry_AlgArticleClient ON tbl_PickAndPackBatchDetail.article = qry_AlgArticleClient.article) LEFT JOIN qry_Relation_Pouches ON qry_AlgArticleClient.article_id = qry_Relation_Pouches.article_id
WHERE (((qry_Relation_Pouches.rel_value) Is Not Null And (qry_Relation_Pouches.rel_value)<>"0" And (qry_Relation_Pouches.rel_value)<>"") AND ((tbl_PickAndPackBatchDetail.BatchID)=[Forms]![frm_EnterPickResults]![BatchIDx]) AND ((tbl_PickAndPackBatchDetail.QtyPicked)>0))
GROUP BY tbl_PickAndPackBatchDetail.BatchID, tbl_PickAndPackBatchDetail.order_id, tbl_PickAndPackBatchDetail.apar_id, tbl_PickAndPackBatchDetail.line_no, "Pouches", qry_Relation_Pouches.rel_value;