filtering on form

Question

I have a table set up with numerous yes/no fields. I want to be able to have a form that lists all records on the bottom and on the top it lists all the yes/no fields. As I select one of the yes/no check boxes it will start to limit the list of records at the bottom. I plan to then have it open form on click to be able to open another form based on that record. I can do the open form without any issues. I keep going to a query linked into this form, but I can't seem to get it to work as I believe it should. It brings up the dialog box asking the question instead of the form an allowing me to filter there. I know you can filter in the datasheet version, but I was going for the form look. I know it's possible, just have forgotten the process over the years.

Answer 1

It can be done with a query which references all of the unbound check boxes as parameters.  Let's assume for simplicity that the table has only three Boolean (Yes/No) columns named bln1, bln2, and bln3, and that in the form header you have three corresponding unbound check boxes named chk1, chk2, and chk3.  The query on which the form is based would be along these lines:

SELECT *

FROM NameOfTableGoesHere

WHERE (bln1 = Forms!NameOfFormGoesHere!chk1

    OR Forms!NameOfFormGoesHere!chk1 = FALSE)

AND (bln2 = Forms!NameOfFormGoesHere!chk2

    OR Forms!NameOfFormGoesHere!chk2 = FALSE)

AND (bln3 = Forms!NameOfFormGoesHere!chk3

    OR Forms!NameOfFormGoesHere!chk3 = FALSE);

When designing a query like this, while the basic unrestricted query can be designed in query design view, the WHERE clause should be added, and the query saved, in SQL view.  If saved in design view Access will move things around so that, at best, the logic will be obscured, or at worst, the query will become too complex to open.

In the AfterUpdate event procedure of each of the three unbound check boxes you'd requery the form with:

    Me.Requery

In the form's Open event procedure you'd need to set all of the unbound check boxes' values to False and reload its recordset with:

    Me.ck1 = False

    Me.ck2 = False

    Me.ck3 = False

    Me.Requery

Having said that, as theDBguy and Duane have pointed out, by having multiple Boolean columns the table design is incorrect, as it encodes 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 correct design would represent each of the data currently represented by the Boolean columns as a separate row in a related table.

You'll find an illustration of how the design can be corrected by means of some code in UnencodeColumns.zip in my public databases folder at:

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

In this little demo file the example appropriate to your database is that for 'Boolean (Yes/No Values'.

Answer 2

I echo the other's concern for table structure.

But I suggest you look at split forms. Such forms can allow you to use datasheet filtering to drill down to a single record.

But if you want help we need to see your table strucutre and the code you are currently using.

Answer 3

I would also second guess your table structure since it seems that if you add or remove options, you will need to add or remove fields as well as modify queries, forms, and reports.

Based on your current specifications, I would use VBA to loop through the check boxes and build an SQL statement that could be used as the form's record source or update the filter property of the form. The names of the check boxes could provide the names of the columns to use for your filter/SQL statement.

Answer 4

the record can have numerous options. The yes/no tells me if it does or doesn't have that particular option with the record. I was looking for a way to select combinations on the fly.

Answer 5

If you have multiple Yes/No fields, that makes it sound like you may have an improper table structure. If you want to create something similar to a search form, you would do better using VBA to do it. If you want to stick with using a query, you will have to account for all possible combinations of selected and unselected fields in your query.