Conditionally Hide A Group Footer Section 1

[dleewms]

I'm using Access 2007. I need to conditionally hide a group footer section. The visible property only has two options, yes or no. I want to write an expression, I think it should work on the OnFormat event, to have the group footer visible when the parts field (this is the field upon which we're grouping) is equal to "Rods" or "Bolts.

Thanks in advance for the assistance.

 

[dhookom]

You might be able to use code in the On Format to cancel the printing of the section.

Cancel = Instr(Me.txtParts,"Rods~Bolts") = 0

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya dleewms . . .

As an example ... in the group footer [blue]On Format[/blue] event

[blue]   Dim flg As Boolean
   
   If Me.City = "london" Then flg = True
   Me.GroupFooter0.Visible = flg[/blue]

... works for me.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dleewms]

Hey AceMan1,

I have added the code exactly as you have it above to the OnFormat event of my group footer. However, the group footer is still displayed on the report. Once I add the code, when I run it, I get a dialog box to create a macro. Does a macro have to be associated with each subroutine? I'm kind of stuck here. Any assistance would be greatly appreciated.

Thanks,
dleewms

 

[dhookom]

We typically don't use macros. Both answers assume you open the code for the On Format event of the report group footer section. The On Format property should display [Event Procedure] in the property window.

I prefer to cancel the display/rendering of the section vs making it invisible.

Duane
Hook'D on Access
MS Access MVP

 

[dleewms]

I have compiled the code and still it doesn't work. Yikes!!!!

 

[dleewms]

Thanks for your response Duane.

I have tried both answers and it still doesn't work.

On the properties sheet, the visible property has been set to both Yes and No, depending on the code used.

With your code which cancels the printing of the section, I initially had the visible property for the footer set to Yes, expecting that when it got to the Rods and Bolts groups, it would not show the group footer. It shows them anyway. I have added the suggested code as an event procedure and compiled it. I'll keep trying. If you have any other suggestions, please pass them along. Thanks again for your respnses.

 

[TheAceMan1]

dleewms . . .

I took a look at a previous thread of yours at thread702-1638610, attempting to find thia all alluring parts fieldname. If I'm right it is actually [blue]Desc[/blue]!

Using the code provided by [blue]dhookom[/blue] you should have:

[blue]Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
   Cancel = (InStr(Me.[purple][b]Desc[/b][/purple], "Rods~Bolts") = 0)
End Sub[/blue]

[blue]Don't forget to provide actual names of items if you want to speed things up. If you don't we can only guess ...[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

dleewms,
Please describe what results are on you screen rather than stating "it still doesn't work". We don't know if the section is always displayed or never displayed or you get an error message or what. Also, please paste your code into a reply. Both the theAceman1 and I have posted code so you should return the favor. The field you want to test against must be bound to a control in the section.

Duane
Hook'D on Access
MS Access MVP

 

[dleewms]

TheAceMan1 & Duane,

Again thank you for your help. Also, Duane thank you for informing me that I was not providing accurate information in my replies.

The visible property on the groupfooter2 is set to Yes. However, I only want to see the Groupfooter 2 section when the PartsDesc field equals Rods or Bolts.

I have placed the code in the On Format event expecting to only see the group footer section (which contains totals) for Rods and Bolts, instead, I continue to see the Group Footer section for each part.

Below is the code I'm using

TheAceMan1

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (InStr(PartsDesc, "Rods~Bolts") = 0)
End Sub

Duane

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
Dim flg As Boolean
If Me.PartsDesc = "Rods" Then flg = True
Me.GroupFooter2.Visible = flg
End Sub

After adding both sets of code, I complie the code and save it. Then I go to the report view and the results are the same with both sets of code. A group footer section is returned for each part not just the one(s) defined.

Thanks,
DLee

 

[dleewms]

I figured out what was going on. In Report View, the code was not read, i.e. all sections showed up. However, when I looked at the report in Print Preview, only the Rods and Bolts sections are visible. This is exactly what I need. I was finally able to accomplish what I needed using Duane's suggested code.

Thanks guys!

 

[dleewms]

This is the code that actually resolved my issue. I think it actually may have originally come from you TheAceMan1. In any case, thanks to both of you.

Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)Dim flg As BooleanIf Me.PartsDesc = "Rods" Then flg = TrueMe.GroupFooter2.Visible = flgEnd Sub

 

[TheAceMan1]

dleewms . . .
[ol][li]The length of this thread has come about simply because we never knew the control name in question. 1st I thought it was [blue]Parts[/blue]. [blue]dhookom[/blue] thought it was [blue]txtParts[/blue]. Checking another thread you showed it to be [blue]Desc[/blue]. Finally in your latest postings you show it to be [blue]PartsDesc[/blue]! Considering you entered the code exactly as we gave it to you ... how could you expect the code to work if the [blue]names of the players[/blue] are wrong.

[blue]The first time you run any code always check that naming matches what you have. Otherwise you'll be wasting your time. This is what happens for something as simple as a typo! So from here on when you start a thread be sure to give the names of the players, table, field(s), form/report names, control names, query names ... ect. You'll reach resolution more quickly.[/blue]

[/li]
[li]

[blue]Also, please paste your code into a reply.[/blue]

This is a [blue]must do[/blue]. VBA is a line by line affair and thats exactly how we read it. Pasting also prevents any typo's on your part. When I saw:

[blue]Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)Dim flg As BooleanIf Me.PartsDesc = "Rods" Then flg = TrueMe.GroupFooter2.Visible = flgEnd Sub[/blue]

I was tempted to leave the thread. It should've looked like:

[blue]Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
   Dim flg As Boolean
   
   If Me.PartsDesc = "Rods" Then flg = True
   Me.GroupFooter2.Visible = flg
End Sub[/blue]

You'll run tipsters away posting code the way you have! If you look at any of my posts you'll always find them nicely formatted espcially when it comes to code. It makes for easier reading for everyone ... including myself [/li]
[li]Finally ... the code is still not correct! You did mention [blue]Rods & Bolts[/blue]! So that would be:

[blue]   Dim flg As Boolean
   
   If Me.PartsDesc = "Rods" Or _
      Me.PartsDesc = "Bolts" Then flg = True
   Me.GroupFooter2.Visible = flg[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dleewms]

Hi TheAceMan!

Thanks again for your comments. I will take them to heart and apply them to my next post.

DLee