The problem is that you need to use a query in code such as looping through a recordset or running an action query of some sort but you want to limit the number of records via some criteria.
However when you run the query in the query window you have to enter appropriate parameter values. Locating an appropriate value can be a pain in the ...
Actually what I always do when action or recordset queries get the slightest bit ugly I make a SELECT query with the required fields. By ugly I mean one join to another table, a lot of fields or whatever. I get this query working nicely showing all the records I want. But with no selection criteria. Now I can quickly see if the query looks proper now or six months from now when I forget what appropriate values were for the criteria. I save that query.
I then create the action or SELECT query in the QBE grid with dummy selection
criteria based on that query I just created. I do NOT save it. Instead I click
to the SQL code view and copy the SQL code to the clipboard. Now I go into my VBA module and paste the SQL code into the VBA code.. I then setup the strings with
the double quotes and line continuation for readability. I also setup the proper WHERE
clause criteria from my calling form or my code.
' decrement the QOH MatItemSizeQty record, ie change 5 to 3
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnHand = [miqQtyOnHand]-" & sngInventoryQty & " "
& _
"WHERE itdID=" & me!InvTransDetailID & ";"
CurrentDb.Execute strSQL, dbFailOnError
where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query.