Duplicate / Copy / Clone Recordset (A2K)

[Schof]

Can someone remind me how to create a copy of a form's current record (or recordset) without maintaining the reference to the underlying table? Basically I want to copy the current record before deleting it so I can reference some of the field values afterwards in order to perform other operations.

I have tried the following but the recordsets become empty after deleting the current record.

dim rst as dao.recordset

set rst = Me.Recordset.Clone
set rst = Me.Form.RecordsetClone
set rst = Me.Form.Recordset.OpenRecordset

I know I can use variables, arrays, etc but I am specifically interested in how to do this using recordsets.

 

[pono1]

Don't know if ADO will work for you, but here's a sample...

    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    
    Dim strSQL As String
    strSQL = Me.RecordSource
    
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
  
'Test
    MsgBox "Rec 1 = " & rst.Fields(0) & " ; " & rst.Fields(1)
    rst.MoveNext
    MsgBox "Rec 2 = " & rst.Fields(0) & " ; " & rst.Fields(1)
'End test

    rst.Close
    Set rst = Nothing

Regards,
Tim

 

[namliam]

You almost have it...

set rst = Me.recordsetclone

Regards

The Mailman

 

[Schof]

I still get an error when I reference the recordset once the underlying record is deleted from the table.

What I am looking for is for the recordset to maintain no reference to the recordsource so I can use it's field values after the original record is deleted.

I have attached a tiny example of this so you can see what I am running in to. It was created using Access 2000 and requires a DAO reference.

 

[dcx693]

The recordsets (cloned or original) should not become empty after deleting a record unless that was the only record in the recordset.

If you clone a recordset, and you delete a record from the original, the clone should reflect the deletion as well, and vice versa, no?

 

[Schof]

If you clone a recordset, and you delete a record from the original, the clone should reflect the deletion as well, and vice versa, no?

That is the behaviour that is being exhibited. I am looking for a way to copy a recordset such that the cloned recordset and the original one are disjointed (I.e. changes made to either one are not reflected in the other)?

 

[dcx693]

Schof, I can't think of a good way to do this, but I'm pretty sure it won't work with recordset clones only because they tend to stay in synch.

You could always obtain the form recordsource using Me.Recordsource, then open a new recordset object using SQL string. Then you could delete the record from the form. The new recordset object would still have all the records, even the one that you just deleted from the form.

 

[pono1]

Schof: A little adjustment and you are there. Put your "get-recordset" code in the click event of one button. Put code that deletes a rec on your form in the click event of another button.

Also: Why don't you want to use an array? They're generally faster than recordsets...

Option Compare Database
Option Explicit

'Module level flag
    Dim MyRst As Boolean
    
'module Level rst object variable
    Dim rst As ADODB.Recordset

Private Sub CmdGetRst_Click()

  If MyRst = False Then

    MyRst = True
    
    Set rst = New ADODB.Recordset
    
    Dim strSQL As String
    strSQL = Me.RecordSource
    
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    
End If
  
'Test
    MsgBox rst.RecordCount
        
    MsgBox "Rec 1 = " & rst.Fields(0) & " ; " & rst.Fields(1)
    rst.MoveNext
    MsgBox "Rec 2 = " & rst.Fields(0) & " ; " & rst.Fields(1)

End Sub

Regards,
Tim

 

[Schof]

Tim, I tried your code but it still did not work.

dcx693, I created a function that I put in a module (so I could call it from any form) based on what you described but I still get the same results (see below).


Public Function copyRecordset(ByVal frm As Form) As dao.Recordset
On Error GoTo Err_copyRecordset

Dim rst As dao.Recordset ' <-- Using DAO so I don't have to add reference on all client machines
Dim strSQL As String

strSQL = frm.RecordSource

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
rst.Filter = frm.Filter <-- need to apply filter because it is not in the recordsource, right?

Exit_copyRecordset:
Set copyRecordset = rst
Exit Function

Err_copyRecordset:
MsgBox Err.Description
Resume Exit_copyRecordset

End Function

Any other ideas?

 

[pono1]

Can't win em all -- yet my code works on my PC (Wk2, A2K).

Keep at it and I'm sure, one way or another, a solution will present itself...

Regards,
Tim

 

[Pat Hartman]

I guess I don't see the point of this. Why don't you simply do what needs to be done and THEN delete the record? That seems to make more sense.

 

[Schof]

Tim, that is the same environment as I am running. Could you put your code in a small DB and upload it so I could see what differs between the two approaches?

Pat, I have greatly oversimplified my problem in order to try and figure out a solution. Essentially when the user wants to delete a record, I need to perform alot of clean-up in other tables where I was unable to define referential integrity rules. I have put all of that code in a module so I can call it from many different forms. The problem is that there are additional operations that need to be performed depending on the form where the deletion is being made from. So I call the module and then perform the operations, after the original record has been deleted. Hence the problem...

I can't reverse the order of the operations because it would not only confuse the user but it would also be problematic if they decided not to delete the original record. I don't want to add transactions to my application just for the sake of not having to add a few recordset field values to some variables. I was just hoping to do away with the variables and work with a recordset because it is very tedious to do when you are dealing with 50+ forms and each one has similar functionality.

 

[dcx693]

I can't reverse the order of the operations because it would not only confuse the user but it would also be problematic if they decided not to delete the original record.

That sounds a bit strange. Aren't you in trying to handle the situation that results because the user has decided to delete a record? And why involve the user at all once they've decided to delete a record? You can call code from a form's After Del Confirm event.

 

[Schof]

The sequence is as follows:

1) prompt user to delete record
2) on confirm, delete record and related records
3) depending on form where deletion is occuring, prompt user to delete other records
4) on confirm, delete other records

What I am trying to do is keep the recordset field values for the record deleted in 2) so I can use them for 4). So what I was trying to say in my last post was that it doesn't make sense for me to do 4) and then 2).

 

[Pat Hartman]

This is the real problem -

I need to perform alot of clean-up in other tables where I was unable to define referential integrity rules

RI should ALWAYS be left to the database engine.

1) prompt user to delete record
2) on confirm, delete record and related records
3) depending on form where deletion is occuring, prompt user to delete other records
4) on confirm, delete other records

2) delete related records, then delete main record
3) why can't you take care of this in a single operation? Why is it possible for the user to elect to not-delete what now have become orphan records?

 

[Schof]

RI should ALWAYS be left to the database engine.

If your application's data model is truly normalized I would agree with you. However, for many reasons (which I don't think we want to debate here) other than the fact that MS-Access has problems supporting resursive relationships, that will not always the case.

The function I created in the module performs many tasks (e.g. re-sequencing, propogating non-RI rule changes, etc) which is why I cannot do everything at once. The reason I am letting the user orphan records is because they (typically) are being used for look-up purposes and may (at the user's discretion) be used in the future.

Sigh...perhaps I should just stick to using variables.

 

[Schof]

Finally found it...

Me.Recordset.OpenRecordset