Solved Update a record with a recordset

[ClaraBarton]

I have a continuous form that's a check register and the query behind it is not updatable.
Double clicking in any field transfers the record fields to unbound fields in the header.
I have saving a new record working but I'm a little lost on updating an existing record.
How do I make sure the update goes back to the same record?
In other words, if the ID is null a new record is created in the table.
The second part (else) is what doesn't work:

Private Sub btnSave_Click()
    Dim rst As DAO.Recordset
    Dim strSql As String
    strSql = "Select * from tblTransactions " & _
        "WHERE TransactionID = " & Nz(Me.txtTransactionID, 0)
    Set rst = CurrentDb.OpenRecordset(strSql)
   
    If IsNull(Me.txtTransactionID) Then
        With rst
            .AddNew
            fAccountID = Me.txtfAccountID
            TransactionID = Me.txtTransactionID
            CkDate = Me.txtDate
            Num = Me.txtNum
            Payee = Me.txtPayee
            Debit = Me.txtDebit
            Credit = Me.txtCredit
            Cleared = Me.txtCleared
            .Update
        End With
    Else
        With rst
            .Update  'update or cancel without add new or edit
            fAccountID = Me.txtfAccountID
            TransactionID = Me.txtTransactionID
            CkDate = Me.txtDate
            Num = Me.txtNum
            Payee = Me.txtPayee
            Debit = Me.txtDebit
            Credit = Me.txtCredit
            Cleared = Me.txtCleared
        End With
    End If
    Set rst = Nothing
End Sub

 

[theDBguy]

You need to make sure you're on the right record before using .Update. You could probably use .FindFirst or something.

 

[plog]

Debit and credits shouldn't be in separate fields in your table. You should just have a TransAmount field and either use positive or negative numbers to differentiate. Makes obtaining a balance easier.

 

[cheekybuddha]

You aren't referencing fields in the recordset rst, just the fields in your form with those field names.

Either add a bang (!) before each field name, or reference it using the syntax: .Fields("FieldName") = ...

But also your AddNew and Update logic seems off too.

 

[arnelgp]

Private Sub btnSave_Click()
    Dim rst As DAO.Recordset
    Dim strSql As String
    strSql = "Select * from tblTransactions " & _
        "WHERE TransactionID = " & Nz(Me.txtTransactionID, 0)
    Set rst = CurrentDb.OpenRecordset(strSql)
 
    With rs
        If IsNull(Me.txtTransactionID) Then
            .AddNew
        Else
            .Edit
        End If
        !fAccountID = Me.txtfAccountID
        !CkDate = Me.txtDate
        !Num = Me.txtNum
        !Payee = Me.txtPayee
        !Debit = Me.txtDebit
        !Credit = Me.txtCredit
        !Cleared = Me.txtCleared
        .Update
    End With
    rs.Close
    Set rst = Nothing
End Sub

 

[cheekybuddha]

@arnelgp - rs / rst

 

[Pat Hartman]

Rather than copying the fields to unbound controls on the form's header, just open a one record form. Then you can use a bound form and do proper validation and ignore all the other code that unbound updating requires.

DoCmd.OpenForm "frmMDOffce", , , "MDID = " & Me.MDID, , acDialog
Me.Refresh

I open popup forms in dialog mode so that focus always stays on the popup until it closes. Then when focus returns to the list form, refresh it so you see any changes made by the popup.

 

[ClaraBarton]

THANK YOU, ARNELPG! So simple and so exactly what I need.

 

[ClaraBarton]

I do have a pop up form and that's where I go for categories, etc. But I wanted this option too.

 

[arnelgp]

@arnelgp - rs / rst

 

[cheekybuddha]

You have a typo in your code in Post #5:

' ...
    Set rst = CurrentDb.OpenRecordset(strSql)
 
    With rs
' ...
    rs.Close
    Set rst = Nothing
' ...