ListBox MultiSelect VBA Issue

[Trepalium]

I'm having difficulty with this code... I'm trying to go through and
reference multiple columns for each item that is selected in a list
box. When I run this code it only executes one time and removes one
item when I have two items selected. Code is below. Let me know if
you
have any ideas.. - Thnx

Dim PstClsNme As String, PstClsID As String, PstClsAssignDte As Date,
tpe As String, rw As Variant


Call DBConnect


If List0.ListCount = 0 Then
MsgBox "You must select at least one item to assign!",
vbCritical,
"Error!"
Else


For Each rw In List0.ItemsSelected


tpe = List0.Column(10, rw)


If tpe = "D2RE" Then
D2RE = True
ElseIf tpe = "Partnership" Then
Partner = True
End If


PstClsNme = Combo2.Column(0)
sql = "SELECT USERID, NAME FROM " & tblUserData & " WHERE
NAME = '" & PstClsNme & "'"
rsdata.Open (sql)
If rsdata.EOF Then
MsgBox "Post-Closer could not be located!",
vbCritical, "Post-Closer Not Found!"
rsdata.Close
Else
PstClsNme = rsdata("Name")
PstClsID = rsdata("USERID")
PstClsAssignDte = Format(Date, "mm/dd/yyyy")


rsdata.Close
sql = "INSERT INTO " & tblFileAssign & " VALUES ('" &
PstClsID & "', '" & PstClsNme & "','" & List0.Column(1, rw) & "','" &
PstClsAssignDte & "')"
rsdata.Open (sql)
If D2RE = True Then
sql = "UPDATE " & tblCustInfoD2RE & " SET
ASSIGNED
= 1 WHERE CLAPPLNUMBER = '" & List0.Column(1, rw) & "'"
rsdata.Open (sql)
List0.RemoveItem rw
ElseIf Partner = True Then
End If
End If


Next rw
End If

 

[Carl Rapson]

I'm having difficulty with this code... I'm trying to go through and
reference multiple columns for each item that is selected in a list
box. When I run this code it only executes one time and removes one
item when I have two items selected. Code is below. Let me know if
you
have any ideas.. - Thnx

Dim PstClsNme As String, PstClsID As String, PstClsAssignDte As Date,
tpe As String, rw As Variant


Call DBConnect


If List0.ListCount = 0 Then
MsgBox "You must select at least one item to assign!",
vbCritical,
"Error!"
Else


For Each rw In List0.ItemsSelected


tpe = List0.Column(10, rw)


If tpe = "D2RE" Then
D2RE = True
ElseIf tpe = "Partnership" Then
Partner = True
End If


PstClsNme = Combo2.Column(0)
sql = "SELECT USERID, NAME FROM " & tblUserData & " WHERE
NAME = '" & PstClsNme & "'"
rsdata.Open (sql)
If rsdata.EOF Then
MsgBox "Post-Closer could not be located!",
vbCritical, "Post-Closer Not Found!"
rsdata.Close
Else
PstClsNme = rsdata("Name")
PstClsID = rsdata("USERID")
PstClsAssignDte = Format(Date, "mm/dd/yyyy")


rsdata.Close
sql = "INSERT INTO " & tblFileAssign & " VALUES ('" &
PstClsID & "', '" & PstClsNme & "','" & List0.Column(1, rw) & "','" &
PstClsAssignDte & "')"
rsdata.Open (sql)
If D2RE = True Then
sql = "UPDATE " & tblCustInfoD2RE & " SET
ASSIGNED
= 1 WHERE CLAPPLNUMBER = '" & List0.Column(1, rw) & "'"
rsdata.Open (sql)
List0.RemoveItem rw
ElseIf Partner = True Then
End If
End If


Next rw
End If

One suggestion: the line

If List0.ListCount = 0 Then

won't work the way you want it to, because what you're testing is the count
of items in the listbox, not the number of items selected. What you want to
use instead is

If List0.ItemsSelected.Count = 0 Then

As far as your other problem, I can only guess but my guess would be that
when you delete the item from the listbox it somehow "throws off" Access.
What might be better (if you can work out the logic) is to process each
selected item without deleting it from the lisbox, then when processing is
finished remove all the selected items at once. That brings up the
question - what is the RowSourceType of the listbox? If it's a Value list,
you could just re-build the item list (omitting the selected items) and
reset the RowSource at the end. If it's a Table/Query, you probably
shouldn't be deleting items from the listbox directly, you should be
modifying the underlying data source and then requerying the listbox.

Carl Rapson

 

[Trepalium]

One suggestion: the line

If List0.ListCount = 0 Then

won't work the way you want it to, because what you're testing is the count
of items in the listbox, not the number of items selected. What you want to
use instead is

If List0.ItemsSelected.Count = 0 Then

As far as your other problem, I can only guess but my guess would be that
when you delete the item from the listbox it somehow "throws off" Access.
What might be better (if you can work out the logic) is to process each
selected item without deleting it from the lisbox, then when processing is
finished remove all the selected items at once. That brings up the
question - what is the RowSourceType of the listbox? If it's a Value list,
you could just re-build the item list (omitting the selected items) and
reset the RowSource at the end. If it's a Table/Query, you probably
shouldn't be deleting items from the listbox directly, you should be
modifying the underlying data source and then requerying the listbox.

Carl Rapson- Hide quoted text -

- Show quoted text -

Thanks. I completely didn't catch that.

FYI I found out what I was doing wrong.

I was removing items during the For statement so it kept reseting the
number of items selected. Got it working now.