How do you reliably use RecordsetClone function?

[alexg]

Using VBA/Access 2003 -
Having a weird time using the recordsetclone function. I am seeing issues where an Order Form is printed and the total is not correct, as the values used for printing is coming from the database. The problem is down to this code as far as I can see and have tested. I see that when multiple main Form records are open, this code doesn't properly update/save the curOrderTotal value in the database if there was a change of the dblsubTot. I see through testing the dblsubtot is correct, but it’s not putting into the database the value. Only does it when one Order is open perfectly. This code is run right before you print the Order to update any changes in the database that may have happened on the Order, ie changed a price of a line item.

Any ideas how to modify code this so that it always saves to the SQL database properly and reliably? Am I using recordsetclone function correctly? The main order form has two subforms on it.

Any help appreciated….thx
Alex

Sub cswUpdateOrder(strOrdNum As String)
    On Error GoTo Error_cswUpdateOrder
    ' cswUpdateOrder "OE10024-000017"
 
    Dim dblTaxTotal As Double
    Dim dblTaxTotalRate As Double
    Dim dblSubTot As Double
    Dim dblSubTotRate As Double
    Dim dblTaxCodeRate  As Double
    Dim dblMHRTot As Double
    Dim strCriteria As String
 
    With Forms!frmOEOrder
        ' Set the Criteria for the Update.
        .RecordsetClone.FindFirst "strOrderNumber = """ & strOrdNum & """"
 
        dblTaxTotal = 0
        dblTaxTotalRate = 0
        dblSubTot = 0
        dblSubTotRate = 0
        dblTaxCodeRate = 0
        dblMHRTot = 0
 
        ' Update the !subDetail.Form.RecordsetClone Sales tax fields.
        strCriteria = "strOrderNumber = """ & .RecordsetClone!strOrderNumber & """"
        !subDetail.Form.RecordsetClone.FindFirst strCriteria
 
        Do Until !subDetail.Form.RecordsetClone.EOF
            !subDetail.Form.RecordsetClone.Edit
                ' Get the Labor
                dblMHRTot = dblMHRTot + (!subDetail.Form.RecordsetClone!dblManHoursRequired * !subDetail.Form.RecordsetClone!dblQtyOrdered)
                dblTaxCodeRate = Nz(!subDetail.Form.RecordsetClone!dblTaxCodeRate)
 
                ' Calculate the Tax
                If !strTransactionType = "RMA" Then
                    !subDetail.Form.RecordsetClone!curSalesTax = ((!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyAllocated) - (!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyAllocated) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100)
                    !subDetail.Form.RecordsetClone!curSalesTaxRate = ((!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyAllocated) - (!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyAllocated) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100)
 
                    dblTaxTotal = dblTaxTotal + CStr(((!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyAllocated) - (!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyAllocated) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100))
                    dblTaxTotalRate = dblTaxTotalRate + CStr(((!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyAllocated) - (!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyAllocated) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100))
                Else
                    !subDetail.Form.RecordsetClone!curSalesTax = ((!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyOrdered) - (!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyOrdered) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100)
                    !subDetail.Form.RecordsetClone!curSalesTaxRate = ((!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyOrdered) - (!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyOrdered) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100)
 
                    dblTaxTotal = dblTaxTotal + CStr(((!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyOrdered) - (!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyOrdered) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100))
                    dblTaxTotalRate = dblTaxTotalRate + CStr(((!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyOrdered) - (!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyOrdered) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100))
                End If
            !subDetail.Form.RecordsetClone.Update
 
            If !strTransactionType = "RMA" Then
                dblSubTot = dblSubTot + cswRoundAmount2(CStr((!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyAllocated) - (!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyAllocated) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)))
                dblSubTotRate = dblSubTotRate + cswRoundAmount2(CStr((!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyAllocated) - (!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyAllocated) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)))
            Else
                dblSubTot = dblSubTot + cswRoundAmount2(CStr((!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyOrdered) - (!subDetail.Form.RecordsetClone!curSalesPrice * !subDetail.Form.RecordsetClone!dblQtyOrdered) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)))
                dblSubTotRate = dblSubTotRate + cswRoundAmount2(CStr((!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyOrdered) - (!subDetail.Form.RecordsetClone!curSalesPriceRate * !subDetail.Form.RecordsetClone!dblQtyOrdered) * (!subDetail.Form.RecordsetClone!dblDiscount / 100)))
 
            End If
 
        !subDetail.Form.RecordsetClone.MoveNext
        Loop
 
        ' Calculate the totals for the Misc subform.
        !subMisc.Form.RecordsetClone.FindFirst strCriteria
        Do Until !subMisc.Form.RecordsetClone.EOF
            !subMisc.Form.RecordsetClone.Edit
                ' Get the Labor
                dblMHRTot = dblMHRTot + (!subMisc.Form.RecordsetClone!dblManHoursRequired * !subMisc.Form.RecordsetClone!dblQtyShipped)
                dblTaxCodeRate = Nz(!subMisc.Form.RecordsetClone!dblTaxCodeRate)
 
                ' Calculate the Tax
                !subMisc.Form.RecordsetClone!curSalesTax = ((!subMisc.Form.RecordsetClone!curSalesPrice * !subMisc.Form.RecordsetClone!dblQtyShipped) - (!subMisc.Form.RecordsetClone!curSalesPrice * !subMisc.Form.RecordsetClone!dblQtyShipped) * (!subMisc.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100)
                !subMisc.Form.RecordsetClone!curSalesTaxRate = ((!subMisc.Form.RecordsetClone!curSalesPriceRate * !subMisc.Form.RecordsetClone!dblQtyShipped) - (!subMisc.Form.RecordsetClone!curSalesPriceRate * !subMisc.Form.RecordsetClone!dblQtyShipped) * (!subMisc.Form.RecordsetClone!dblDiscount / 100)) * (dblTaxCodeRate / 100)
 
                dblTaxTotal = dblTaxTotal + (CStr((!subMisc.Form.RecordsetClone!curSalesPrice * !subMisc.Form.RecordsetClone!dblQtyShipped) - (!subMisc.Form.RecordsetClone!curSalesPrice * !subMisc.Form.RecordsetClone!dblQtyShipped) * (!subMisc.Form.RecordsetClone!dblDiscount / 100))) * (dblTaxCodeRate / 100)
                dblTaxTotalRate = dblTaxTotalRate + (CStr((!subMisc.Form.RecordsetClone!curSalesPriceRate * !subMisc.Form.RecordsetClone!dblQtyShipped) - (!subMisc.Form.RecordsetClone!curSalesPriceRate * !subMisc.Form.RecordsetClone!dblQtyShipped) * (!subMisc.Form.RecordsetClone!dblDiscount / 100))) * (dblTaxCodeRate / 100)
 
            !subMisc.Form.RecordsetClone.Update
 
            dblSubTot = dblSubTot + cswRoundAmount2(CStr((!subMisc.Form.RecordsetClone!curSalesPrice * !subMisc.Form.RecordsetClone!dblQtyShipped) - (!subMisc.Form.RecordsetClone!curSalesPrice * !subMisc.Form.RecordsetClone!dblQtyShipped) * (!subMisc.Form.RecordsetClone!dblDiscount / 100)))
            dblSubTotRate = dblSubTotRate + cswRoundAmount2(CStr((!subMisc.Form.RecordsetClone!curSalesPriceRate * !subMisc.Form.RecordsetClone!dblQtyShipped) - (!subMisc.Form.RecordsetClone!curSalesPriceRate * !subMisc.Form.RecordsetClone!dblQtyShipped) * (!subMisc.Form.RecordsetClone!dblDiscount / 100)))
 
        !subMisc.Form.RecordsetClone.MoveNext
        Loop
 
 
        .RecordsetClone.Edit
            ' Update Labor.
            .RecordsetClone("curMHRTotal") = cswRoundAmount2(dblMHRTot) * .RecordsetClone!curHourly
            .RecordsetClone!curMHRTax = .RecordsetClone("curMHRTotal")
 
            ' Update Freight Sales Tax.
            If IsNull(.RecordsetClone!curFreight) Or .RecordsetClone!curFreight = "" Then
                .RecordsetClone("curFreight") = 0
            End If
 
            ' Update Labor Sales Tax.
            If IsNull(.RecordsetClone!curMHRTotal) Or .RecordsetClone!curMHRTotal = "" Then
                .RecordsetClone("curMHRTotal") = 0
            End If
 
            ' Update the Total Sales Tax and Invoice Totals.
            .RecordsetClone("curSalesTax") = cswRoundAmount2((Nz(dblTaxTotal) + (Nz(Me!curFreight) * (Nz(Me!dblTaxFreightPercent) / 100)) + (Nz(Me!curMHRTotal) * (Nz(Me!dblTaxMHRPercent) / 100))))
            .RecordsetClone("curSalesTaxRate") = cswRoundAmount2((Nz(dblTaxTotalRate) + (Nz(Me!curFreight) * (Nz(Me!dblTaxFreightPercent) / 100)) + (Nz(Me!curMHRTotal) * (Nz(Me!dblTaxMHRPercent) / 100))))
            .RecordsetClone("curOrderTotal") = cswRoundAmount2(dblSubTot + Nz(.RecordsetClone!curSalesTax) + .RecordsetClone!curFreight + (.RecordsetClone!curMHRTotal))
            .RecordsetClone("curOrderTotalRate") = cswRoundAmount2(dblSubTotRate + (Nz(.RecordsetClone!curSalesTax) * .RecordsetClone!dblExchangeRate) + .RecordsetClone!curFreightRate + Nz(.RecordsetClone!curMHRTotal))
        .RecordsetClone.Update
 
    End With
 
Exit_cswUpdateOrder:
    ' Close Recordsets and destroy object variables.
    Forms!frmOEOrder.RecordsetClone.Close
    Forms!frmOEOrder.subDetail.Form.RecordsetClone.Close
    Forms!frmOEOrder.subMisc.Form.RecordsetClone.Close
    Exit Sub
Error_cswUpdateOrder:
    cswLogError Application.CurrentObjectName, "Error_cswUpdateOrder", Now, Err.Number, Err.Description
    Resume Next
End Sub
 

[MikeTheBike]

Hi

As far as I am aware, and according to the Access help, the RecordsetClone object is read only!?

Which may explain the apparent difference between the 'modified' Clone object and the actual data.

You could perhaps use the form's Recordset object, but the active record in the form will change as you move through the records.

However, not having a clue what you are trying to do, and looking at the code, I suspect there is a more logical way to do this.

MTB

[jforbes]

I agree with MikeTheBike, there is a more logical way to accomplish this. Most likely, putting all the calculations into a Query and using the Query as the basis of any Reporting or displaying of Totals. Someone will point out soon enough that it's not a good practice to store fields that can be calculated, and that it breaks normalization, but there are times that, even if it's a hard headed boss, that the calculated results can be saved along with the data.

So given that the calculations are to be stored, an update query still might be better as far as response and ease of development and maintenance. So you might want to consider it.

To increase the reliability of the code using RecordsetClones, I would first clone all the RecordSets into Variables and then only use the RecordSet Variables to perform the data manipulations. Mostly, because your dealing with a blackbox when it comes to when the Form and it's underlying RecordSet are updated. This is a mock-up of the code and a large portion of it has been removed, but it should give you the idea:

Sub cswUpdateOrder(strOrdNum As String)
...
     Dim oOrder as DAO.Recordset
     Dim oDetail as DAO.Recordset
     Dim oMisc as DAO.Recordset
 
     Set oOrder = Forms!frmOEOrder.RecordsetClone
     Set oDetail = Forms!frmOEOrder.subDetail.Form.RecordsetClone
     Set oMisc = Forms!frmOEOrder.subMisc.Form.RecordsetClone
     oDetail.MoveFirst
     oMisc.MoveFirst
 
     With oOrder 
         ' Set the Criteria for the Update.
         .FindFirst "strOrderNumber = """ & strOrdNum & """"
 
         dblTaxTotal = 0
         dblTaxTotalRate = 0
         dblSubTot = 0
         dblSubTotRate = 0
         dblTaxCodeRate = 0
         dblMHRTot = 0
 
         ' Update the !subDetail.Form.RecordsetClone Sales tax fields.
         strCriteria = "strOrderNumber = """ & oOrder!strOrderNumber & """"
         oDetail.FindFirst strCriteria
 
         Do Until oDetail.EOF
             oDetail.Edit
                 ' Get the Labor
                 dblMHRTot = dblMHRTot + (oDetail!dblManHoursRequired * oDetail!dblQtyOrdered)
                 dblTaxCodeRate = Nz(oDetail!dblTaxCodeRate)
... 
            oDetail.Update
... 
            oDetail.MoveNext
         Loop
... 
         .Edit
             ' Update Labor.
             !curMHRTotal = cswRoundAmount2(dblMHRTot) * !curHourly
...
         .Update
 
     End With
...

[alexg]

I have read the same, that recordsetclone is read only. I didn't write this code, but what they are trying to do is write the changed values to the database. It seems like what they are doing updates on the screen but doesn't update in the database when multiple records are open. I will reconsider and rewrite this last part of the code to use the recordset instead. I think it cause a conflict as it's already being used with the query and form in use.

Thanks for your help!

[alexg]

I didn't write this, just trying to fix it. I agree about calculations not being saved, but they did to speed up reports and the printing of orders and invoices.
I will look at your suggestion and at least use it to do the update on the final values that need to be put back in the database. For the most part all the code currently works, except for the last few lines don't actually write to the database. I think they just update the screen view values/form that you are looking at the moment.

Thanks for your valuable input.
Alex

[zmbd]

MikeTheBike hit the nail on the head.
From Microsoft Reference:

Form.RecordsetClone Property (Access2013)
You can use the RecordsetClone property to refer to a form's Recordset object specified by the form's RecordSource property. Read-only.

Jforbes has the right idea about setting the pointer for the RSClone, just as with the CurrentDB call, each time the call is directly made, it recreates the pointer and may create other issues in the long run. By setting the RSClone to the object-pointer you ensure that you are using the same object instance for each action AND when you release that instance of the object, that the memory and recordset are also released.

Finally, any changes made to the recordset that feeds the form not made via bound controls tends to not be properly reflected in the form (and subforms) until a requery is forced AND the recordsetclone most likely will not reflect any changes until it is also forced to requery.