Duplicate Record to New Form, Delete Rec in Current Form

[LISAANN]

I would like to have a commnad button that selects, copies and saves a record from the current form and then open a new form and place the record there. Upon closing the new form I would like the record from the current form to be deleted. I have played around with the following code, but cannot seem to get it to come together. If I exclude "acFormAdd, acDialog" then I can duplicate. But I need this to "pause" the deletion. Since I am a novice programmer, I just can't seem to figure this out on my own.
Thanks!

Private Sub Command61_Click()
'Initialize error handling
On Error GoTo Proc_Error


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.OpenForm "GL_DEL", , , , acFormAdd, acDialog
DoCmd.RunCommand acCmdPasteAppend
DoCmd.RunCommand acCmdDeleteRecord

Proc_Exit:
Exit Sub

Proc_Error:
Select Case Err.Number
Case 2501 'Action Cancelled
Resume Proc_Exit
Case Else
MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly + vbCritical, "Runtime Error"
End Select


End Sub

 

[gwinn7]

I am assuming you have two different tables for this. Because, that is what you need to do what you are attempting...

On the current form, try somthing like the following and then debug...

Private Sub MyCommandButton_Click()

On Error GoTo ErrorMyCommandButton_Click

' Save the record first...
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

' Copy the data into your second table...
Docmd.SetWarnings False
Docmd.RunSQL "INSERT INTO MySecondTable " & _
"SELECT MyFirstTable.* " & _
"FROM MyFirstTable WHERE [MyID] = " & me.ID
Docmd.SetWarnings True

' Open the new form...
' Make sure that the second form has the recordsource set
' to the second table...
DoCmd.OpenForm "MyForm", , , "[MyID] = " & Me.ID

' Now, close this form...
docmd.Close "MyCurrentForm"

Exit_MyCommandButton_Click:
Exit Sub

Err_MyCommandButton_Click:
MsgBox Err.Description
Resume Exit_MyCommandButton_Click

End Sub

On the second form, delete the current record from one of the close events of the form.

Hope that helps. Again, you will need to do a little debugging.

Gary
gwinn7
A+, Network+

 

[thornmastr]

Would you mind explaining the purpose of this process? Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[LISAANN]

The reasoning behind this button is to delete a record from the "active" table and then archive it to "deleted" table. The reason I need a pop up form is so the user can explain why they will be deleting the record. The users of this database are not very accurate and delete things they do not mean to. I need a "check my butt" system to keep records for a little while but not use them in the active table. Make sense?