Control/Section Visibility

[RacerGirl117]

I have a report in which I need to hide detail records if the BOQty is less than zero AND the Comments field does not contain the word "Advance". I am stumped on how to do this in the most efficient manner. Right now if the BOQty field is not equal to zero, it displays the detail record regardless of the value of the Comments field. For this particular report the only time we need to see negative BOQty values is if the item is an advance replacement (which means the Comments field will contain the word "advance&quot. Does that make any sense? Can anyone offer any suggestions?

The code I currently have is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

' Make Comments visible if it contains the word "Advance"
Comments.Visible = (Left$(Comments & " ", 7) = "Advance&quot

' Decide which value to display in the BOQty field
If IsNull(BOQty) Then
BOQty = Nz(Eng_Qty) - Nz(SumOfQty_Shipped)
End If

'Make Detail invisible (if BOQty is greater than zero)
Detail.Visible = Nz(BOQty, 0) > 0

End Sub

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)

'Determine the BOQtyTotal
If IsNull(BOQtyTotal) Then
BOQtyTotal = Nz(EngQtyTotal, 0) - Nz(QtyShippedTotal, 0)
End If

'Hide the Group Footer if BOQtyTotal = zero
If BOQtyTotal > 0 Then
GroupFooter1.Visible = True
Else
GroupFooter1.Visible = False
End If

End Sub


Keep in mind that I'm pretty new to this VBA thing, so some of my code may be a bit barbaric by some standards.
Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[PaulBricker]

I'm just a tad confused. Do you want to hide the Detail section if the conditions are met or do you want to hide the record. Is there more than one record in the Detail section. Why don't you do this in the underlying query. Calculate the BOQty and then on the Criteria line for BOQty put < 0 and for the Comments criteria put <>&quot;Advance&quot;.

This will filter out any of the records where BOQty is < 0 and Comments <> &quot;Advance&quot;

If you can't do it that way because you need something in your Group Footer, then tell us if you are trying to hide the detail section or the records in it.

Paul

 

[RacerGirl117]

Paul,

I'm trying to hide the section if the conditions specified are NOT met. There can be more than one record in the detail section. The BOQty is calculated in the query, but can contain null values, which is why I did what I did in my code. It just runs faster in code than it does in a query. And there are external fields in the group footer that need to be displayed even if the BOQtyTotal = zero.

My code now reads as follows:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Make Comments visible if it isn't empty
If IsNull(Comments) Then
Comments.Visible = False
Else
Comments.Visible = True
End If

'Determine the BOQty
If IsNull(BOQty) Then
BOQty = Nz(Eng_Qty) - Nz(SumOfQty_Shipped)
End If

'Make Detail visible if it is not equal to zero
Detail.Visible = Nz(BOQty, 0) <> 0

End Sub

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)

'Determine the BOQtyTotal
If IsNull(BOQtyTotal) Then
BOQtyTotal = Nz(EngQtyTotal, 0) - Nz(QtyShippedTotal, 0)
End If

'Display the group footer if BOQtyTotal is not equal to
'zero or if the Detail section is visible.
GroupFooter1.Visible = BOQtyTotal <> 0 Or Detail.Visible = True

End Sub

This seems to give me what I want. Basically, I want to show the detail section if the BOQty is not equal to zero. I also only want to show the group footer if the BOQtyTotal is not equal to zero or the detail section is visible.

I guess we'll see if this is what is really needed when the person who will be using the report comes back in Monday.

Thanks, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[PaulBricker]

Some logic is lost when using Reports so I'm not surprised that you had to use a complete If...Then Statement instead of Comments.Visible = IsNull(Comments)

Glad you got it.

Paul

 

[RickSpr]

You might want to look at the PrintSection property of reports. It's intended for just this purpose--to avoid printing a section based on conditions detected in the section's Format event. The advantage of PrintSection is that you don't have to turn it on again, like you do Visible. PrintSection is always set to True when the Format event begins. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

I forgot to mention--in addition to setting PrintSection to False, you may also want to set the MoveLayout property to False, so you don't leave a blank section in the report. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein