How to show/hide flagged records

[bllgnn]

Each record in my table has a Discontinued field (yes/no).

I want to give the user the choice of seeing the Discontinued records or not,with a checkbox of the form chkShowDiscontinued.

I have tried a few methods, but none of them work.

After the user makes the choice on the form, what is the best way to include or not include the Discontinued records in the form?

 

[KenReay]

Hi

In the after update event of the check box for Show All, put

Something like:

Dim strSQL As String

If chkShowAll Then
strSQL = "SELECT * FROM myTAble;"
Else
strsQL = "SELECT * FROM MyTAble WHERE Not chkDiscontinued;"
End If
Me.Recordsource = strSQL
Me.Requery

You need to adjust this to include your control names and table name, and maybe adjust the SQL to include ORDER BY and a JOIN, but you get the idea? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[bllgnn]

Hi KenReay,

Thanks for the help, but I'm still having problems.

When the form opens, it shows all records, which I can understand because chkShowAll has not yet been checked.

When I check chkShowAll after the form is opened, I get an "Enter Parameter Value" for chkShowAll twice, once after Me.Recordsource = strSQL and again after Me.Requery.

When I click chkShowAll a second time, I get an 'Invalid use of Null'. A breakpoint shows the value of chkShowAll is Null, which I do not understand.

Any further help will be appreciated.

 

[bllgnn]

Hi KenReay,

Sorry, there was a typo in my last post:

I get an "Enter Parameter Value" for chkShowAll twice
should read
I get an "Enter Parameter Value" for chkDiscontinued twice

 

[KenReay]

Hi

I think we just have an error through you following my example too literally, I was trying to convey the idea of what you need to do, you need to adjust the SQL to use the column names etc for your situation. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[bllgnn]

Hi KenRaey,

This is getting over my head. What do column names have to do with it?

I have spent hours trying every variation of your idea I can think of.

Surely I am not the first person who wanted to show/hide records with a yes/no flag.

Can anyone tell me how to do it?

 

[hwkranger]

Simplest way of doing this is:

Make your form from a QUERY instead of from a Table.
Create a query that includes all the parts of your table, then put in the criteria box for your checkbox field put "TRUE", that way records that are false will not be displayed.

This is the simplest solution, but requires you to make a new form.. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[KenReay]

Hi

I sent you an example with made up table name, made up column name and made up control name, since I am not clairvoyant, I did not know the names of your form, table, or controls. All I asked you to do was to substitute your values. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK