Multi Select List

[Heather]

I have two list box's you can select funds from the first
list box and the go into the second list box. Currently I
can only do one fund at a time can some tell me how I can
change it to a MultiSelect and click the add button to do
it. My current code is this.


Private Sub cmdAdd_Click()
Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset
Dim SelItem As Control

Set SelItem = Me.Listfunds

If IsNull(SelItem) Then
MsgBox "Please select A Fund from from the list."
Else

Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset


MyRS.Open "tbl_Fund", conn, adOpenDynamic,
adLockOptimistic

With MyRS
' find the record for the selected item.
.Find "FUND = '" & SelItem & "'"
' set the Yes-No_Fld of the selected record
to "No".
.Fields("Yes-No_Fld").Value = "No"
.Update
End With

Set MyRS = Nothing
Set conn = Nothing


Me.ListYes.Requery
Me.ListNo.Requery
End If

End Sub

 

[Dirk Goldgar]

I have two list box's you can select funds from the first
list box and the go into the second list box. Currently I
can only do one fund at a time can some tell me how I can
change it to a MultiSelect and click the add button to do
it. My current code is this.


Private Sub cmdAdd_Click()
Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset
Dim SelItem As Control

Set SelItem = Me.Listfunds

If IsNull(SelItem) Then
MsgBox "Please select A Fund from from the list."
Else

Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset


MyRS.Open "tbl_Fund", conn, adOpenDynamic,
adLockOptimistic

With MyRS
' find the record for the selected item.
.Find "FUND = '" & SelItem & "'"
' set the Yes-No_Fld of the selected record
to "No".
.Fields("Yes-No_Fld").Value = "No"
.Update
End With

Set MyRS = Nothing
Set conn = Nothing


Me.ListYes.Requery
Me.ListNo.Requery
End If

End Sub

Heather - what follows isn't tested code, but it should be something
like this:

'----- start of revised (air) code -----
Private Sub cmdAdd_Click()

Dim conn As ADODB.Connection
Dim MyRS As ADODB.Recordset
Dim varItem As Variant
Dim strFund As String

With Me.Listfunds

If .ItemsSelected.Count = 0 Then
MsgBox "Please select at least one Fund from from the list."
Else

Set conn = CurrentProject.Connection
Set MyRS = New ADODB.Recordset

MyRS.Open "tbl_Fund", conn, adOpenDynamic, adLockOptimistic

For Each varItem In .ItemsSelected

strFund = .ItemData(varItem)

With MyRS
' find the record for the selected item.
.Find "FUND = '" & strFund & "'"
' set the Yes-No_Fld of the selected record to "No".
.Fields("Yes-No_Fld").Value = "No"
.Update
End With

Next varItem

Set MyRS = Nothing
Set conn = Nothing

Me.ListYes.Requery
Me.ListNo.Requery
End If

End With

End Sub
'----- end of code -----