Visible = True Vs Visble = False

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.

My VB skills are weak (ok practically non-existant); however, I inherited
this DB from someone who's code kind of works, but not in all instances.

Data Structure for the Report is:
FunctionOrder, Function, ActivityOrder, Activity, Risk.

Each Function may contain mulitple Activities.
Each Activity may contain multiple Risk.

I wish to supress the display for the FunctionOrder, Function,
ActivityOrder, and Activity for all but the first record.

Below is the code I inherited, it kind of works. On some pages the
FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am resonably
sure it occurs when a new activity starts on a new page and the risks
associated to the Activity can not fit on the page.

Any suggestions would be greatly appreciated.

Code:
---------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Declaring Function and pageno variables locally and as static
' So that if another report is running at same time, it will not use them
' as it might had they been declared publicly or with the same
' name as in the other report.

' Note - this report has code which controls manually when and where the
' "F-", the function order and the function print on the page.
' To handle this manually or programmatically, the code gets somewhat
complicated.
' One aspect handled is the suppression of this function information on the
' next page when a function begins on the previous page and continued to the
next
' page.
Static strrptRIAFunction As String
Static strrptRIAActivity As String

Static reccount As Integer
'set line width
Me.DrawWidth = 10
'create gridlines
Me.Line (0, 0)-(0, 15000)
Me.Line (3115, 0)-(3115, 15000)
Me.Line (6780, 0)-(6780, 15000)
Me.Line (8980, 0)-(8980, 15000)
Me.Line (9540, 0)-(9540, 15000)
Me.Line (11830, 0)-(11830, 15000)
Me.Line (14300, 0)-(14300, 15000)
Me.Line (15110, 0)-(15110, 15000)

' Defines the Text Fields used to designate Function and Activity (prefix
for FunctionOrder and ActivityOrder
Me.Text14 = "F-"
Me.Text101 = "A-"

' Set default of visible
Me.Text14.Visible = True
Me.Function.Visible = True
Me.FunctionOrder.Visible = True
Me.Text101.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True

' Set Visibility
If Me.Function.OldValue <> strrptRIAFunction Then
Me.Text14.Visible = True
Me!FunctionOrder.Visible = True
Me!Function.Visible = True
End If

If Me.Function.OldValue = strrptRIAFunction Then
Me.Text14.Visible = False
Me!FunctionOrder.Visible = False
Me!Function.Visible = False
End If


If Me.Activity.OldValue <> strrptRIAActivity Then
Me.Text101.Visible = True
Me!ActivityOrder.Visible = True
Me!Activity.Visible = True
End If

If Me.Activity.OldValue = strrptRIAActivity Then
Me.Text101.Visible = False
Me!ActivityOrder.Visible = False
Me!Activity.Visible = False
End If


strrptRIAFunction = Nz(Me.Function.OldValue)
strrptRIAActivity = Nz(Me.Activity.OldValue)

End Sub
 
Are you printing the five fields in a detail section? If so, you can add
another control to determine if the fields should print.

New text control
Name: LineCounter
Control Source: =1
Running Sum: Overall

Now add code to the Detail Section's format event

If Me.LineCounter = 1 then
Me.FunctionOrder.visible = True
Me.Function.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True
Else
Me.FunctionOrder.visible = False
Me.Function.Visible = False
Me.ActivityOrder.Visible = False
Me.Activity.Visible = False

End IF

That will show the four controls for the first record and hide them for
every other record in the report. If that is not what you want, then post
back with a better description of what you do want.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I realize that I should have said REPLACE all that code in the Detail format
section with ... instead of "Add Code"

By the way, do this all on a COPY of your report.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Yes I am printing the fivel fields in the detail section. The Detail Section
is also where I call the Event Procedure.

I implemented what you suggested; however, it suppresses the values for all
but the first FunctionOrder, Function, ActivityOrder and Activity.

I modified what you suggested slightly and have ALMOST what I need.

By setting the "Running Sum" to "No". It produces the desired output for
everything I mentioned previously.

I do have two text fields used as a Prefix before the FunctionOrder and
ActivityOrder which I also need to set as Visible = False, when the
FuctionOrder and the ActivityOrder are the same as the previous record(s).

I take it from the code below, this does not work because these are Text
field and not numeric items which can be counted.

New Code:
----------
' Define Labels for FuctionOrder and ActivityOrder prefixes
Me.Text14 = "F-"
Me.Text101 = "A-"

'create gridlines
Me.Line (0, 0)-(0, 15000)
Me.Line (3115, 0)-(3115, 15000)
Me.Line (6780, 0)-(6780, 15000)
Me.Line (8980, 0)-(8980, 15000)
Me.Line (9540, 0)-(9540, 15000)
Me.Line (11830, 0)-(11830, 15000)
Me.Line (14300, 0)-(14300, 15000)
Me.Line (15110, 0)-(15110, 15000)

' Now add code to the Detail Section's format event
If Me.LineCounter = 1 Then
Me.Text14.Visible = True
Me.FunctionOrder.Visible = True
Me.Function.Visible = True
Me.Text101.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True
Else
Me.Text14.Visible = False
Me.FunctionOrder.Visible = False
Me.Function.Visible = False
Me.Text101.Visible = False
Me.ActivityOrder.Visible = False
Me.Activity.Visible = False

End If
End Sub
 
I knew to replace and not add, but thanks.

Given what I said in the other reply regarding the "Running Sum" is set to
"No".

I did just realize that the text for the Function and Activity do not repeat
on each line, but the FunctionOrder and ActivityOrder do.


Example of what I want the data to look like:

F-1 F1Text A-1 A1Text Risk
Risk
Risk
A-2 A2Text Risk
F-2 F2Text A-1 A1Text Risk
A-2 A2Text Risk
Risk
Risk
F-3 F3Text A-1 A1Text Risk
 
You might want to try a completely different approach that I
think(?) does what you want without any complicated logic.

Create a group with header for each of the fields you want
to hide. Add a text box for the field to the group header
and make sure the group header sections are the same height
as the detail section. The code for the group header
sections' Format event would simply be:
Me.MoveLayout = False

The only controls in the detail section would be the ones
you want to display on every line. The only(?) code needed
for the detail section would be the line drawing code.
 
Customer doesn't like using the Single Header lines. They want it to look
similiar to an Excel spreadsheet, but only to display the FunctionOrder,
Function, ActivityOrder, and Activity when the value changes.

F-1 Function1Text A-1 Activity1Text Risk
Risk
Risk
A-2 Activity2Text Risk
Risk
F-2 Function2Text A-1 Activity1Text Risk
Risk
A-2 Activity2Text Risk
F-3 Function3Text A-1 Activity3Text Risk




Marshall Barton said:
You might want to try a completely different approach that I
think(?) does what you want without any complicated logic.

Create a group with header for each of the fields you want
to hide. Add a text box for the field to the group header
and make sure the group header sections are the same height
as the detail section. The code for the group header
sections' Format event would simply be:
Me.MoveLayout = False

The only controls in the detail section would be the ones
you want to display on every line. The only(?) code needed
for the detail section would be the line drawing code.
--
Marsh
MVP [MS Access]

I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.

My VB skills are weak (ok practically non-existant); however, I inherited
this DB from someone who's code kind of works, but not in all instances.

Data Structure for the Report is:
FunctionOrder, Function, ActivityOrder, Activity, Risk.

Each Function may contain mulitple Activities.
Each Activity may contain multiple Risk.

I wish to supress the display for the FunctionOrder, Function,
ActivityOrder, and Activity for all but the first record.

Below is the code I inherited, it kind of works. On some pages the
FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am resonably
sure it occurs when a new activity starts on a new page and the risks
associated to the Activity can not fit on the page.

Any suggestions would be greatly appreciated.

Code:
---------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Declaring Function and pageno variables locally and as static
' So that if another report is running at same time, it will not use them
' as it might had they been declared publicly or with the same
' name as in the other report.

' Note - this report has code which controls manually when and where the
' "F-", the function order and the function print on the page.
' To handle this manually or programmatically, the code gets somewhat
complicated.
' One aspect handled is the suppression of this function information on the
' next page when a function begins on the previous page and continued to the
next
' page.
Static strrptRIAFunction As String
Static strrptRIAActivity As String

Static reccount As Integer
'set line width
Me.DrawWidth = 10
'create gridlines
Me.Line (0, 0)-(0, 15000)
Me.Line (3115, 0)-(3115, 15000)
Me.Line (6780, 0)-(6780, 15000)
Me.Line (8980, 0)-(8980, 15000)
Me.Line (9540, 0)-(9540, 15000)
Me.Line (11830, 0)-(11830, 15000)
Me.Line (14300, 0)-(14300, 15000)
Me.Line (15110, 0)-(15110, 15000)

' Defines the Text Fields used to designate Function and Activity (prefix
for FunctionOrder and ActivityOrder
Me.Text14 = "F-"
Me.Text101 = "A-"

' Set default of visible
Me.Text14.Visible = True
Me.Function.Visible = True
Me.FunctionOrder.Visible = True
Me.Text101.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True

' Set Visibility
If Me.Function.OldValue <> strrptRIAFunction Then
Me.Text14.Visible = True
Me!FunctionOrder.Visible = True
Me!Function.Visible = True
End If

If Me.Function.OldValue = strrptRIAFunction Then
Me.Text14.Visible = False
Me!FunctionOrder.Visible = False
Me!Function.Visible = False
End If


If Me.Activity.OldValue <> strrptRIAActivity Then
Me.Text101.Visible = True
Me!ActivityOrder.Visible = True
Me!Activity.Visible = True
End If

If Me.Activity.OldValue = strrptRIAActivity Then
Me.Text101.Visible = False
Me!ActivityOrder.Visible = False
Me!Activity.Visible = False
End If


strrptRIAFunction = Nz(Me.Function.OldValue)
strrptRIAActivity = Nz(Me.Activity.OldValue)

End Sub
Click to expand...
Click to expand...
 
It kind of worked, but I had a couple of little cliches with the displaying
of information which started on one page, but could not finish on the same
page. Sometimes a Function would display without lines and all the
activities would start on the next page.

After I changed how the "Keep Together" attribute worked for the Sorting and
Grouping and in the Section headers I finally got it working the way it
should.

Thanks!!

Simple usually is better.

Thanks again!!

Marshall Barton said:
I already understood that. Try my suggestion and see how
close it comes.
--
Marsh
MVP [MS Access]

Customer doesn't like using the Single Header lines. They want it to look
similiar to an Excel spreadsheet, but only to display the FunctionOrder,
Function, ActivityOrder, and Activity when the value changes.

F-1 Function1Text A-1 Activity1Text Risk
Risk
Risk
A-2 Activity2Text Risk
Risk
F-2 Function2Text A-1 Activity1Text Risk
Risk
A-2 Activity2Text Risk
F-3 Function3Text A-1 Activity3Text Risk
Click to expand...
Click to expand...
 
Well....I thought I had it all done, but...

On a couple of records the Activity bleeds into the next row and overtype
the new row.

Not sure why it is doing it except the activity values is a little longer
than most of the others.

Any suggestions, Marshall?

dsc2bjn said:
It kind of worked, but I had a couple of little cliches with the displaying
of information which started on one page, but could not finish on the same
page. Sometimes a Function would display without lines and all the
activities would start on the next page.

After I changed how the "Keep Together" attribute worked for the Sorting and
Grouping and in the Section headers I finally got it working the way it
should.

Thanks!!

Simple usually is better.

Thanks again!!

Marshall Barton said:
I already understood that. Try my suggestion and see how
close it comes.
--
Marsh
MVP [MS Access]

Customer doesn't like using the Single Header lines. They want it to look
similiar to an Excel spreadsheet, but only to display the FunctionOrder,
Function, ActivityOrder, and Activity when the value changes.

F-1 Function1Text A-1 Activity1Text Risk
Risk
Risk
A-2 Activity2Text Risk
Risk
F-2 Function2Text A-1 Activity1Text Risk
Risk
A-2 Activity2Text Risk
F-3 Function3Text A-1 Activity3Text Risk


:
You might want to try a completely different approach that I
think(?) does what you want without any complicated logic.

Create a group with header for each of the fields you want
to hide. Add a text box for the field to the group header
and make sure the group header sections are the same height
as the detail section. The code for the group header
sections' Format event would simply be:
Me.MoveLayout = False

The only controls in the detail section would be the ones
you want to display on every line. The only(?) code needed
for the detail section would be the line drawing code.


dsc2bjn wrote:
I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.
Click to expand...
Click to expand...
Click to expand...
 
It is only when the Activity has one word to put on the next line that it
overlaps the next row of data.

Length of text is not the issue, since I found shorter ones that do the same
thing.



dsc2bjn said:
Well....I thought I had it all done, but...

On a couple of records the Activity bleeds into the next row and overtype
the new row.

Not sure why it is doing it except the activity values is a little longer
than most of the others.

Any suggestions, Marshall?

dsc2bjn said:
It kind of worked, but I had a couple of little cliches with the displaying
of information which started on one page, but could not finish on the same
page. Sometimes a Function would display without lines and all the
activities would start on the next page.

After I changed how the "Keep Together" attribute worked for the Sorting and
Grouping and in the Section headers I finally got it working the way it
should.

Thanks!!

Simple usually is better.

Thanks again!!

Marshall Barton said:
I already understood that. Try my suggestion and see how
close it comes.
--
Marsh
MVP [MS Access]


dsc2bjn wrote:
Customer doesn't like using the Single Header lines. They want it to look
similiar to an Excel spreadsheet, but only to display the FunctionOrder,
Function, ActivityOrder, and Activity when the value changes.

F-1 Function1Text A-1 Activity1Text Risk
Risk
Risk
A-2 Activity2Text Risk
Risk
F-2 Function2Text A-1 Activity1Text Risk
Risk
A-2 Activity2Text Risk
F-3 Function3Text A-1 Activity3Text Risk


:
You might want to try a completely different approach that I
think(?) does what you want without any complicated logic.

Create a group with header for each of the fields you want
to hide. Add a text box for the field to the group header
and make sure the group header sections are the same height
as the detail section. The code for the group header
sections' Format event would simply be:
Me.MoveLayout = False

The only controls in the detail section would be the ones
you want to display on every line. The only(?) code needed
for the detail section would be the line drawing code.


dsc2bjn wrote:
I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.
Click to expand...
Click to expand...
Click to expand...
 
If any of the values in any of the group headers grows and
the detail does not, you will see that problem.

This is one of the problems I was trying to avoid when I
said to make the headers the same Height as the detail.
That indirectly implied that using CanGrow in any or the
group headers would require additional trickery.

To work around the issue, add a module level varable:
Private lngMaxHdr As Long
to keep track of the tallest header. Then add code to the
top level group header's PRINT event procedure:
lngMaxHdr = Me.Height
and to each other group header's PRINT event procedure:
If Me.Height > lngMaxHdr Then lngMaxHdr = Me.Height

It is only when the Activity has one word to put on the next line that it
overlaps the next row of data.

Length of text is not the issue, since I found shorter ones that do the same
thing.



dsc2bjn said:
Well....I thought I had it all done, but...

On a couple of records the Activity bleeds into the next row and overtype
the new row.

Not sure why it is doing it except the activity values is a little longer
than most of the others.

Any suggestions, Marshall?

dsc2bjn said:
It kind of worked, but I had a couple of little cliches with the displaying
of information which started on one page, but could not finish on the same
page. Sometimes a Function would display without lines and all the
activities would start on the next page.

After I changed how the "Keep Together" attribute worked for the Sorting and
Grouping and in the Section headers I finally got it working the way it
should.

Thanks!!

Simple usually is better.

Thanks again!!

:

I already understood that. Try my suggestion and see how
close it comes.
--
Marsh
MVP [MS Access]


dsc2bjn wrote:
Customer doesn't like using the Single Header lines. They want it to look
similiar to an Excel spreadsheet, but only to display the FunctionOrder,
Function, ActivityOrder, and Activity when the value changes.

F-1 Function1Text A-1 Activity1Text Risk
Risk
Risk
A-2 Activity2Text Risk
Risk
F-2 Function2Text A-1 Activity1Text Risk
Risk
A-2 Activity2Text Risk
F-3 Function3Text A-1 Activity3Text Risk


:
You might want to try a completely different approach that I
think(?) does what you want without any complicated logic.

Create a group with header for each of the fields you want
to hide. Add a text box for the field to the group header
and make sure the group header sections are the same height
as the detail section. The code for the group header
sections' Format event would simply be:
Me.MoveLayout = False

The only controls in the detail section would be the ones
you want to display on every line. The only(?) code needed
for the detail section would be the line drawing code.


dsc2bjn wrote:
I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.
Click to expand...
Click to expand...
Click to expand...
 
I still have an issue.

I think I did what you suggested.

I created a new database module and add the line, Private lngMaxHdr As Long.
I saved and closed the the database module.

You stated to put the "lngMaxHdr = Me.Height" within the top level group
header. In this case it was my Function header.

I added, "If Me.Height > lngMaxHdr Then lngMaxHdr = Me.Height" to the
Activity and Detail sections.

I still have items on the report where the Function and Activty descriptions
over run the details.

Did you mean Module or just part of the report VB script for the "lngMaxHdr
= Me.Height"?




Marshall Barton said:
If any of the values in any of the group headers grows and
the detail does not, you will see that problem.

This is one of the problems I was trying to avoid when I
said to make the headers the same Height as the detail.
That indirectly implied that using CanGrow in any or the
group headers would require additional trickery.

To work around the issue, add a module level varable:
Private lngMaxHdr As Long
to keep track of the tallest header. Then add code to the
top level group header's PRINT event procedure:
lngMaxHdr = Me.Height
and to each other group header's PRINT event procedure:
If Me.Height > lngMaxHdr Then lngMaxHdr = Me.Height

It is only when the Activity has one word to put on the next line that it
overlaps the next row of data.

Length of text is not the issue, since I found shorter ones that do the same
thing.



dsc2bjn said:
Well....I thought I had it all done, but...

On a couple of records the Activity bleeds into the next row and overtype
the new row.

Not sure why it is doing it except the activity values is a little longer
than most of the others.

Any suggestions, Marshall?

:

It kind of worked, but I had a couple of little cliches with the displaying
of information which started on one page, but could not finish on the same
page. Sometimes a Function would display without lines and all the
activities would start on the next page.

After I changed how the "Keep Together" attribute worked for the Sorting and
Grouping and in the Section headers I finally got it working the way it
should.

Thanks!!

Simple usually is better.

Thanks again!!

:

I already understood that. Try my suggestion and see how
close it comes.
--
Marsh
MVP [MS Access]


dsc2bjn wrote:
Customer doesn't like using the Single Header lines. They want it to look
similiar to an Excel spreadsheet, but only to display the FunctionOrder,
Function, ActivityOrder, and Activity when the value changes.

F-1 Function1Text A-1 Activity1Text Risk
Risk
Risk
A-2 Activity2Text Risk
Risk
F-2 Function2Text A-1 Activity1Text Risk
Risk
A-2 Activity2Text Risk
F-3 Function3Text A-1 Activity3Text Risk


:
You might want to try a completely different approach that I
think(?) does what you want without any complicated logic.

Create a group with header for each of the fields you want
to hide. Add a text box for the field to the group header
and make sure the group header sections are the same height
as the detail section. The code for the group header
sections' Format event would simply be:
Me.MoveLayout = False

The only controls in the detail section would be the ones
you want to display on every line. The only(?) code needed
for the detail section would be the line drawing code.


dsc2bjn wrote:
I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.
Click to expand...
Click to expand...
Click to expand...
 
dsc2bjn said:
I think I did what you suggested.
Click to expand...


Forget that earlier partial workarounf, I wasn't done with
it and did not mean to send it. The problem is even messier
than I thought it would be.

It turns out that the best I could do ran into another issue
when one of the headers grow and the detail grows, then
Access will grow the detail too much. It works well if the
detail grows but none of the headers grow. It also works if
any header grows but the detail does not grow.

The messiness starts by adding an unbound hidden text box
named txtMaxHdr to the top level group header. This will be
set to the height of the tallest header.

The next step is to create a group header section named
DummyHdr for the detail sorting level. (If you are not
sorting the details, create a group level on the PK field.)
Add a text box named txtLine to this section and set its
control source to =1 and RunningSum property to Over Group.
This will be used to determine the first detail after one of
the other headers has a new value. Make this header section
invisible because it is only used to help the code.

Hear's a copy of my report module. I hope you can unravel
the line wrapping.
-----------------------------------------------------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
If Me.txtLine = 1 And Me.Section(0).Height < txtMaxHdr
Then
Me.Section(0).Height = txtMaxHdr
End If
txtMaxHdr = 0
End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As
Integer)
Me.Line (Me.EmployeeID.Left, 0)
-Step(Me.EmployeeID.Width, Me.Height), , B
Me.Line (Me.AbsenceDate.Left, 0)
-Step(Me.AbsenceDate.Width, Me.Height), , B
Me.Line (Me.Reason.Left, 0)-Step(Me.Reason.Width,
Me.Height), , B
End Sub

Private Sub DummyHdr_Format(Cancel As Integer, FormatCount
As Integer)
Me.Section(0).Height = 0 'reduce detail size in case
it was changed
End Sub

Private Sub EmployeeHdr_Print(Cancel As Integer, PrintCount
As Integer)
txtMaxHdr = Me.Height 'top group
Me.MoveLayout = False
End Sub

Private Sub AbsenceHdr_Print(Cancel As Integer, PrintCount
As Integer)
If Me.Height > txtMaxHdr Then txtMaxHdr = Me.Height
Me.MoveLayout = False 'intermediate groups
End Sub
-----------------------------------------------------------
I tested that code in A2003. I think it should work in
A2002, but don't know what will happen in A2007. It can not
work in A2k or earlier versions. If you are using A97 or
A2K then I might or might not be able to think up another
way, but would rather not try unless it's necessary.
 
I will try it and see how it works, but I have created a pseudo-workaround.

I increased the height of all the headers to allow for the height of the
first header group print completely (granted if someone enters a longer
values it would still run over).

This wil buy me some time to attempt your solution.
 
Back
Top