How do I Show and Hide Records on an AccessForm?

I would like to place a button or a checkbox in the header of an Access 2010 form that allows me to toggle between showing all records and showing only the “active” records.  The records are displayed in datasheet view.
The name of the form is CONTACT LIST.  The record source of the form is a query called CONTACTS EXTENDED.
I have a Yes/No field in the source table named HIDECONTACT which I will check when I want to mark a record as “inactive”.  I set the toggle button to this field as the record source.
How do I set the button and the form so that:
1)       On click, the form toggles between all records and only records where HIDECONTACT is null or false (not selected).
2)      Only active records are displayed when the form opens?
I tried it a dozen ways and I am confused how the filter settings in the button properties work in tandem with the VBA.  Any explanation here would be helpful.
I’m a rookie.  Thanks in advance for your help, experts!
-TH
thutchinsonAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Lets first clear something up.
A standard checkbox will be set up so that:
0=False=Off
-1=True=On
In other words "IsNull" should not be a consideration with a checkbox

Try this sample db, it performs as you specified.

JeffCoachman
Database49.mdb
0
 
Gustav BrockCIOCommented:
Set the Filter property of the form to: [HideContact] = True

Then use the button to set the FilterOn property of the form to True or False.

/gustav
0
 
Helen FeddemaCommented:
Sometimes it works better to have two subforms on the form, one filtered and one not, and then just make one visible and the other invisible depending on whether you want to filter or not.  Filters have been somewhat unreliable from Access 2007 on.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
thutchinsonAuthor Commented:
The form is not behaving properly on a consistent basis.
I want to toggle between showing all the records and showing only the records without a check in the box or toggle on/toggle off. Instead, the first record is being selected each time the control is engaged.

 It should open showing only records that are NOT checked. (HideContact=False).  When I select the checkbox, the filter should come off and show all the records.


Filter on the form is set to HideContact=False or Null
(HideContact is the Y/N field on the table. If True, contact is inactive.  Default is null.)


The checkbox (named cmdToggleActive) control event is:

 Private Sub cmdToggleActive_Click()

If Me.cmdToggleActive = True Then
    Me.FilterOn = False
    Me.Refresh
   
ElseIf Me.cmdToggleActive = False Then
    Me.FilterOn = True
    Me.Refresh
   
End If
End Sub


Could someone look and tell me if this should work?
0
 
thutchinsonAuthor Commented:
Hi Helen,

Could you elaborate on your suggestion a little more?

Thank you.
0
 
Gustav BrockCIOCommented:
> It should open showing only records that are NOT checked. (HideContact=False).  

Set these properties of the form:

Filter: HideContact=False
FilterOnLoad: True


> When I select the checkbox, the filter should come off and show all the records.

Set this property for your checkbox (named chkShowAll):

DefaultValue: False

Use this code for the checkbox:

Private Sub chkShowAll_AfterUpdate()
        Me.FilterOn = Not Me!chkShowAll.Value
End Sub

That's all that is needed.

/gustav
0
 
EirmanChief Operations ManagerCommented:
Try this method which does not use filtering, but changes the record source between two (or more queries)

Base your form on a query eg. AllRecordsQuery

Create a new query called HideInactiveQuery and in this query, under HIDECONTACT enter false in criteria.

Rather that using a combo or toggle to switch the record source, I suggest that you start off with two command buttons.

For the ALL RECORDS button enter the following (in On Click Event) .....
Me.RecordSource = "AllRecordsQuery"
Me.Requery

For the ALL ACTIVE RECORDS button enter the following .....
Me.RecordSource = "HideInactiveQuery"
Me.Requery

Once this is working, create a new question here asking how to do the above with a combo and Select Case (instead of two buttons).

Then you can create as many queries as you need and they are all only a click away with your form filtering combo.

I always found this method of changing the record source to be better than filtering.
0
 
Gustav BrockCIOCommented:
Me.Requery is not needed.

/gustav
0
 
EirmanChief Operations ManagerCommented:
Me.Requery is not needed.
Thanks gustav .... a bad coding habit I guess.
I know I need it sometimes .... not sure exactly when.

For the ALL RECORDS button enter the following (in On Click Event) .....
Me.RecordSource = "AllRecordsQuery"

For the ALL ACTIVE RECORDS button enter the following .....
Me.RecordSource = "HideInactiveQuery"
0
 
thutchinsonAuthor Commented:
I got three solutions and I'm sure they will all work.  This sample database made this one the easiest to adopt and it worked great.  Thank you Jeff.

I am also intrigued by airmen's query method and I will follow up with a new question on that after I try it out.

Many thanks to all of you.

-TH
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.