Transfer One Record into New Table and Delete in Old Table



I need the Access form and when you click the"YES" I need to have the one RECORD to transfer the reocrd into a different table and Delete in the present table. Just the Record you are on.  Is this possible?
'//////////////////////////////////////
Private Sub cmdTransferToTable1_Click()
Dim iResponse As Integer

  iResponse = MsgBox("Are You Sure?", vbYesNoCancel, "Processing")

  Select Case iResponse
 
    Case vbYes

'//////////////////

Any help would greatly be appreciate!!


ca1358Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
First, run an append query that adds the record from the 'present table' into the 'different table'.
Second, run a delete query that deletes the record from the 'present table'.

Better, bundle both actions into a transaction such that they both succeed or fail together.

{using ado}

Dim cn as adodb.connection
Set cn = 'Need more info here.

With cn
  .BeginTrans
 
  .Execute "INSERT INTO tblDifferent (foo) SELECT blah FROM tblPresent WHERE ID=" & SomeID
  .Execute "DELETE FROM tblPresent WHERE ID=" & SomeID

  If err.number=0 then
    .CommitTrans
  Else
    .RollbackTrans
  End IF
End With
0
 
ca1358Author Commented:
Thank you!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
All Courses

From novice to tech pro — start learning today.