[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3604
  • Last Modified:

How to make detail section of report hide based on criteria

How do I make the details section of the report hide and shrink when the checkbox in the header is checked?

I have a checkbox in my report header and details (textboxes and labels) in the 'details' part of the report. The report lists records according to a grouping and sort.

0
9XqUwH3S
Asked:
9XqUwH3S
  • 8
  • 7
  • 5
3 Solutions
 
SeanStricklandCommented:
This code works for me (place in the code behind your Report -- assume that your checkbox is named checkbox1):

-Sean
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If CheckBox1.Value = True Then
Detail.Visible = False
End If
End Sub

Open in new window

0
 
SeanStricklandCommented:
Granted, that makes the ENTIRE details section disappear.  Not just select records.  If you want to exclude certain records based on criteria, that needs to be done at the query level (the query that your report uses as its recordsource).
0
 
9XqUwH3SAuthor Commented:
That is attached to the event property of the details section (referring to top post)?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
9XqUwH3SAuthor Commented:
I'm wondering what I did wrong. Do I need to point to the header part of the form in the code (I'm wondering)?
0
 
SeanStricklandCommented:
Checkbox1.Value simply looks for any field on your report called "Checkbox1" and gets the value of it (True/False if it's a checkbox).

Detail.Visible = False hides your detail section of your report.

You shouldn't have to point to the header part of the form.  You should just be able to add that code to the Format Event Property of your Detail Section (right click the Details background -> click properties -> click events tab -> click the ... next to Format)
0
 
9XqUwH3SAuthor Commented:
Yes, I did everything like you said to do.
Would the Display When property matter?Anything else you can think that would act upon that?
 
0
 
SeanStricklandCommented:
No, that tells access whether the field should be visible at all times, only visible on the screen, or only visible when printed.  Shouldn't have anything to do with this.

The checkbox in the header... what's it named and is it a bound field (bound to a column in your recordsource/query)?

Do you have any other code behind your report?  (Open report in design view and click view->code)
0
 
9XqUwH3SAuthor Commented:
Yes, it is bound to its own query. The query has 4 tables in it.
No other code.
0
 
SeanStricklandCommented:
Not sure why this code wouldn't work, then.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If CheckBox1.Value = True Then
Detail.Visible = False
End If
End Sub

What does the report do when you open it with that code attached?
0
 
Jeffrey CoachmanCommented:
9XqUwH3S,

I'm with SeanStrickland, this should be simple, and what he posted should be working fine.

There must be something else at play here.

I think what is missing here are some explanations:
1. What this system is supposed to be doing.
2. What connection does the "Header checkbox" have with the checkbox vales in the detail section?
3. How does the Report Header checkbox get it's value?

4. It would also be a big help if you actually posted a sample of your database and what exactly you are trying that does not work.

JeffCoachman
0
 
9XqUwH3SAuthor Commented:
the db has stuff I wouldn't want open to the general public. Is there a way to get around that?
 
0
 
Jeffrey CoachmanCommented:
The key word there was "Sample".

Just create a sample db with fake data.

As long as the sample exhibits the issue.

JeffCoachman
0
 
9XqUwH3SAuthor Commented:
Okay, here is the database with sample data and the coded report. It was saved as .mdb for uploading.
Perhaps you can see why the code isn't hiding the details section based on the checkbox status field in the tblEmployers table.

SampleDatabase.mdb
0
 
Jeffrey CoachmanCommented:
9XqUwH3S,

1. Not that it matters, but this appears to be actually a "Form", that was "Saved as" a Report.
The tip-off was that there is a section called "Form Header".
... In any event I created a True Report to be sure...

2. The main issue here is that, in Access 2007, the default "View" for Reports is "Report View".
This not the same as "Print Preview", and will not trigger the code to run.

To avoid this, you can disable "Report View" in your Reports.
Open the Report in design view and set the "Allow Report View" property to: NO

(also make sure the database is in a "Trusted Location"
http://msdn.microsoft.com/en-us/library/bb421308.aspx
... a database not in a Trusted Location will not execute code either, unless you specifically tell it to)

Here is my sample, the Report is Named: Report1:

;-)

JeffCoachman

Access-EEQ24369082-Hide-Visible-.mdb
0
 
9XqUwH3SAuthor Commented:
That is a pretty big distinction regarding code and reports.
Solved!
Can you tell me what the "0' represents in the code behind the form which causes the details section to disappear based on the checkbox field status?
See snippet below.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If [Period of Unemployment] = True Then
        Me.Section(0).Visible = False
    Else
        Me.Section(0).Visible = True
    End If
End Sub

Open in new window

0
 
Jeffrey CoachmanCommented:
9XqUwH3S,

<That is a pretty big distinction regarding code and reports. >
... "It is, what it is."
;-)

<Can you tell me what the "0' represents in the code behind the form>
...The Zero represents the section Number.

You can refer to a Form/Report Section an any number of ways:

Me.Section(acDetail).Visible
Me.Section(0).Visible
Me.Section.Detail0.Visible

Since the most commonly referenced Form/Report section is the Details section, I took to using just  the shortest syntax.
;-)

JeffCoachman
0
 
Jeffrey CoachmanCommented:
Have you gotten ahold of a good Access reference book yet?
0
 
9XqUwH3SAuthor Commented:
I think I meant to say that the fact that code only runs in Page Preview and not the other views was a major difference.
Yes, I have several books. I've found Alison Balter's Access Development for Access 2007 to be the most helpful. It is quite large and follows the Inside-Out book sequence, only it makes sense.
I get so full of ideas that I tend to lose my place and I do not always have the computer with me to try the examples as closely as I should.
My bookmark is at the beginning of Advanced Report Techniques. I needed to get this done pronto. So, I signed-up again just to get that question answered.
I'm so grateful to haver solved that problem. The chapter will reinforce what you just showed me.
There just seem to be some gaps about the basic stuff that are just now starting to crystalize.
I'm beginning to wonder if you own this website. I see your name so often. LOL.
0
 
Jeffrey CoachmanCommented:
9XqUwH3S,

<There just seem to be some gaps about the basic stuff that are just now starting to crystalize.>
...This is why I always reccommend *FIRST* using the "General" Access reference books.

This way you fill in the "gaps".

The Access Development book is an "Advanced" book.

I started with the Access Bible:
http://www.amazon.com/Access-2007-Bible-Michael-Groh/dp/0470046732/ref=pd_bbs_sr_8?ie=UTF8&s=books&qid=1241372187&sr=8-8

But I am sure this book is similar:
http://www.amazon.com/Special-Using-Microsoft-Office-Access/dp/0789735970/ref=pd_bbs_9?ie=UTF8&s=books&qid=1241372187&sr=8-9

<I'm beginning to wonder if you own this website. I see your name so often>
No, I'm just stalking you.
;-)

Jeff

0
 
Jeffrey CoachmanCommented:
;-)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now