asked on

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

Jim Dettman (EE MVE)

<<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.

Dale Fye

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.

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IT Project Mgr

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

Megin

ASKER

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!

Helen Feddema

Here is a screen shot of a form where I use the code (on the Select All and Deselect All buttons):