I have an unbound checkbox on a form and I would like to limit the
records based on the checkbox. Here's the data:
Form: frmProducts
Table behind form: tblProducts
unbound checkbox: chkShowDiscontinued
Dependent field from table and form: chkDiscontinued (also a checkbox)
When chkShowDiscontinued = False, I want to show records where
chkDiscontinued = false
When chkShowDiscontinued = True, I want to show all the records
recordcount = hundreds of records

I have done this in several different ways and I wanted to bounce this
idea off several people to see if anyone has any better ideas for
making it work. None of mine have worked so far. Not 100% anyway and
I'm tired of dealing with it.

WhathaveIdone?

WhathaveIdone? wrote:

What's your filter?

Similar to
Select ... From ... Where chkDiscontinued =
Forms!YourFormName!chkShowDiscontinued
?

If for a form, you can do something like
Me.Filter = (chkDiscontinued = Me.chkShowDiscontinued)
Me.FilterOn = True
if you want to set it when the checkbox is checked.

On 14/09/2010 23:23:16, "WhathaveIdone?" wrote:
Query behind the form should be something like - OK I Mean ProductForm
RecordSource "SELECT tblProducts.* FROM tblProducts WHERE chkDiscontinued = "
& Forms!ProductForm!chkShowDiscontinued & ";"

on the AfterUpdate of the chkShowDiscontinued, I think you will need
Me.Requery

Phil

Field: chkDiscontinued
Criteria: Forms!frmProducts!chkShowDiscontinued OR
(Forms!frmProducts!ChkShowDisContinued=True)

In query design view
-- Enter Forms!frmProducts!ChkShowDisContinued in a blank FIELD "box".
-- Criteria (line 1): True
Criteria (line 2) : leave it blank

For chkDiscontinued field set it up this way
Field: chkDiscontinued
Criteria (line 1): Leave it blank
Criteria (line 2) : Forms!frmProducts!chkShowDiscontinued


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

WhathaveIdone? wrote:

On Sep 15, 7:27*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:
Good ideas everyone. Thanks John. Your solution was bay far the
simplest and easiest. Worked like a charm. I just added a requery
function to the chkDiscontinued AfterUpdate(), and it was perfect.
EXACTLY what I needed. Thanks for showing the simplest answer on that!

-WhathaveIdone?

"WhathaveIdone?" <brasus04 (AT) gmail (DOT) com> wrote in
news:da90f977-59f4-4d5b-a891-5a0ec3dd3a89 (AT) x18g2000pro (DOT) googlegroups.co
m:

I have a UI philosophy about this kind of thing that is perhaps a
bit strange, but it comes about from confusion on the part of users
as to what I meant by checkbox filters like this.

A checkbox would turn on/off display of certain records.

While there are only two states to the checkbox, True or False,
i.e., checked or unchecked, there are actual three sets of data that
might be useful to the users:

1. all records.

2. discontinued only.

3. the data set EXCLUDING discontinued records.

While you don't say your users need 3, the problem is wording the
label on the checkbox so that it is clear what the result is when
you uncheck it. "Hide discontinued" may be sufficient, but not
necessarily -- depends on what it means and how your users interpret
it.

So, I usually do these with an option group, with the default being
whichever of the three choices is the most common. You can save
space by using a combo box for that, if necessary, but I like having
the choices visible all at once.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/