Solved

How do I Show and Hide Records on an AccessForm?

Posted on 2013-10-31
10
1,719 Views
Last Modified: 2013-11-04
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
0
Comment
Question by:thutchinson
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
 

Author Comment

by:thutchinson
Comment Utility
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
 

Author Comment

by:thutchinson
Comment Utility
Hi Helen,

Could you elaborate on your suggestion a little more?

Thank you.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
> 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
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
Me.Requery is not needed.

/gustav
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
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
 

Author Closing Comment

by:thutchinson
Comment Utility
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now