Errors updating GetRecordsetClone for subform

[resistme]

Hi,

On clicking a buton in Access Form, I want to update the status of the subform (which a based on a query of two different tables). However nothing happens. When I include an rsw.update instruction I get an error saying:

The most recent error number is 3020. It's Message text is: Update or CancelUpdate without AddNew or Edit
​

What's wrong!

Private Sub cmdCreateInvoice_Click()
    Dim InvoiceDetailID As Long
    Dim InvoiceID As Long
    Dim TicketID As Long
    Dim UpdateStatus As Boolean
 
    InvoiceID = Nz(Me![Invoice ID], 0)
    Dim rsw As New RecordsetWrapper
            With rsw.GetRecordsetClone(Me.sbfInvoiceDetailsSubform.Form.Recordset)
                 While Not .EOF
                    rsw.Edit
                    ![Product Invoice Status ID] = TicketItem_Invoiced
                    ![Return Invoice Status ID] = TicketItem_Invoiced
                    ![Billed Quantity] = 2222             ' This is just to text if I can change ANY text in subform!!! 
                    rsw.MoveNext
                Wend
            End With
    Forms![Invoice Details]![sbfInvoiceDetailsSubform].Requery
 
End Sub
 
 
Option Compare Database
Option Explicit
Private m_rs As DAO.Recordset2
 
Public Function GetRecordsetClone(rs As DAO.Recordset2) As DAO.Recordset2
    If Not m_rs Is Nothing Then
        Debug.Assert False ' This is only designed to be used once
    Else
        Set m_rs = rs.Clone
        Set GetRecordsetClone = m_rs
    End If
End Function
 
Public Function OpenRecordset(Domain As String, _
                              Optional Criteria As String = "1=1", _
                              Optional OrderBy As String, _
                              Optional RecordsetType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                              Optional RecordsetOptions As DAO.RecordsetOptionEnum _
                              ) As Boolean
 
 
    If Not m_rs Is Nothing Then
        ' Close the recordset so it can be re-used
        CloseRecordset
    End If
 
    Dim strSQL As String
    strSQL = "SELECT * FROM [" & Domain & "] WHERE " & Criteria
 
    If OrderBy <> "" Then
        strSQL = strSQL & " ORDER BY " & OrderBy
    End If
 
    On Error GoTo ErrorHandler
    Set m_rs = CurrentDb.OpenRecordset(strSQL, RecordsetType, RecordsetOptions)
    OpenRecordset = True
Done:
    Exit Function
ErrorHandler:
    ' verify the private Recordset object was not set
    Debug.Assert m_rs Is Nothing
 
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.OpenRecordset", "strSQL = " & Chr(34) & strSQL & Chr(34)) Then Resume
End Function
 
Public Function Delete() As Boolean
    On Error GoTo ErrorHandler
 
    m_rs.Delete
    Delete = True
 
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.Delete") Then Resume
End Function
 
Public Function AddNew() As Boolean
    On Error GoTo ErrorHandler
 
    m_rs.AddNew
    AddNew = True
 
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.AddNew") Then Resume
End Function
 
Public Function Edit() As Boolean
    On Error GoTo ErrorHandler
 
    m_rs.Edit
    Edit = True
 
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.Edit") Then Resume
End Function
 
Public Function Update() As Boolean
    On Error GoTo ErrorHandler
 
    m_rs.Update
    Update = True
 
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.Update") Then Resume
End Function
 
Public Function MoveNext() As Boolean
    On Error GoTo ErrorHandler
 
    m_rs.MoveNext
    MoveNext = True
 
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.MoveNext") Then Resume
End Function
 
Public Function CloseRecordset() As Boolean
    On Error GoTo ErrorHandler
 
    m_rs.Close
    CloseRecordset = True
 
Done:
    Set m_rs = Nothing
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.CloseRecordset") Then Resume
End Function
 
Public Property Get Recordset() As DAO.Recordset2
    Set Recordset = m_rs
End Property
 
Private Sub Class_Terminate()
    If Not m_rs Is Nothing Then
        m_rs.Close
        Set m_rs = Nothing
    End If
End Sub

Many thanks

 

[Access_guy49]

I think your Error occures because your trying to "update" a recordset without telling the program if your updating an existing recordset (edit) or adding a new recordset.

If your goal is this line though

Forms![Invoice Details]![sbfInvoiceDetailsSubform].Requery

Why don't you just use an SQL query? Construct your query and then set it as the source, then all you would have to do is requery whenever you wanted it to update.

you already create the sql query in

Public Function OpenRecordset

 

[resistme]

Hi,

I do have rsw.Edit where rsw is the current recordset, so don't see why it's not working!

Afterwards, I have the requery just to force it to update so I can confirm the changes were made!

Argh!!!!!!!!!!!!

 

[resistme]

I think your Error occures because your trying to "update" a recordset without telling the program if your updating an existing recordset (edit) or adding a new recordset.

I have got

 rsw.edit

in the code where rsw is the current record set of the subform so not sure why it's not working. I did include a count to test that it was going through the record set, it's just not updating any values! Arghhhhhhhhh

 

[SOS]

Umm, you don't update the RecordsetClone, you update the recordset. The clone is just a reflection of the current recordset.