moving records b/w tables with the click of a button

[BenHarper]

I have searched the help files and can't find anything on this idea. I want to make a button on a form that, when clicked, moves the current record from it's current table to a different one (ie: copy the record, paste it in the other table and delete the old one).
Both tables will have the same fields, so there won't be any trouble in that respect. Also, it would have confirm popup displayed ("Are you sure you want to move this record? YES/NO")
I am not very familiar with SQL etc. so examples of code would help a lot.
Thank you very much in advance,
Ben Harper
[}{]

 

[Warren Van Duesbury]

The easiest way is to create an Append query to append the data into your new table. Then create a Delete query to delete the record in the original table. Save both queries in your database as qryMoveIt and qryDeleteIt (or something meaningful). Next add the following code to a button on the form.

If MsgBox("Are you sure you want to move this record?", vbOKCancel, "Move Record?") = vbOK Then

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMoveIt"
DoCmd.OpenQuery "qryDeleteIt"
DoCmd.SetWarnings True


[This message has been edited by Warren Van Duesbury (edited 08-10-2000).]

[This message has been edited by Warren Van Duesbury (edited 08-10-2000).]

 

[BenHarper]

This works, but copies all of the records, as opposed to only the one that is active in the open form. I don't know how to apply a filter to take only the selected record.
Could someone tell me how to do this?

Thanks

 

[Neo90815]

Hi Ben,

I have a form with two fields and an Active-X Calendar. Once user select the date and input the time..then click on insert; the value of the two fields are sent to another form.

*************************************
Private Sub Command17_Click()
[Form_Call Manager].Date = Form_Calendar.Date2.Value
[Form_Call Manager].DateTime = Form_Calendar.Time.Value

DoCmd.Close

End Sub
*********************************


About the Prompt "Are You Sure?"..Sorry I don't know.

Good Luck!

 

[Pat Hartman]

Add parameters to BOTH queries.

Where tblKeyField = Forms!YourForm!YourControl;

Changing the names of course to match your table and form.