?
Solved

access: explanation of code

Posted on 2011-02-25
11
Medium Priority
?
256 Views
Last Modified: 2012-05-11
I have been given the code below which works as advertised in restricting textboxes in a report to boxes which have text in them.

Four mini questions: what is formatcount in plain english please
and how does it operate in relation to sections and
what does section (0) mean?
what would section (1) mean?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Section(0).Controls
    ctl.Visible = Not IsNull(ctl)
Next
End Sub

Open in new window

0
Comment
Question by:topUKlawyer
[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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 34981643
Here is the info on Section ... which refers to an Area of the Form or Report, such as the Form Header/Footer, Detail section ... etc: (From Help)

Section Property
See AlsoApplies ToExampleSpecificsSection property as it applies to controls on a form or report.

You can identify these controls by the section of a form or report where the control appears. Read/write Integer.

expression.Section
expression    Required. An expression that returns one of the above objects.

Remarks
For controls, you can use the Section property to determine which section of a form or report a control is in.

Section property as it applies to the Form and Report objects.

You can use the Section property to identify a section of a form or report and provide access to the properties of that section. Read-only Section object.

expression.Section(Index)
expression    Required. An expression that returns one of the above objects.

Index   Required Variant. The section number or name.

Remarks
The Section property corresponds to a particular section. You can use the following constants listed below. It is recommended that you use the constants to make your code easier to read.

Setting Constant Description
0 acDetail Form detail section or report detail section
1 acHeader Form or report header section
2 acFooter Form or report footer section
3 acPageHeader Form or report page header section
4 acPageFooter Form or report page footer section
5 acGroupLevel1Header Group-level 1 header section (reports only)
6 acGroupLevel1Footer Group-level 1 footer section (reports only)
7 acGroupLevel2Header Group-level 2 header section (reports only)
8 acGroupLevel2Footer Group-level 2 footer section (reports only)

If a report has additional group-level sections, the header/footer pairs are numbered consecutively beginning with 9.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34981653
Section(0) is the Detail section part of the report
Section(1) is the header section part of the report
Section(2) is the footer section part of the report
0
 
LVL 1

Expert Comment

by:TerrySolanen
ID: 34981778
1) FormatCount (and Cancel) are both variables that are "passed into" the subroutine (sub).  In most subroutines, variables that are passed into the subroutine are used...but neither variable is used, so you could delete the "(Cancel As Integer, FormatCount As Integer)" wording from the first line, and everything will work the same.

2) Since neither variable is used, they don't operate at all in relation to sections.

3) On the form or report this code is for, there are (or could be) multiple sections.  "Section(0)" is referring to the first one (the form detail section).

4) Section(1) would refer to the second one, aka the form header.

So, in summary, this code is a lot longer than it needs to be.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75
ID: 34981802
Here is the Help Info on FormatCount:

"You can use the FormatCount property to determine the number of times the OnFormat property has been evaluated for the current section on a report. Read/write Integer.

expression.FormatCount
expression    Required. An expression that returns one of the objects in the Applies To list.

Microsoft Access increments the FormatCount property each time the OnFormat property setting is evaluated for the current section. As the next section is formatted, Microsoft Access resets the FormatCount property to 1.

Under some circumstances, Microsoft Access formats a section more than once. For example, you might design a report in which the KeepTogether property for the detail section is set to Yes. When Microsoft Access reaches the bottom of a page, it formats the current detail section once to see if it will fit. If it doesn't fit, Microsoft Access moves to the next page and formats the detail section again. In this case, the setting for the FormatCount property for the detail section is 2 because it was formatted twice before it was printed.

You can use the FormatCount property to ensure that an operation that affects formatting gets executed only once for a section."

==== ====

mx
0
 

Author Comment

by:topUKlawyer
ID: 34981904
thanks.
how do I get to Format Count property.

thru help section I guess but can I get to it through Object browser and clicking the objects methods etc or am I barking up the wrong tree.... or just barking generally...

0
 

Author Comment

by:topUKlawyer
ID: 34981924
sokay i got it through the code window

is that right?
0
 
LVL 75
ID: 34981942
For example:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
For Each ctl In Me.Section(0).Controls
    ctl.Visible = Not IsNull(ctl)
Next
Dim x As Integer
x=FormatCount
Debug.Print x
End Sub

You can then see the value of the Format Count in the VBA immediate window - each time it changes.

mx
0
 
LVL 75
ID: 34981965
"is that right?"
More or less ... see my example above.

mx
0
 
LVL 1

Expert Comment

by:TerrySolanen
ID: 34981995
What is it that you are trying to accomplish by getting to the FormatCount?  It's not something that you can set, if that's what you are asking.

Basically, it's a behind the scenes value that MS Access updates. (based on what DatabaseMX references)

As it applies to your code in this post, it's not used.
0
 
LVL 75
ID: 34982085
I can't even remember the last time I need to use that property.  But, for some complex reports I suspect it would be handy.

mx
0
 

Author Comment

by:topUKlawyer
ID: 34983486
Thanks to everyone
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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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: …
Suggested Courses

770 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