Correct way of Clone data on a single form in Access (2 Viewers)

[nector]

Hi

I'm now doubting myself here on a single form data clone, I want to clone the underlying data on a single form called frmProducts so that people can edit that data anytime they want to.

Here is what I'm doing:

Private Sub CboSelectProducttoedit_AfterUpdate()
Me.Filter = "ProductID  = " & Me!CboSelectProducttoedit.Value & ""
Me.FilterOn = True

 Dim Records As DAO.Recordset

    Set Records = Me.Form.Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close

End Sub

it appears to be working without referencing frmProducts , that is where it is confusing me, maybe someone will be able to spot the mistake here.

 

[Minty]

Walk through the code as written and explain to yourself what each step is doing.
If you come to the conclusion that it isn't doing anything at all, I suspect you may be correct.

Why would you create a clone of a record to edit it?
Why not simply edit it directly.

 

[arnelgp]

the code is doing nothing and is Always on the first record of the filtered form.
it is best to open another form with the correct record to edit and edit it on that form.

 

[Edgar_]

Why would you create a clone of a record to edit it?
Why not simply edit it directly.

I believe a business rule would justify that if the original record shouldn't change and when a new record needs to be created based on it.

 

[Minty]

I believe a business rule would justify that if the original record shouldn't change and when a new record needs to be created based on it.

I get that, but the way this is worded, appears to be a copy to be edited for no obvious end goal.
A copy of record as a new record to be updated is not what was described.

 

[Josef P.]

Correct way of Clone data

To be on the safe side, a question of understanding:
What do you expect from Form.RecordsetClone?
If you expect a copy of the data, RecordsetClone is not the method you are looking for.

 

[KitaYama]

I believe a business rule would justify that if the original record shouldn't change and when a new record needs to be created based on it.

I don't really see any benefit for what you say. In the case you mentioned, you need an Audit table. Not keeping two or several copies of the same record in the same table any time an edit is necessary. It only brings the queries to their edge. And adds a lot of problems.

And by the way, no one says that code doesn't create a copy.

 

[Edgar_]

I don't really see any benefit for what you say. In the case you mentioned, you need an Audit table. Not keeping two or several copies of the same record in the same table any time an edit is necessary. It only brings the queries to their edge. And adds a lot of problems.

I'm talking about a situation where you need a new record that looks a lot like an existing one. You can just copy the original record and change the content in one or more fields to avoid re-work. A few examples that come to my mind are job applications, recipes, medical diagnosis, etc.; they are usually very long records that you can just duplicate to reuse their contents for a new record.

As for how to do it, I think an INSERT SELECT query would be a good option.

 

[KitaYama]

I'm talking about a situation where you need a new record that looks a lot like an existing one.

Then my bad. Applogies.

 

[nector]

Sorry I think people misunderstood my situation here, the form data entry property is set to "YES" meaning once data saved , there will not be any opportunity to correct any errors at all because the data is gone for good unless if someone has an opportunity to correct it directly in the tables which I do want anyone to do.

I have done a similar code in the past for a parent form and a child form the code works very for over ten years now no problem, the issue come on a single form how to achieve the same thing

Here is the code I'm trying to copy to a single form:

Private Sub CboCreditDebits_AfterUpdate()
Dim db As DAO.Database
Dim strSQL As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSQL = "SELECT intrlData FROM [tblCustomerInvoice] WHERE [InvoiceID] =" & [Forms]![frmCustomerInvoice]![CboCreditDebits]
With db.CreateQueryDef("", strSQL)
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
         Me.txtInvoicetoedit = .Fields(0).Value
    End If
    End With
End With
Set prm = Nothing
Set db = Nothing
Me.Filter = "InvoiceID = " & Me!CboCreditDebits.Value & ""
If (Me.txtInvoicetoedit <> "") Then
    Beep
    MsgBox "This document is already approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    Me.FilterOn = False
Else
    Me.FilterOn = True
End If
Dim Records As DAO.Recordset

    Set Records = Me![sfrmLineDetails Subform].Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
            
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
MsgBox "Please save the invoice after editing", vbInformation, "Internal Auditing Manager"
Me.Requery
End Sub

This is what I want to archive

The RecordsetClone property setting is a copy of the underlying query or table specified by the form's RecordSource property. If a form is based on a query, for example, referring to the RecordsetClone property is the equivalent of cloning a Recordset object by using the same query. If you then apply a filter to the form, the Recordset object reflects the filtering.

I hope this makes sense now