Select Record Source based on Checkbox

[lydonw]

I have a fair-sized data set going into a form that I want to filter based on a checkbox.

I have a table with records of all of the consumers we have worked with, with current consumers identified by a checkbox (Active). I've created queries that show either all active or all inactive based on this checkbox, so I can create separate forms to this data. The problem is, I'm building this database for people who will break anything that isn't completely apparent, and if they have to select a different form they will (somehow) end up in a table or query and break things, or just call me and cry about it.

So. I need check boxes (or opinion bubbles) in the header (preferably) of the form that allow the user to check whether or not they want to see active consumers, inactive, or both, and have the data in the detail section reflect either query or the master table (for both).

Is this possible?

This is part of a larger project: www (dot) access-programmers (dot) co.uk/forums/showthread.php?t=231813

 

[lydonw]

Is this possible?

Make an option group for my three options, where Active=1, Inactive=2, Both=3, fill these values in a field (wherever), then use a query to generate the data based an if statement?

I'm not sure how the function would look, but...

Field: [Active]
Table Consumers
Total: Where
Criteria: [Active]=1 AND [button]=1
Result: Display Active only

" "
Criteria: [Active]=0 AND [button]=2
Result: Display Inactive only

Field: [button]
Table: Whatever
Total: Where
Criteria: 3
Result: Display all

thoughts?

 

[lydonw]

Ok, so i made up the expression for the above and, after saving, Access changed it to the following

Field: [Consumers]![Active]
Table:
Total: Where
Criteria: True and False

Field: [Forms]![Consumers]![ConsumerSelect]
Table:
Total: Where
Criteria: 1 And 2


The changes in the criteria (True And False; 1 And 2) don't make sense to me, unless Access uses logic statements in a unique way. This seems like it would always result in a Where that is not met, which seems to be the case since my Query is empty with these two fields present.

Help?

 

[vbaInet]

If you want to build a search form you need to build your SQL string in code and apply the built up string to the form's Record Source. Search the forum for "search form" by John Big Booty. Use the Advanced Search function.

 

[lydonw]

As an update, I actually did end up doing this with just the Where sorting.

I set two buttons in a group to be equal to true and false, where Active = -1 and Inactive = 0. Then I just set my query to show Active where it was equal to the ConsumerSelector group.