Edit RecordSet not working; HELP!

[resistme]

I am editing the sample database Northwinds, using the recordsetwrapper functions to edit data in Ticket Details once invoicing orders in the Invoice Details table.
However, nothing is getting updated in the Ticket Details table.

The procedure is run from a macro button form when saving the data in the Invoice Details table

MACRO COMMAND
----------------------------

SetProductStatus(Me![cbTicket], 3)

FUNCTIONS
------------------------------
Public Sub SetProductStatus(TicketID As Long, ProductStatus As TicketItemInvoiceStatusEnum)

Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Ticket Details", "[Ticket ID] = " & TicketID) Then
With rsw.Recordset
If Not .EOF Then
.Edit
![Product Invoice Status ID] = ProductStatus
SetProductStatus = rsw.Update
End If
End With
End If

End Sub

FROM RECORDSETWRAPPER (Northwinds procedure)
---------------------------------------------------------------------
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

Set m_rs = CurrentDb.OpenRecordset(strSQL, RecordsetType, RecordsetOptions)
OpenRecordset = True

Done:
Exit Function

End Function

--------------------------------------------

It just doesn’t seem to find the data the data in Table Details to update the status to amend and not sure how to find out what bit is failing! I've been struggling with this for days!!!!!!!

HELP!!!

 

[ajetrumpet]

format your code. it is not readable

 

[resistme]

Hi Adam,

Sorry, tried to add the tabs for formatting, but when I posted it removed them all!!!!

 

[vbaInet]

[ c o d e ]
..... my code here
[ / c o d e ]

Without the spaces.

 

[resistme]

MACRO COMMAND
----------------------------
 
SetProductStatus(Me![cbTicket], 3)
 
FUNCTIONS
------------------------------
Public Sub SetProductStatus(TicketID As Long, ProductStatus As TicketItemInvoiceStatusEnum)
 
    Dim rsw As New RecordsetWrapper
    If rsw.OpenRecordset("Ticket Details", "[Ticket ID] = " & TicketID) Then
        With rsw.Recordset
            If Not .EOF Then
                .Edit
                ![Product Invoice Status ID] = ProductStatus
                SetProductStatus = rsw.Update
            End If
        End With
    End If
 
End Sub
 
FROM RECORDSETWRAPPER (Without error tracking)
------------------------------
 
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
 
    Set m_rs = CurrentDb.OpenRecordset(strSQL, RecordsetType, RecordsetOptions)
    OpenRecordset = True
 
Done:
    Exit Function
End Function

Sorry didn't know how to post code previously. Hope this is easier to read?

Hope you can help.

 

[resistme]

I'm not sure if I'm making it too complicated.

I have two tables; Ticket Details and Sales Details.

Once I add a TicketID item onto the Sales Details table, I want to look up the TicketID and update the Status field in the Ticket Details table.

I thought it should be simple (as the Northwinds database did it perfectly), but it just doesn't do anything.

Ideally I would open the record set with specific criteria, edit the correct field, and loop until EOF and stop.

Please help!!!!!!!

 

[SOS]

Please help!!!!!!!

Got a meeting for about an hour so if nobody else fixes your problem I will in a bit here.

 

[SOS]

Well, it would look like you are not updating the actual recordset

m_rs

anywhere. Your RecordsetWrapper class doesn't appear to have an update method (nor an edit method).

 

[resistme]

Well, it would look like you are not updating the actual recordset

m_rs

anywhere. Your RecordsetWrapper class doesn't appear to have an update method (nor an edit method).

Hi Sorry,

I didn't included them as I didn't think they were relevant (I am beginning to hate SQL/VBA with a passion - I could handle Filemaker Pro on my old classic Mac!) ....

here's the full code for the recordsetwrapper:

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
 
 
    Set m_rs = CurrentDb.OpenRecordset(strSQL, RecordsetType, RecordsetOptions)
    OpenRecordset = True
Done:
    Exit Function
 
End Function
 
 
 
Public Function Delete() As Boolean
 
 
    m_rs.Delete
    Delete = True
 
Done:
    Exit Function
 
End Function
 
Public Function AddNew() As Boolean
 
 
    m_rs.AddNew
    AddNew = True
 
Done:
    Exit Function
 
 
End Function
 
Public Function Edit() As Boolean
 
    m_rs.Edit
    Edit = True
 
Done:
    Exit Function
 
End Function
 
Public Function Update() As Boolean
 
 
    m_rs.Update
    Update = True
 
Done:
    Exit Function
 
End Function
 
Public Function MoveNext() As Boolean
 
 
    m_rs.MoveNext
    MoveNext = True
 
Done:
    Exit Function
 
End Function
 
Public Function CloseRecordset() As Boolean
 
    m_rs.Close
    CloseRecordset = True
 
Done:
    Set m_rs = Nothing
    Exit 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