Repost: listbox help

[sherry]

Hi,

I have written a procedure that transfers an experience
record from 1 listbox to another. The problem is, I can
only transfer 1 record regardless of how many are
selected.

The transfer is completed by using an SQL
statement to change the value of a yes/no table item,
based on a
selected item in the listbox. The following code is the
result of my last failed attempt to transfer multiple
records:-

For Each vntItem In Me.lstExpiRecs.ItemsSelected
DoCmd.RunSQL ("UPDATE Expi_tblCoAssignment SET
Expi_tblCoAssignment.CoTempInclusion = yes WHERE CoAssigID
= " & [Forms]![Expi_miscOPpickList]![lstExpiRecs].Column
(0) & ";")
Me.lstExpiOutput_Records.Requery
Me.lstExpiRecs.Requery

Next

It works fine for a single record. Any help much
appreciated.
Sherry

 

[sherry]

Many thanks for this Dirk, I'll give it a go.

-----Original Message-----

Hi,

I have written a procedure that transfers an experience
record from 1 listbox to another. The problem is, I can
only transfer 1 record regardless of how many are
selected.

The transfer is completed by using an SQL
statement to change the value of a yes/no table item,
based on a
selected item in the listbox. The following code is the
result of my last failed attempt to transfer multiple
records:-

For Each vntItem In Me.lstExpiRecs.ItemsSelected
DoCmd.RunSQL ("UPDATE Expi_tblCoAssignment SET
Expi_tblCoAssignment.CoTempInclusion = yes WHERE CoAssigID
= " & [Forms]![Expi_miscOPpickList]![lstExpiRecs].Column
(0) & ";")
Me.lstExpiOutput_Records.Requery
Me.lstExpiRecs.Requery

Next

It works fine for a single record. Any help much
appreciated.

Untested, but how about:

'---- start of air code ----
Dim varItem As Variant
Dim strList As String

With Me.lstExpiRecs

If .ItemsSelected.Count > 0 Then

For Each varItem In .ItemsSelected
strList = strList & "," & .ItemData(varItem)
Next varItem

If .ItemsSelected.Count = 1 Then
strList = "= " & Mid$(strList, 2)
Else
strList = "In (" & Mid$(strList, 2) & ")"
End If

CurrentDb.Execute _
"UPDATE Expi_tblCoAssignment " & _
"SET

Expi_tblCoAssignment.CoTempInclusion = yes " & _