Need to deselect items in multi select list box in Access

I have a multi select list box on a form that is used to choose items from a past report and add them to a current report.
There is a button that adds everything that is selected in the list box to a table, and then a second list box is updated with the information.

Everything works fine, but I need the selected items in the first list box to be cleared when the user clicks the button (btnAddSelected).

I am using
Me.lstprevRpts.Selected(I) =False

Open in new window

right now, but it is clearing the information that is added to the second list box.

Do I just have this command in the wrong place?

Private Sub btnAddSelected_Click()

Dim frm As Form
Dim ctl As Control


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Variant
Set frm = Forms("frm_Act_Enter")
Set ctl = frm![lstPrevRpts]

Set db = CurrentDb
Set rst = db.OpenRecordset("Act_SubTo_Date")



    For Each i In ctl.ItemsSelected
        rst.AddNew
'          Debug.Print i, ctl.Column(4, i)
            rst("ActID") = ctl.Column(4, i)
            rst("ActDate") = Nz(Me!ActDate.Value, Date)


        rst.Update

Next i

Me.lstPrevRpts.Selected(i) = False
        
rst.Close
Set rst = Nothing

Refresh

End Sub

Open in new window

MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Do I just have this command in the wrong place?>>

  Yes.   Your referencing the loop variable i outside of the loop.  You either:

A. Want  to move it inside the loop after you've added the record.

B. Write separate loops to write the records and clear the select.

 Right now, your only deselecting the last item.

Jim.
0
Dale FyeCommented:
Personally, I'd go with Jim's 2nd option and write a separate loop to clear the selections.

You might also want to have some check in there that prevents you from adding the same report to the "Act_SubTo_Date" table more than once.  You may already have a unique index in that table, but if you do, you should have an error handler in your procedure which will handle the error properly and not cause your application to bomb.
0
Helen FeddemaCommented:
Here are some code segments to select all or deselect all in a multi-select listbox' I usually place them on command buttons in the footer of the form with the listbox:
'Select all rows in listbox
Set lst = Me![lstSelectMultiple]
intRows = lst.ListCount - 1

For intIndex = 0 To intRows
   lst.Selected(intIndex) = True
Next intIndex

'Deselect all rows in listbox
Set lst = Me![lstSelectMultiple]
intRows = lst.ListCount - 1

For intIndex = 0 To intRows
   lst.Selected(intIndex) = False
Next intIndex

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

IT Project MgrVBA/SQL developerCommented:
My suggestion, here's a sub that I call from click event of the "clear" button named "cmdClr", where lstMultiSelect is the name of the list box:

Private Sub cmdClr_Click()
    ClearListBox
End Sub


Private Sub ClearListBox()
Dim iCount As Integer
       
    For iCount = 0 To Me!lstMultiSelect.ListCount
        Me!lstMultiSelect.Selected(iCount) = False
    Next iCount
   
End Sub
0
MeginAuthor Commented:
This seems to work just fine. I don't know about placing it in the footer of  the form. I added it to the end of my code, just before End Sub.  Seems to be working great.

Thanks!
0
Helen FeddemaCommented:
Here is a screen shot of a form where I use the code (on the Select All and Deselect All buttons):
Buttons to select all or deselect all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.