locked
Filter report from multiselect list box RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    I would like to call on your help please as I just cannot get my filter to work.  Currently the query gets one piece of information from a combo box on a form and then further filters on 2 other fields...

    I have changed the combo box to a multiselect list box but just cannot get the query to work correctly.  The list box is working fine, letting me make multiple selections but the various instructions I have found just will not work and I cannot figure out why.

    Any help would be appreciated.  Thanks.

    Chris | UK

    Thursday, March 24, 2016 5:03 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    A multiselect list box does not have a value, it has an ItemsSelected collection, which is a collection of variants each of which identifies a selected item.   It is necessary to loop through the collection in code and build a delimited list of the items selected.  You would normally expect to use the IN operator against a value list.  However, the IN operator does not accept a parameter as its argument.  Microsoft has published a way of simulating this by means of GetToken and InParam functions.

    You'll find an example as MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to restrict a report's results by means of controls in a dialogue form, including a multiselect list box.  Where the report's query references a value list this is stored in a hidden text box control in the form.

    Ken Sheridan, Stafford, England

    Thursday, March 24, 2016 5:52 PM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    A multiselect list box does not have a value, it has an ItemsSelected collection, which is a collection of variants each of which identifies a selected item.   It is necessary to loop through the collection in code and build a delimited list of the items selected.  You would normally expect to use the IN operator against a value list.  However, the IN operator does not accept a parameter as its argument.  Microsoft has published a way of simulating this by means of GetToken and InParam functions.

    You'll find an example as MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to restrict a report's results by means of controls in a dialogue form, including a multiselect list box.  Where the report's query references a value list this is stored in a hidden text box control in the form.

    Ken Sheridan, Stafford, England

    Thursday, March 24, 2016 5:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Ken

    Your coding is similar to some of the coding that I have not been able to get to grips with yet but I think actually having a working model as you have provided will help me to understand better how the code is working and be able to build this into my database.

    I'll let you know how I get on.

    Chris | UK

    Thursday, March 24, 2016 6:01 PM