Copy Data from Selected Record and place in new db and table on another form?

[Quizton]

Need a little bit of help with this one, I have searched for other alternatives as well as searching codebank surprised its not in there since i remember something along the lines of moving one table to the next wich is kinda what Im doing here so if I could get some good help on this one It would be very helpful
I have a delete cmd and ATM it is set to delete the record all together. I recently added a new msg to the delete cmd "Employee has been moved to Past employee list"
So here is the question I was wondering were would I place this new code and how would I go about doing it? I have tried multiple formulas and I am missing something still. Here Is my code that already displays the message but have not placed the move record command to it yet.
vbCode___________________________________________________________
Private Sub cmdDelete_Click()
errormsg = MsgBox("Are You Sure You Want To Delete This Employee", vbYesNo, "Delete Record")
If errormsg = vbYes Then
errormsg = MsgBox("Employee Has Been Moved To Past Employee List", vbOKCancel, "Delete Record") <--Here is were I must move to new db table?
If errormsg = vbOK Then
rs.Delete <--Maybe a MoveTo PastRecord I cant quite get it right?
Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
list
txtFirstName.Text = vbNullString
txtFirstName.Enabled = True
txtLastName.Text = vbNullString
txtLastName.Enabled = True
txtEmployed.Text = vbNullString
txtEmployed.Enabled = True
txtUnemployed.Text = vbNullString
txtUnemployed.Enabled = True
cmdSave.Enabled = True
cmdCancel.Enabled = True
cmdadd.Enabled = True
Else
Exit Sub
End If
End If
End Sub
______________________________________________________________
Well there is a sample of what I have at this current time I will gladly take any info on this matter and thanks in advance to all you pro's and good helpers out there
thanks,
Quiz

[dee-u]

Are you looking for .MoveLast,.MoveNext,.MoveFirst and .MovePrevious commands?

[Quizton]

Something along the lines of When I delete the record it wont really delete but then it will move it to a different table(like a past records list)
so it will delete from the normal table I have "EMPDATA" and then move to "PastEMPDATA"
Thanks again Dee-u

[dee-u]

An example...

INSERT INTO Carrier ( CarrierCode, CarrierName, CarrierAdd1, CarrierAdd2, CarrierAdd3, CarrierAdd4, CarrierAdd5 )
SELECT Carrier.CarrierCode, Carrier.CarrierName, Carrier.CarrierAdd1, Carrier.CarrierAdd2, Carrier.CarrierAdd3, Carrier.CarrierAdd4, Carrier.CarrierAdd5
FROM Carrier
WHERE (((Carrier.CarrierCode)=1));

[dee-u]

Isn't the logic is supposed to be something like this?

Private Sub cmdDelete_Click()
    If MsgBox("Are You Sure You Want To Delete This Employee", vbYesNo, "Delete Record") = vbYes Then
        'move the record before deleting
        rs.Delete
        Set rs = db.OpenRecordset("EMPDATA", dbOpenTable)
        List
        txtFirstName.Text = vbNullString
        txtFirstName.Enabled = True
        txtLastName.Text = vbNullString
        txtLastName.Enabled = True
        txtEmployed.Text = vbNullString
        txtEmployed.Enabled = True
        txtUnemployed.Text = vbNullString
        txtUnemployed.Enabled = True
        cmdSave.Enabled = True
        cmdCancel.Enabled = True
        cmdadd.Enabled = True
    End If
End Sub

[crater]

you want to
copy the employees record from "Present employee DB" or probaly present employee table"
add the employees record to a "past employeeDB" or probaly "past employee Table"
delete employees record from "Present employee DB" or table

[dee-u]

You know how to add a record in a database right? It's just that, add it to the other table then delete it...

[Hack]

I'm very confused on this.

If errormsg = vbOK Then
Set rs = db.OpenRecordset("PastEMPDATA", dbOpenTable)
rs.update then
Set rs = db.Openrecordset("EMPDATA",dbOpenTable)
rs.Delete

What type of objects are PASTEMPDATA and EMPDATA? Those should be SQL queries, but I'm guessing they are not.