Copy a record to a new Table before deleting that record

[lizI3]

Hi, I have a form that contains a subform. The Main form has a 1 to many releationship with the subform connected by the CLIENTID. On the main form there is a Delete button. This button deletes that record in the main and all related records in the subform. Before the record is deleted I want to copy the record including all related records in the subform to a new table.
I can get the main form and the first record in the subform to copy, but if there is more that one record in the subform with the same clientID it does not copy. Iam using VBA not SQL. this is my code :

Please what am I doing wrong ????

Private Sub cmdDelete_Exit(Cancel As Integer)
Dim SQL As String
Dim dbs As Database
Dim rst As Recordset
Dim stDocName As String

Dim stlinkcriteria As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Deletes", dbOpenDynaset)
Do While Me![CLIENTID] = Forms![Input]![Transaction Subform]![CLIENTID]
With rst
.AddNew
![CLIENTID] = Me![CLIENTID]
![MainTransit] = Me![MainTransit]
![StartDate] = Me![StartDate]
![FName] = Me![FName]
![LName] = Me![LName]
![CName] = Me![CName]
![AmendedDate] = Me![AmendedDate]
![Currency] = Forms![Input]![Transaction Subform]![Currency]
![Transaction Type] = Forms![Input]![Transaction Subform]![Transaction Type]
![Amount] = Forms![Input]![Transaction Subform]![Amount]
![Transit] = Forms![Input]![Transaction Subform]![Transit]
![Frequency] = Forms![Input]![Transaction Subform]![Frequency]
![Days of the Week] = Forms![Input]![Transaction Subform]![Days of the Week]
![Processing Day] = Forms![Input]![Transaction Subform]![Processing Day]
![Dr/Cr] = Forms![Input]![Transaction Subform]![Dr/Cr]
![Post] = Forms![Input]![Transaction Subform]![Post]
![Service Charge] = Forms![Input]![Transaction Subform]![Service Charge]
![Authorized By] = Forms![Input]![Transaction Subform]![Authorized By]
![Comments] = Forms![Input]![Transaction Subform]![Comments]
![Institute] = Forms![Input]![Transaction Subform]![Institute]
![Name of Customer to be Credited] = Forms![Input]![Transaction Subform]![Name of Customer to be Credited]
![TransationID] = Forms![Input]![Transaction Subform]![TransationID]
.Update
End With
DoCmd.GoToRecord , , acNext, 1
Loop

stDocName = "deletesfrm"
stlinkcriteria = "forms![Input] = " & " '" & Forms![Input]![Transaction Subform]![CLIENTID] & " '"
DoCmd.OpenForm stDocName, , , stlinkcriteria

 

[SBendBuckeye]

You might try something like this and see if it works. I am at work and cannot test so you may need to tweak it a little bit as this is off the top of my (flat or fat) head.

Assume you are saving your mainform to tblMain and the subform to tblSubform.

Dim strSQL As String

strSQL = "INSERT INTO tblMain " _
& "SELECT MainFormRecordSetName.* " _
& "FROM MainFormRecordSetName "
& "WHERE MainFormRecordSetName.ClientID = '" _
& MainFormTextBoxClientID & "';"

DoCmd.RunSQL strSQL

strSQL = "INSERT INTO tblSubform " _
& "SELECT SubformRecordSetName.* " _
& "FROM SubformRecordSetName "
& "WHERE SubformRecordSetName.ClientID = '" _
& MainFormTextBoxClientID & "';"

DoCmd.RunSQL strSQL

Of course you will want to test this without the delete step first to ensure it works properly.

Good Luck!

 

[lizI3]

Thanks for your help, but Iam still not sure how to do this. Iam working with forms not tables.

I have a form open called (Input) on it is a subform (Transaction Subform) in datasheet view. For every Client ID record on the input form (Parent) there is a minimum of 2 entries on the subform (Child) with the same ClientID. I want the whole record for that ClientID ( the 1 Parent and many Child records to be copied to a new table called Deletes before the record is deleted, so we have a record of what was deleted on any given day.

 

[mrf1xa]

Hi Liz

I think your problem is that you are copying the info from your form, not directly from the recordset, but not refreshing the form after each one to show the new info?

HTH Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[SBendBuckeye]

Sorry, I didn't mean to confuse you. The code above would be code that you would put in your cmd_Delete event sub above.

Good Luck!