"ALL" feature on a filter

[Ted C]

I have users requesting that a form be able to filter based on a value from a drop-down list (like "course name", since it's a training database) or not: basically, choose a value from the list or choose "ALL". I'm not sure how to go about implementing such a feature.



They would also like to be able to select multiple items from a list, to get all courses taken by a specific group of students, for example. Ideas for that would also be helpful.

This is for Access 2010.

[jzwp11]

I have users requesting that a form be able to filter based on a value from a drop-down list (like "course name", since it's a training database) or not: basically, choose a value from the list or choose "ALL". I'm not sure how to go about implementing such a feature.

How are you applying the filter now from the drop-down list/combo box? Are you using code in the after update event of the combo box? Can you provide the row source of the combo box?

They would also like to be able to select multiple items from a list,

You would use a multi-select list box for this, but you will need code to loop through the selected items and accummulate them. Then I assume you would want to do something with the selected items, so you'll have to provide some more detail on that.

[Ted C]

I'm using a field of an open form in the filter property of the report. It looks like this.

[tblPersonnel]![StudentID]=[Forms]![frmReportsMenu]![lstStudents]WHERE (((tblClassAttendance.StatusID)<>3))
ORDER BY tblClassSession.SessionDate;

I don't know if there's a way to get a "wildcard" from the lstStudents control of the frmReportsMenu form.

I take it there's no way to just create a query that uses the selected items in a multi-select list box as a set in a WHERE clause, then?

[jzwp11]

I'm using a field of an open form in the filter property of the report. It looks like this.

Does that field refer to only one student? Can you provide more details on the form?

I take it there's no way to just create a query that uses the selected items in a multi-select list box as a set in a WHERE clause, then?

Not quite. You can accummulate the selected items and use them as a filter for the report. Your report would be based on a query that is unfiltered and you would apply the filter as you open the report via the docmd (do command) in Visual Basic for Application code. You need the code to get the selected items anyway so you would just use the criteria portion of the do command to do the filtering at that time.

[Ted C]

The student field is a list of all students from a table (tblPersonnel). The form has additional fields for course name, role (job descriptions of students), date range, organization (we're a contractor, the client sometimes takes our training courses), and supervisor (so reports can be prepared for management). Each of those is a list queried from a related table. Buttons on the right side of the form open reports. Each report uses different parameters. The users would like to have more parameter options than I originally built into the form.

[jzwp11]

In the attached database, I illustrate a couple different ways of filtering the report.

Using the form frmSelectPeople, I illustrate the technique I described using a multi-select list box. Unfortunately, if you use a multiselect list box having an option for ALL can cause some issues, so I present another approach to handle that using the same form.

Using the form frmSelectOneOrAll, you can either select "ALL" or you can select 1 person to include in the report.