List Box Question

[Bob Quintal]

I am working on a database that deals with oil companies and
their wells
Occasionally, an oil company might sell some or all of their
wells to another company so I need an easy way to select a
group of wells and change the associated company. I created a
simple form with the following controls;

1st combo box (cboCurrentOperator) that selects
OperatorID(primary key) and CompanyName from tblOperators

2nd combo box (cboNewOperator) that selects the same as the
1st

a list box (lstWells) that selects WellID(primary key)
OperatorID and WellName from tblWells filtered by the 1st
combo box. Multi Select property is set to extended.

an Update button (cmdUpdate) with the following code for the
on click event

Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstWells

For Each varItem In ctl.ItemsSelected

ctl.Column(1) = Me.cboNewOperator

Next varItem

This does not work (I also tried adding .Column(0) to the end
of Me.cboNewOperator) so apparently I can't use the Column
reference for this type of code. I think maybe some type of
UPDATE sql statement might be what i need but I don't know how
to get it to update only the items that were selected in the
list box.

Am I on the right track, or going about this all wrong?
Any help would be very much appreciated.

You can't change a value using the listbox column() propert.

the values are all read-only. You can however run a SQL
statement that UPDATEs one record inside your varItem

I hope that a column of that listbox contains the primary key to
the wells table. You will need that for the whereClause.

PK = 0 '<- set this to the correct column.
For Each varItem In ctl.ItemsSelected

strSQL = "UPDATE wells SET operator " _
& "VALUES (""" Me.cboNewOperator & """) " _
& "WHERE wellid = " & me.ctl.Column(PK) & ";"
Docmd.RunSQL strSQL
Next varItem
..
I'll let you prettify this, and test for bugs. I don't have your
db at hand to test with.

 

[Beetle]

I am working on a database that deals with oil companies and their
wells
Occasionally, an oil company might sell some or all of their wells to
another company so I need an easy way to select a group of wells and
change the associated company. I created a simple form with the
following controls;

1st combo box (cboCurrentOperator) that selects OperatorID(primary
key) and CompanyName from tblOperators

2nd combo box (cboNewOperator) that selects the same as the 1st

a list box (lstWells) that selects WellID(primary key) OperatorID and
WellName from tblWells filtered by the 1st combo box. Multi Select
property is set to extended.

an Update button (cmdUpdate) with the following code for the on click
event

Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstWells

For Each varItem In ctl.ItemsSelected

ctl.Column(1) = Me.cboNewOperator

Next varItem

This does not work (I also tried adding .Column(0) to the end of
Me.cboNewOperator) so apparently I can't use the Column reference for
this type of code. I think maybe some type of UPDATE sql statement
might be what i need but I don't know how to get it to update only the
items that were selected in the list box.

Am I on the right track, or going about this all wrong?
Any help would be very much appreciated.

 

[krissco]

I am working on a database that deals with oil companies and their
wells
Occasionally, an oil company might sell some or all of their wells to
another company so I need an easy way to select a group of wells and
change the associated company. I created a simple form with the
following controls;

1st combo box (cboCurrentOperator) that selects OperatorID(primary
key) and CompanyName from tblOperators

2nd combo box (cboNewOperator) that selects the same as the 1st

a list box (lstWells) that selects WellID(primary key) OperatorID and
WellName from tblWells filtered by the 1st combo box. Multi Select
property is set to extended.

an Update button (cmdUpdate) with the following code for the on click
event

Dim ctl As Control
Dim varItem As Variant

Set ctl = Me.lstWells

For Each varItem In ctl.ItemsSelected

ctl.Column(1) = Me.cboNewOperator

Next varItem

This does not work (I also tried adding .Column(0) to the end of
Me.cboNewOperator) so apparently I can't use the Column reference for
this type of code. I think maybe some type of UPDATE sql statement
might be what i need but I don't know how to get it to update only the
items that were selected in the list box.

Am I on the right track, or going about this all wrong?
Any help would be very much appreciated.

Right track. Good looking code - just a small amount of tweaking
needed.

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 = "


For Each varItem In Me.lstWells

'Try this first to make sure you are getting the correct data
debug.print strSql & varItem

'Once you are satisfied with the query, uncomment this line:
'con.execute strSql

Next varItem

con.close
set con = nothing

This will execute a query for each well id in the selected list. You
may need to change what varItem is or reference a column or just
iterate the whole lstWells control and look at the .Selected property
for rows.

Good Luck

-Kris

 

[Beetle]

Right track. Good looking code - just a small amount of tweaking
needed.

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 = "

For Each varItem In Me.lstWells

'Try this first to make sure you are getting the correct data
debug.print strSql & varItem

'Once you are satisfied with the query, uncomment this line:
'con.execute strSql

Next varItem

con.close
set con = nothing

This will execute a query for each well id in the selected list. You
may need to change what varItem is or reference a column or just
iterate the whole lstWells control and look at the .Selected property
for rows.

Good Luck

-Kris

Thank you for your help Kris

I had to play with it a little and it does work now, however it only
executes the update for whichever item was selected last in the list
box. The other items do not change. The code now 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

 

[krissco]

Thank you for your help Kris

I had to play with it a little and it does work now, however it only
executes the update for whichever item was selected last in the list
box. The other items do not change. The code now 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

Ok. Only one problem w/ the code:

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

You are creating one SQL statement and executing the same statement
one time for each item selected. Your statement should change for each
item. You will want to have the listWells.Column(0) inside of the
loop, not outside. In addition, once strSql is setup, don't change it.

Here is some slightly wasteful code:

Private Sub cmdUpdate_Click()

'Check to see if anything at all was selected
If lstWells.ItemsSelected.Count > 0 Then

'Setup variables
Dim currentRow As Integer
Dim strSql As String
Dim con As Adodb.Connection
Set con = CurrentProject.Connection

'Set up first part of SQL statement (all but the wellID criteria)
strSql = "update tblWells set OperatorID = " & Me.cboNewOperator _
& " where WellID = "

'Loop through all rows in the list. Check to see if it is
selected.
'If it is, then run the query and specify the current WellID as
the
'final criteria.
For currentRow = 0 To lstWells.ListCount - 1
If lstWells.Selected(currentRow) Then
con.Execute strSql & lstWells.Column(0, currentRow)
End If
Next currentRow

'Cleanup ADODB object
con.Close
Set con = Nothing

End If

End Sub

What I mean by slightly wasteful is you will notice that I am not
using the ItemsSelected collection any more. I copied this from some
old code of mine (and didn't want to re-write it). It should work for
you.

-Kris