Update query Criteria

[aron.ridgway]

I have the following update query, im a little stuck in formatting my WHERE clause. I only want it to run an update if ALL the records in tblOrderDetail.StatusFK = 2. Currently it runs if only one has StatusFK = 2.

Im sure its simple but cannot figure it out.

UPDATE (tblOrder INNER JOIN (tblOrderDetail INNER JOIN tblQtySoFarTEMP ON tblOrderDetail.OrderDetailPK = tblQtySoFarTEMP.OrderDetailPK) ON tblOrder.OrderPK = tblOrderDetail.OrderFK) LEFT JOIN tblReceive ON tblOrder.OrderPK = tblReceive.OrderFK SET tblOrder.Statusfk = 2, tblReceive.DateFullyReceived = Now()
WHERE (((tblOrderDetail.OrderFK)=[forms]![frmReceive]![OrderPK]) AND ((tblOrderDetail.Qty)=[tblQtySoFarTEMP]![TotalReceive]) AND ((tblOrderDetail.StatusFK)=2));

 

[Rx_]

Let me suggest this to get you started.
1 Look up the forms underlying recordset (table or query).
2 Create a new Select Query using the fieds from the record set
3 Add the criteria (filter) in the select query and validate it returns the records needed by running the query
4 Change the query to Design veiw, change it to Update query
5 use the Update To row to determint what the query should be updated to
6 (if you are in a test db, run it once to validate the results)
7 In the query - change to SQL view - this is the SQL needed
8 Go back and substitute the Forms - fields for this SQL or use it to determine the correct sql syntax.