CODEWITHSUNDEEP
sqlsql-servervbams-accessms-access-2010
Jamie Stuart Robin Parsons
Jamie Stuart Robin Parsons

Reputation: 1152

Update Multiple SQL Server Columns from Access 2010 Form

I have an Access Database which is linked to a SQL Server table. Within Access, I have a form with two multi-select list boxes (lets call their values x and y) and a text box (z).

When selecting the values and pressing a button, I want the form to update the rows where the values match the ones from the form:

UPDATE tableName SET ColumnToUpdate = [z] 
WHERE Column1 IN ([x1,x2...xn])
AND Column2 IN ([Y1,y2...yn])

Is there a method of doing this?

Upvotes: 1

Views: 110

Answers (1)

mnieto
mnieto

Reputation: 3874

You can enumerate selected items in each ListBox and build the SQL. Something like this

sql = "UPDATE tableName SET ColumnToUpdate = '" & txtZ & "' "
sql = sql & "WHERE Column1 IN (" & GetValuesFromList(listBoxX) & ") "
sql = sql & "AND Column2 IN (" & GetValuesFromList(listBoxy) & ")"

And the function GetValuesFromList:

Private Function GetValuesFromList(ListBox lst) as String
Dim Items As String
Dim Item As Variant

    Items = ""
    For Each Item In lst.ItemsSelected
        Items = Items & lst.ItemData(Item) & ","
    Next
    GetValuesFromList = Left(Items, Len(Items) - 1)
End Function

If the selected values in the list boxes are string values, you should modify the function to concatenate the quotes.

Upvotes: 1

Related Questions