VB: Moving records from one table to another (insert/delete records)

Hello everyone.

I am trying to create a small program that will allow me to converge three separate Excel spreadsheets into a single Access database. My goal for this is to have a database which I will initially enter data into, and then by use of the interface I am programming, one will be able to send machines (add records to an Active table or move records into the Active table as needed), receive machines (move record from Active table to a Repair table), retire machines (move record from Active or Repair table to Retired table), or get a report which the user will be able customize based on the data that they want to see (user inputs data, then tables are filtered based on the information input, not filtering on empty fields).

I've got a form for Receiving machines, which consists of a text box to input a serial number, two buttons that start off active (Receive and Cancel), a data view linked to the Active table, and two buttons that start off disabled (Receive and Cancel--cmdReceiveYes and cmdReceiveNo, respectively). The general workflow for this is that a user will input a serial number, and the dataview will be filtered on this once the user clicks the Receive button. If the text box is blank, or if the serial number doesn't exist in the Active database, the user gets an error. If it matches, the dataview is filtered, the text box becomes read-only, the Receive and Cancel buttons become disabled, and the lower Receive and Cancel buttons (cmdReceiveYes and cmdReceiveNo) become enabled. If the user cancels at this point, the upper portion of the form (text box and command buttons) become enabled again, and the lower command buttons become disabled.

Here's where I'm stuck. If the user clicks the lower Receive button (cmdReceiveYes), I want the program to copy the appropriate row from the Active table to the Repair table and then delete the row from the Active table.

Here's what I have:



Obviously it's not what I need (otherwise, would I ask?), but I do need to figure this out. Any help is much appreciated.