Multi select problem

[bakerboy_1111]

Hello All,

I have created a form which allocates cables to drums. This is based on a table called "Cab_Sched_Temp" which has a "Cable_No" field and a "Drum_Number" field. Basically, the way it works is that within the form I have a list box with a list of Cable_No's in it, and a combo box with a list of Drum_Number's. The user selects a Cable_No, as well as a Drum_Number and then clicks an "Add" button. This button then updates the Drum_Number field with the currently selected Drum_Number for the selected Cable_No.

I hope my explanation makes sense!! So far this form works a treat. What I'm now trying to do is make it possible to select multiple Cable_No's from the list box, and allocate them all Drum_Number's with a single click of the Add button. I've initially tried setting the Multi Select property on the Cable_No list box to "Simple" or "Extended", but this just results in my Add button not working at all (it is not recognising that there is a Cable_No selected at all, even though I have many selected).

Anyway, I'm guessing that this means I have to modify my actual code rather than just the Multi Select property. My current code is as follows:

Option Compare Database

Private Sub btnAdd_Click()
Dim SQL As String
If IsNull(cmbDrums.Value) Then
MsgBox "Please select a drum."
ElseIf IsNull(lstCables.Value) Then
MsgBox "Please select a cable to add."
Else
DoCmd.SetWarnings False
SQL = "UPDATE Cab_Sched_Temp SET Drum_Number = " & cmbDrums.Value & " WHERE Cable_No like " & lstCables.Name & ";"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End If
Me.Refresh
End Sub

Private Sub btnRemove_Click()
Dim SQL As String
If IsNull(cmbDrums.Value) Then
MsgBox "Please select a drum."
ElseIf IsNull(lstDrumCables.Value) Then
MsgBox "Please select a cable to remove."
Else
DoCmd.SetWarnings False
SQL = "UPDATE Cab_Sched_Temp SET Drum_Number = Null WHERE Cable_No = '" & lstDrumCables.Value & "';"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
End If
Me.Refresh
End Sub

Private Sub cmbDrums_Change()
Dim SQL As String
SQL = "SELECT [Cable_No], [Approx_Cable_Length] FROM Cab_Sched_Temp WHERE Drum_Number = " & cmbDrums.Value & ";"
lstDrumCables.RowSource = SQL
Me.Refresh
End Sub


In the above code, "btnAdd" is the Add button, "btnRemove" is a Remove button which essentially does the opposite of the Add button, "cmbDrums" is the combo box used to select the Drum_No, and "lstCables" is the list box used to select the Cable_No. "Approx_Cable_Length" is just another field in the Cab_Sched_Temp table which I display.

Does anyone have any advice as to what I can do to get multi selections to work?

Thankyou to anyone who helps,

Bakerboy