moving record from one table to another

[Parasyke]

Thanks in advance... I need to on a subform in datasheet view to be
able to, instead of delete a record, move that record to another
table. I can if needed turn the subform into a continuous form
emulating a datasheet view. Any clues on how to do this? Thanks! ...
Dav

[storrboy]

On Feb 14, 10:54 pm, "Parasyke" <[e-mail]:

Thanks in advance... I need to on a subform in datasheet view to be
able to, instead of delete a record, move that record to another
table. I can if needed turn the subform into a continuous form
emulating a datasheet view. Any clues on how to do this? Thanks! ...
Dav

Unless there is a program logic reason to do so, I prefer to mark
records as deleted instead of actually deleting them.
You can filter the subform to show records that are not 'deleted' and
they can be retained for history.
Other wise what you need to do is create your own Delete shortcut menu
item or command button and disable deletion of records in the subform
(so that people don't use the actual delete functions). Add something
like the following to a click event of whatever delete button you use
(maybe include a confirmation msgbox).

On Error GoTo stoprun
Dim sql1 As String
Dim sql2 As String
Dim dbs As Database
Dim wrk As Workspace

Set dbs = CurrentDb
Set wrk = DBEngine.Workspaces(0)

sql1 = "INSERT INTO ...[rest of query] WHERE...somefield=" & Me!
subform.Form![criteria field] & "));"
sql2 = "DELETE ...[rest of query] WHERE...somefield=" & Me!
subform.Form![criteria field] & "));"

wrk.BeginTrans 'Start transaction in case one of these fails
dbs.Execute sql1, dbFailOnError
dbs.Execute sql2, dbFailOnError
wrk.CommitTrans
Me!subform.Form.Requery

exit_here:
Set wrk = Nothing
Set dbs = Nothing
Exit Sub

stoprun:
MsgBox Err.Number & " - " & Err.Description
wrk.Rollback
Resume exit_here