CODEWITHSUNDEEP
vbams-access
TheRedOne
TheRedOne

Reputation: 165

Updating Multiple Rows using .edit in vba access 2007

I hope I can ask this right. I have a listbox on a form (access 2007) and have it set on "simple" so I can multi-select. I am trying to update multiple columns in a table, based on the selections in the listbox. I have a few textboxes that I want to use as the information to update the table. I have a loop that is only updating the first record in the table no matter how many items are selected. I think I understand why it's only updating the first record from my loop but am not sure

Dim db  As DAO.Database
Dim rs  As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)

Dim i As Variant

    For Each i In Listbox.ItemsSelected

    With rs
    .Edit

     !Col1 = Me.Textbox1
     !Col2 = Me.Textbox2
     !Col3 = Me.Textbox3

    .Update

    End With
    Next

I assume this is because I am not specifying the "where" in the loop I want my table updated, but I have no idea how to do this in this loop. I would have 3 columns in the listbox (at positions 1, 3 and 4) that all need to be included to specify which records in the table need to be updated. I have tried this as well using an sql query with DoCmd.RunSql but it seems impossible to change the focus of the ListIndex mid-query. Aplogies for my lack of knowledge I am pretty new to visual basic. Please Help

Upvotes: 0

Views: 4920

Answers (1)

Trace
Trace

Reputation: 18889

Simplified example how I would give it a try if I understood correctly: 

Sub MultiSelect_Listbox()

Dim lCnt as Long
dim lID as long     
dim sSQL_Update as string    

dim sText_1 as String
dim sText_2 as String
dim sText_3 as String

dim bSuccess as Boolean

sText_1 = me.txt_Textbox_1
sText_2 = me.txt_Textbox_2
sText_3 = me.txt_Textbox_3    

With Me.lst_Listbox
    For lCnt = 1 To .ListCount
        If .Selected(lCnt) Then
            lID = .Column(0, lCnt - 1)
            'Example update for 1 column
            sSQL_Update = "UPDATE T_TABLE SET COL_TEXT_1 = '" & sText_1 & "' WHERE ID = " & lID & ";"
            bSuccess = Update_Statement(sSQL_Update)
        End If
    Next
End With
End Sub

Public Function Update_Statement(sUpdate_Stmt) As Boolean

Dim db                          As Database

Set db = CurrentDb
db.Execute (sUpdate_Stmt)
Update_Statement = True

End Function

Upvotes: 1

Related Questions