I want to run a make-table query with a parameter, using a command button click event on a form showing a single record. The parameter value needs to be inputted automatically using the value of a control in the current record of the same form. Can this be done with a macro event? If so, what does the macro look like? If not, how do I express the WHERE clause of the SQL statement which would run the query in a VBA sub procedure attached to the button?
Details: I've got a customer orders table and a customer order details table. The customer order user input form shows records from the customer order details table as a subform, with the customer order ID as the linking field. After the details are inputted, I want to post those details to a stock movements table, but need to do some sales unit conversions along the way. So I'm trying to run a make-table query which, besides doing some unit and price calculations, only creates records for the current customer order, and then to run an append query which appends these cooked details from the temporary table to the stock movements table (which exists to record the ins and outs needed to create a current stock level for each inventory item). I tried to append to the stock movements table directly from a select query which did the order details unit conversion calculations , but couldn't get calculated query fields to match up with the destination table fields. Thus the make-table query intermediate step, which needs a parameter so that it doesn't recreate the entire customer order details table (potentially infinite in length) each time it runs.