List Box Update

[looloo]

I have a form with a combo box, a list box and a button. The data in the combo box is from FieldA from Table1 and the list box is populated from a query that selects all the records in Table2 that have a null IDField. Table2 is related to Table1 by the index for FieldA (IDField)...

On click, I want the selected record(s) from the list box to have their IDField updated using the contents of the combo box.

I'm pretty inexperienced and can't imagine how to go about this. Hope I explained it well enough.

[stmoong]

I'm assuming you want to update Table2 when you click on the button. You'll need to write some VBA code for the On Click event of the button.

Basically, loop through the selected items in the ListBox and update using value from the combo box.

Here's a skeleton code to get you started.

 
Dim oItem As Variant
 
If Me!mylistbox.ItemsSelected.Count <> 0 Then
    For Each oItem In Me!mylistbox.ItemsSelected
        ' Use DAO recordset to update those selected records
        ' either with SQL or using RecordSet.Edit and RecordSet.Update methods
        ' .....
    Next oItem
Else
    MsgBox "Nothing was selected from the list", vbInformation
    Exit Sub 'Nothing was selected
End If

[looloo]

Yes, that is what I want to do. I've done loops and recordsets before in SQL but how to do this in VBA? I've only recently figured out how to open the code modules.

[looloo]

What makes it hard for me to grasp is that the list box is populated with a query that holds two fields from Table2. So how do I reference just one of these fields in an update statement?

[looloo]

Okay, I looked up the syntax elsewhere on the web. Here is what I have. When I test it, absolutely nothing happens, not even an error. I'm clearly missing something important!

Dim SQLMasterUpdate As String
Dim oItem As Variant

If Me!RestaurantListBox.ItemsSelected.Count <> 0 Then
For Each oItem In Me!RestaurantListBox.ItemsSelected

SQLMasterUpdate = "UPDATE Restaurant SET " & _
"Restaurant.AccountManagerID = (SELECT AccountManager.AccountManagerID " & _
"WHERE AccountManagerName = " & AccountManagerName.Text & ")" & _
"WHERE ((SELECT RestaurantInfo, RestaurantPhone FROM Restaurant) = " & oItem & ");"
DoCmd.RunSQL SQLMasterUpdate
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
End Sub

[looloo]

Well, now obviously the oItem variable is simply an integer. I had overlooked that. Soo.... how do I pull the values out of the list box in order to run the update query on all records that match the list box?

[stmoong]

Actually, oItem is referring to the index for the row. So, maybe you might to rename it accordingly to say iRow.

To get the value, you can do the following:

Me.RestaurantListBox.ItemData(iRow)

If you have multiple columns, then you need to specify the index for the column to get the specific value you want, for example:

Me.RestaurantListBox.Column(iColumn, iRow)

[looloo]

Thanks a BUNCH! That's what I was looking for.

[stmoong]

You're welcome.