One method of using SQL queries in code as parameter queries can be difficult to work with in code

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.

[ AccessMain ]