List Box Multi Select problem

[Beetle]

This is a continuation from previous thread "List Box Question"
(sorry, but I didn't seem to be getting any more responses on my previous
thread)

I have a simple form with 2 combo boxes, a list box (multi select =
extended), and a button.

You select a company name in the first combo box (cboCurrentOperator) and
the list box (lstWells) populates with the first companies oil wells. You
then select a company name in the second combo box (cboNewOperator), click
the button (cmdUpdate) and any wells selected in the list box shoul be
reassigned to the new company.

Thanks to krissco and Bob Quintal for responding to my previous thread. I
did get the code to work, however it only updates whichever item was selected
last in the list box. The other selected items do not update. The current
code looks like this;

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSql As String
Dim con As Adodb.Connection
Set con = CurrentProject.Connection

strSql = "update tblWells set OperatorID = " & Me.cboNewOperator _
& " where WellID = " & Me.lstWells.Column(0)

For Each varItem In Me.lstWells.ItemsSelected


con.Execute strSql

Next varItem

con.Close
Set con = Nothing

End Sub

Any Ideas?
Thanks

 

[pietlinden]

This is a continuation from previous thread "List Box Question"
(sorry, but I didn't seem to be getting any more responses on my previous
thread)

I have a simple form with 2 combo boxes, a list box (multi select =
extended), and a button.

You select a company name in the first combo box (cboCurrentOperator) and
the list box (lstWells) populates with the first companies oil wells. You
then select a company name in the second combo box (cboNewOperator), click
the button (cmdUpdate) and any wells selected in the list box shoul be
reassigned to the new company.

Thanks to krissco and Bob Quintal for responding to my previous thread. I
did get the code to work, however it only updates whichever item was selected
last in the list box. The other selected items do not update. The current
code looks like this;

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSql As String
Dim con As Adodb.Connection
Set con = CurrentProject.Connection

strSql = "update tblWells set OperatorID = " & Me.cboNewOperator _
& " where WellID = " & Me.lstWells.Column(0)

For Each varItem In Me.lstWells.ItemsSelected

con.Execute strSql

Next varItem

con.Close
Set con = Nothing

End Sub

Any Ideas?
Thanks

move the strSQL = "update tblWells..." *inside* the for/each loop so
the string is being changed each time. Taht should fix it...

 

[Ofer Cohen]

You need to change the order of the code line

try setting the SQL within the loop and not outside:

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSql As String
Dim con As Adodb.Connection
Set con = CurrentProject.Connection

For Each varItem In Me.lstWells.ItemsSelected

strSql = "update tblWells set OperatorID = " & Me.cboNewOperator _
& " where WellID = " & Me.lstWells.Column(0)

con.Execute strSql

Next varItem

con.Close
Set con = Nothing

End Sub

 

[Douglas J. Steele]

Both the responses you've already received mentioned that you need to move
where the SQL string is built and executed. However, they missed the
necessary change to how you reference the list box when building the SQL
string.

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSql As String
Dim con As Adodb.Connection
Set con = CurrentProject.Connection

For Each varItem In Me.lstWells.ItemsSelected

strSql = "update tblWells set OperatorID = " & Me.cboNewOperator _
& " where WellID = " & Me.lstWells.Column(0, varItem)
con.Execute strSql

Next varItem

con.Close
Set con = Nothing

End Sub

 

[Beetle]

Thanks for the advice. I moved my SQL statement inside the loop but I'm still
having the same problem

Any other ideas?

 

[Beetle]

That solved my problem. Thanks for your help.

Both the responses you've already received mentioned that you need to move
where the SQL string is built and executed. However, they missed the
necessary change to how you reference the list box when building the SQL
string.

Private Sub cmdUpdate_Click()

Dim varItem As Variant
Dim strSql As String
Dim con As Adodb.Connection
Set con = CurrentProject.Connection

For Each varItem In Me.lstWells.ItemsSelected

strSql = "update tblWells set OperatorID = " & Me.cboNewOperator _
& " where WellID = " & Me.lstWells.Column(0, varItem)
con.Execute strSql

Next varItem

con.Close
Set con = Nothing

End Sub