SQL with selected items of a listbox... 1

[snayjay]

Ok, I have a listbox called BLIST (stands for Buildings List). It is set to multi-select and I want for users to be able to choose multiple buildings from this listbox and then click a status button. Either 'Inhabited' or 'Uninhabited'. Then I wanted to apply the new status to the recordset of all the selected items thru SQL. I thought the best way was a For Loop... Below is what I tried, but it didn't work. Any clue how to fix it?

[COLOR=blue]Private Sub[/color] INHABITEDcmd_Click()

   [COLOR=blue]Dim[/color] varItem [COLOR=blue]as Variant[/color] [COLOR=green]'itemdata number[/color]
   [COLOR=blue]For each[/color] varItem [COLOR=blue]In[/color] BLIST.ItemsSelected
      [COLOR=blue]dim[/color] addSQL
      addSQL = "UPDATE Btbl SET Btbl.INUN = 'Inhabited' WHERE_
      Btbl.BID = BLIST.ItemsSelected(varItem).Value 
      DoCmd.SetWarnings [COLOR=blue]False[/color]
      DoCmd.RunSQL (addSQL)
   [COLOR=blue]Next[/color]

[COLOR=blue]End Sub[/color]

Didn't know if it would work I just thought I'd give it a try. I don't quite know how to identify the value of the Item Selected from the Listbox; when there are multiple selected... otherwise it would just be <listboxname>.value. My listbox has 5 columns in it and the bound column is the BID (Primary Key from the Btbl that I'm trying to edit thru SQL). Hope that all makes sense. Any help would be appreciated.

v/r

Snayjay

 

[JerryKlmns]

Your code executes so many times as the items selected in a For ... Next loop. The example bellow uses that For ... Next loop to build an sql statement and execute it once. All items selected values, are included in the In operator used at the WHERE clause.

Private Sub INHABITEDcmd_Click()
Dim mySQL As String
Dim InOperator As String
Dim varItem as Variant 'itemdata number

For each varItem In BLIST.ItemsSelected
InOperator = InOperator & ", " BLIST.ItemData(varItem)
Next
InOperator = Mid(InOperator, 3)
mySQL = "UPDATE Btbl SET Btbl.INUN = 'Inhabited' WHERE Btbl.BID In (" & InOperator & ")"
DoCmd.SetWarnings False
CurrentDB.Execute mySQL, dbFailOnError
DoCmd.SetWarnings True

End Sub

 

[snayjay]

You are awesome man... worked like a charm. The WHERE clause ended up as

WHERE Btbl.BID In (23, 24, 25, 26, 27, 28, 29)

I didn't know you could do it that way... thanks for the tip.

AJ