Access query filltering with checkboxes

Question

Hi!

I am trying to use checkboxes to filter values from a table into a listbox.
So lets say I have 10 columns (skills) in my table with Yes/No (0/-1) values. For each column, I have a corresponding chekbox in a form. This form also has a listbox. What I can do is to have the name column values from the table where the checked checkbox corresponds to a True (-1) value in the same column. e.g chkCarpenting is checked, the listbox will show names of everyone who has True value in column "Carpenting". What I want is to check also other boxes eg. chkBlacksmith, chkDriver, chkDiver and have only the names returned to the listbox of those indivituals where all the checkbox corresponding columns have a True value. Can this be done? Thanks in advance for your consideration and help!

Answer 1

Provided that you model the data correctly as described by xps350, if you really want to display the data in a list format you might like to take a look at StudentCourses.zip in my public databases folder at:

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

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file illustrates how courses taken by each student can be displayed in list format. One uses a multi-select list box in which the courses taken can selected. The other displays the courses taken in a line separated list in a read only text box. In both cases the controls are unbound and populated at runtime by code in the form's module. The conventional interface of a form/subform is code-free, however, and very easy to implement. It also has the advantage of being able to include other attributes of the relationship type between students and courses, status in my example.

The model you are currently using is known as 'encoding data as column headings'. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way. The data can be recast into a correct model by writing some VBA code, for which you'll find an example in UnencodeColumns.zip in my same OneDrive folder.

PS: for searching on multiple values of an attribute take a look at DatabaseBasics.zip in the same OneDrive folder. This demo, in the section on 'retrieving data from the database' illustrates two methods for the use of an unbound multi-select list box to do this. Again this requires the data to be modelled correctly.