Solved

How do I Show and Hide Records on an AccessForm?

Posted on 2013-10-31
10
2,091 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
ID: 39615341
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
ID: 39615593
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
ID: 39615826
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:thutchinson
ID: 39615828
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
ID: 39615951
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39616096
> 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
ID: 39616341
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
ID: 39616349
Me.Requery is not needed.

/gustav
0
 
LVL 23

Expert Comment

by:Eirman
ID: 39616365
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
ID: 39621968
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql statement - 9 24
User Level Security 6 38
Setting Macro in Access to Automate Running an Append at a Certain Time 2 22
access query to sql server 3 20
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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

778 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