How to have a "All" or "No selection means all" option on my Listbox Filter? (1 Viewer)

BobbyN · Feb 28, 2017

How to have a "All" or "No selection means all" option on my Listbox Filter?

Hello everyone!

I'm learning VBA on my own sort of as I go along, and I've been doing an okay job but I'm stuck on one thing. So far I have created a form with a list box that acts as a filter to a query. If you click in "cities" Yonkers, Woodside and Jamaica and then the filter button - only properties with those cities come up.

I want an "All" button or a condition that states that if none are selected, select all but I cant figure it out or find anything that works for me.

Here is my code right now:

Code:

DoCmd.RunSQL ("Delete * from cities")
For x = o To Me.CityList.ListCount - 1
    If Me.CityList.Selected(x) = True Then
        DoCmd.RunSQL ("Insert into cities values('" & _
            Me.CityList.ItemData(x) & "')")
    End If

Next

There is no way Im doing it the simplest, but it's working for me with this exception (I learned it from a video).

Cities is a Table seperate from my properties table (which has all information) - from what I can tell, based on the filter it lists only those selected.
CityList is the list box, pulling options from "All Cities" A query of every City through a group by function.

Many Thanks! If you need any clarification let me know and Ill try my best to respond helpfully!

Cross-posted here on access forms due to lack of knowledge on etiquette however it wont let me post the link because I dont have 10 posts.

Ranman256 · Feb 28, 2017

Re: How to have a "All" or "No selection means all" option on my Listbox Filter?

Code:

If IsNull(cboCity) then 
  Docmd.openQuery "qsAllCities"
Else
  Docmd.openQuery "qs1City"
End if

The 1 city query would use the cbo box as criteria,
Select * from table where city = forms!myForm!cboCity

CJ_London · Feb 28, 2017

Re: How to have a "All" or "No selection means all" option on my Listbox Filter?

Use the itemselected collection for the listbox

Code:

if myListbox.itemsselected.count=0 then 'treat as all selected
    ....
    ....
end if

Also, please do not cross post - here - http://www.accessforums.net/showthread.php?t=64692

see this link for reasons why http://www.excelguru.ca/content.php?184

BobbyN · Feb 28, 2017

Re: How to have a "All" or "No selection means all" option on my Listbox Filter?

Sorry about the cross-post, I didnt know the proper conduct around it. Thank you for your reply, although it doesnt seem to be working, should it look like this? Sorry if this is a stupid clarification, I am very new to this all.

Code:

DoCmd.RunSQL ("Delete * from cities")
For x = o To Me.CityList.ListCount - 1
    If Me.CityList.ItemsSelected.Count = 0 Then 'treat as all selected
    End If
    If Me.CityList.Selected(x) = True Then
        DoCmd.RunSQL ("Insert into cities values('" & _
            Me.CityList.ItemData(x) & "')")
    End If
Next

CJ_London · Feb 28, 2017

Re: How to have a "All" or "No selection means all" option on my Listbox Filter?

I can't comment on your code since I don't know what it is supposed to do - but I don't see how this line works

For x = o To Me.CityList.ListCount - 1

o is a letter, so the code won't work, end of. And if it does work, that means what you have provided is not what you are actually using, and I'm not prepared to spend time helping to fixing code that is not in itself the problem.

As I said, I don't know what your code is supposed to do but was expecting it to be more like

Code:

DoCmd.RunSQL ("Delete * from cities")
If Me.CityList.ItemsSelected.Count = 0 Then 'treat as all selected
    'do something here
else
    For x = o To Me.CityList.ListCount - 1
      If Me.CityList.Selected(x) = True Then
        DoCmd.RunSQL ("Insert into cities values('" & _
            Me.CityList.ItemData(x) & "')")
       End If
    Next
end if

How to have a "All" or "No selection means all" option on my Listbox Filter? (1 Viewer)

BobbyN

New member

Ranman256

Well-known member

CJ_London

Super Moderator

BobbyN

New member

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread