CODEWITHSUNDEEP
ms-accessmacroslistbox
user2396812
user2396812

Reputation: 83

MS Access de-select listbox after running macro/query

So I have a form (frmBookingForm) in Access, and a listbox (lstMyBookings) that displays the results of a query. Below this, I have a button (cmdDeleteBooking) which runs a delete query using the lstMyBookings selection as input. The button then runs a macro that firstly checks whether a record in the listbox is selected, and if one is, runs the delete query and requeries the data, so the deleted record is no longer shown in the listbox. If not, an error message is displayed. However, if I then click the button again, it again runs the delete query, even though there is apparently nothing selected in the list box.

Essentially, how can I 'clear' the listbox selection?

I'd prefer a solution that can be done in a macro format, as I have little to no understanding of VBA. However, if providing a VBA solution, I'd greatly appreciate a short explanation of it.

Thanks :)

Upvotes: 2

Views: 2797

Answers (1)

Lynn Crumbling
Lynn Crumbling

Reputation: 13357

Looks like this website has a nice little function to do it. Essentially, you need to test if it's a multiselect, and then do one of two things. I suppose if you know ahead of time that it is/isn't a multiselect, you wouldn't even need the "if" statement:

If List0.MultiSelect = 0 Then
    List0 = Null
Else
    For Each varItem In List0.ItemsSelected
        List0.Selected(varItem) = False
    Next
End If

If the control's MultiSelect property is set to None, this code just sets List0 to Null. If the control's MultiSelect property is set to anything else, the code loops through all items that are currently selected, and sets the Selected property of that item to False. My example assumes your control is called List0.

EDIT

To use this code, use an event instead of a macro. Here's how you do this:

  1. Right click on the button, select properties
  2. In the Property Sheet window, click on the "Event" tab
  3. Click inside of the "On Click" blank area, and click the dropdown arrow, then select "[Event Procedure]"
  4. Click the ellipsis ("...") to go into the code editor.

In the code editor, your should already have your event for the button (let's assume the button is called Command1:

Private Sub Command1_Click()


End Sub

Add your code in between (assuming the listbox is called List0):

Private Sub Command1_Click()

If List0.MultiSelect = 0 Then
    List0 = Null
Else
    For Each varItem In List0.ItemsSelected
        List0.Selected(varItem) = False
    Next
End If

End Sub

Upvotes: 3

Related Questions