Link to home
Start Free TrialLog in
Avatar of David Lelièvre
David Lelièvre

asked on

Multi select listbox + date filter in a subform

Hello,

I'm having some issue with my database.
I'm trying to use a listbox to allow multi selections to filter a subform. I also would also like to add a filter date FROM and TO.
I've read multiple topics on it, but I can't seem to figure out how to do it.

Thank you.
Database11.accdb
Avatar of Gregory Miller
Gregory Miller
Flag of United States of America image

So this is really easy. I chose the WorkerID List on your example. If you open the properties, there is a Multi-Select option. Default is None which is what you have it set for. If you change it to Simple, you can select multiple items by just clicking on them, nothing else is required. If you want to have more functionality, choose Extended which then lets you use CTRL and Shift modifiers to select/deselect single or ranges of items in the list.
User generated image
Avatar of David Lelièvre
David Lelièvre

ASKER

Hi Gregory,

I think I didn't explain my issue well enough.

What I'm trying to achieve is something like this:

https://www.experts-exchange.com/questions/29049612/Ms-Access-Filter-record-on-subform-based-on-multiple-selection-in-List-box.html?anchorAnswerId=42249294#a42249294

I'd like to have a listbox to be able to filter my subform.

I also want to add a date filter, however I can't even get to the multi select filter part for some reason.

Thank you.
Avatar of PatHartman
Here is a working example.
FillFormFields.zip
Hi @PatHartman,

This is almost what I'm looking for,
However, is there a way to make it work for a unique form instead of a continous form? (see attached file)
Also, how can I add a date filter?

Thank you.

David
Database2.accdb
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Then what have I done  wrong in my previous attached file?

I did create a filter command button and added the event on click vba.

But when I click on it, it doesn't filter my subform.

Thank you for your help by the way.
Your interpretation didn't work because you were using a subform (and also there was a coding error when you set the filter).  Filters cannot be set this way.

Take a look at the modified example I made for you.  You can change the view from Continuous to Single if you want and that will not affect anything else.

If you want to stick with a subform, you need to look at the second example in my sample database.  Rather than setting the recordsource for the main form, you set the recordsource for the subform.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My original goal was to use a subform to display a crosstab of the filtered data (using "WorkerID" as row heading, "Status" as column heading, and the count of "Status" as value). This is why I kept trying to use a subform. Haha.

 Is there a better way to do it?

Thank you for your clear explanations.
Crosstab.accdb
As long as the crosstab always returns a consistent set of columns, you will be able to use it as a subform.  If the crosstab returns a variable set of columns, you will not easily be able to do this.  Even a report is difficult although if you can fix the max columns to some number that you know will fit within the max width of an access report (22 inches), you will be able to do it and I can provide a sample.

Try to use the new sample I added.  It uses a subform.  Of course, the query is not a crosstab so you will have to open the crosstab in SQL view so you can copy the SQL string and paste that into a variable in the code.
It is going to be a variable set of columns, so I'll have to go with the report way. Can the report do the same thing as a crosstab? I can fix the max columns. And yes please, a sample would be wonderful.

Thanks again.
Well there are 2 ways to tackle this
1st way ...you open the form in design view via VBA...you count the columns of your CrossTab query and you create on the fly the controls needed...since its going to be a subform you just put some extra fields for the parent forms to handle the filtering and that's all.
2nd way...based on your  sample data ...you are not going to have too variations on the progress of a job, so you just design your subform with some extra textboxes for future use......on the opening you decide how many to use and you hide the rest...you assign the controlsource of the active textboxes and you are ready...