Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I Show and Hide Records on an AccessForm?

Posted on 2013-10-31
10
Medium Priority
?
3,261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 52

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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 2000 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 52

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 24

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 52

Expert Comment

by:Gustav Brock
ID: 39616349
Me.Requery is not needed.

/gustav
0
 
LVL 24

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

618 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