Updating Access from Excel

peterhw

New Member
Joined
Jan 23, 2012
Messages
39
I have followed several links here and elsewhere and just can't figure out what I am doing wrong!
I select items from a listbox display and then wish to update (2 lines for my testing).
The first iteration appears to go through all steps when in debug mode BUT does not actually update the database. The second iteration FAILS at the update command and goes to ErrHandler:. ID is a unique record ID in Access and Trans_Comment is a valid field name. The records have not been updated or deleted
The error is
3021:Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

<tbody>
</tbody>

I have had something similar working previously then made changes.
Code:
Sub Category_update()
Dim int1 As Integer, Item_ID As Integer
Dim str0 As String, str1 As String, str2 As String, str3 As String, str4 As String, mycomment As String
On Error GoTo ErrHandler
    str1 = DBtable
    Connect2Access_Init
    For int1 = 1 To ListBox2.ListCount - 1
        If ListBox2.Selected(int1) = True Then
            str1 = ListBox2.List(int1, 4)
            MsgBox ("List Item ID = " & str1 & " is selected to update")
            mycomment = "Utilities Energy"                                  '   hard coded for tesTing
            Set rs = New ADODB.Recordset
            mycmd = "SELECT * FROM " + DBtable + " WHERE ID = " + str1 + ";"
            With rs
                .Open Source:=mycmd, ActiveConnection:=cn, LockType:=adLockOptimistic
                .Fields("Trans_Comment") = mycomment
                .Update
                .Close
            End With
        End If
    Next int1
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    Exit Sub
ErrHandler:
    Error_Handler ("Category_update")
End Sub

any help appreciated
 
Last edited:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top