Insert & then Delete

[surajparmar]

Hi Everyone,

I have a query where I am inserting records from one table to another. I would then like to delete the original records from the other table. To perform this I currently have 2 queries but this then requires the user to input the package reference twice. I am running both queries from a button within my form.

Below is the SQL for the insert query:

INSERT INTO OrderSerialInfo ( OrderNo, SerialInfo, PackageRef )
SELECT DISTINCT (Orders.OrderNo), (StockSerialInfo.SerialInfo), (StockSerialInfo.PackageRef)
FROM Orders, StockSerialInfo, Stock
WHERE Orders.OrderNo=[Please enter OrderNo] And StockSerialInfo.PackageRef=[Please enter Package Reference];

Once this is complete the button then runs the second query to delete the original records:
DELETE *
FROM StockSerialInfo


WHERE PackageRef=[Please Re-enter PKG number];

My question is how do I combine these 2 queries so that the user only needs to enter the package reference once rather than twice? I'm hoping it's an easy one I've missed but not too sure.

Many thanks for all your help in advance,

Suraj

[NoellaG]

Hi everyone,

it's indeed an easy one: just put 2 unbound controls on the form, and let the user type the order and package reference there. Then in your queries you put the names of the control like Forms![name form]![name control] as selection criterium in stead of the parameters [Please enter OrderNo] or [Please enter Package Reference].

greetings
NG

[surajparmar]

Hi NG,

Many thanks for your quick response. I have created an unbound form and textboxes and updated my query to the following:


INSERT INTO OrderSerialInfo ( OrderNo, SerialInfo, PackageRef )
SELECT DISTINCT (Orders.OrderNo), (StockSerialInfo.SerialInfo), (StockSerialInfo.PackageRef)
FROM Orders, StockSerialInfo, Stock
WHERE Orders.OrderNo=Forms![TransferStock]![txtOrderNo] And StockSerialInfo.PackageRef=Forms![TransferStock]![txtPKGRef];


Once I enter the values on my form I click a button that runs the query but receive a popup box "Enter Parameter Value" which mentions the SQL parameter and a empty text box to type something in. After clicking OK I get a second popup for the second parameter. Is there a mistake in my SQL?