Share via

Combo Box Multiple Selection and show all records if not record selected

Anonymous
2024-08-27T00:00:04+00:00

Hello,

I have a query that needs to pull the records based on the criteria.

* If the combo box value is blank, then I would like to pull all of the records

* The combo box would allow to make multiple selection

* When I click the button, I will see the expected results

* How do I need to modify my POS Description so and the query?

Microsoft 365 and Office | Access | For education | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-08-27T01:29:53+00:00

    You might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg

    In this little demo file, the first form in the section on 'Retrieving data from the database' illustrates two methods for returning records on the basis of one or more selections in a multi-select list box.  Both include ‘Select All’ buttons.  The same form also illustrates how records can be returned by means of multiple combo boxes, with the option to combine the parameters with Boolean AND or OR operators.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2024-08-27T11:49:09+00:00

    You need to review your table design. You should have a lookup table for your POS_Description like this:

    tluPositions

    PositionID (PK autonumber)

    Position

    In your employee table, you should have a foreign key for PositionID that will store the corresponding PositionID for that employee.

    Now, to get a listing of specified positions a listbox as Tom suggested. This article shows how Microsoft Access tips: Use a multi-select list box to filter a report (allenbrowne.com)

    The relevant properties of your listbox should be:

    RowSource:

    SELECT PositionID, Position FROM tluPositions ORDER BY Position;

    BoundColumn = 1

    Was this answer helpful?

    0 comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2024-08-27T00:33:18+00:00

    > The combo box would allow to make multiple selection

    That's not how comboboxes work. They select ONE item. If you want multi-select, consider a listbox. And no, do not use a multivalue combobox, nor any MVF.

    A listbox would also solve the problem of unselecting all.

    One alternative to a listbox is a subform with an additional IsSelected column (Yes/No field, checkbox control) which is bound to a local temp table. This would enable to you join the query with this table to return only the selected items.

    Re your field naming convention: it is a best practice not to use any spaces or other funny characters. They may cause notational difficulties.

    Wow, PositionID is the same thing as ValHcm? You may want to reconsider your names again.

    Feel free to ask follow up questions if something in this answer is not clear. I'm not exactly sure of your level of expertise.

    Was this answer helpful?

    0 comments