Odd report formatting issue (1 Viewer)

[fat controller]

I have a report that has a number of groups in it, with each group having its own On Format code. One of the groups is causing me some grief (although, it could be other groups also, and this is simply the first group that I have spotted it with!).

The code for the problematic group is as follows:

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

Cancel = IsNull(Me.txtStand11)

Me.lblAvail11.Visible = (Len(Me.txtAvail11 & vbNullString) > 0)
Me.lblOpRes11.Visible = (Len(Me.txtOpRes11 & vbNullString) > 0)
Me.lblMeal11.Visible = (Len(Me.txtMeal11 & vbNullString) > 0)
Me.lblFerry11.Visible = (Len(Me.txtFerry11 & vbNullString) > 0)
Me.lblDisplay11.Visible = (Len(Me.txtDisplay11 & vbNullString) > 0)

Cancel = IsNull(Me.txtStand12)

Me.lblAvail12.Visible = (Len(Me.txtAvail12 & vbNullString) > 0)
Me.lblOpRes12.Visible = (Len(Me.txtOpRes12 & vbNullString) > 0)
Me.lblMeal12.Visible = (Len(Me.txtMeal12 & vbNullString) > 0)
Me.lblFerry12.Visible = (Len(Me.txtFerry12 & vbNullString) > 0)
Me.lblDisplay12.Visible = (Len(Me.txtDisplay12 & vbNullString) > 0)

End Sub

When the report runs, the second part of group does not display, which is the labels/fields AFTER the line "Cancel = IsNull(Me.txtStand12).

Now, here is the weird bit. If there is some data (a full stop is sufficient) in the first field of the next group, all of the above will display. OR, if I change the code to "Cancel=IsNull(Me.txtStand11), the data will display. :banghead:

Any ideas?

 

[The_Doc_Man]

Your "double Cancel" construct is probably not right. You have only one Cancel parameter to return, either true or false, but you apply it twice and you have no IF or GOTO in the code, it is straight in-line top to bottom (which is actually a good design for the rest of that code).

Ask yourself what happens if the "11" group is null but the "12" group is not. You OVERWRITE the Cancel and, in effect, CANCEL the cancellation.

I might rewrite this as

Cancel = IsNull(me.txtStand11) OR IsNull(me.txtStand12)

Or I might use AND if you wanted the group to print as long as one of the two elements wasn't null. Move this test to the top of the code ... or the bottom, doesn't make a difference which one. But since Cancel is a scalar variable, it can only return one value and you therefore should only feed it once.

 

[sneuberg]

I suggest putting in some Debug.Prints to see whats going on. You could start with

Debug.Print "IsNull(Me.txtStand11): " & IsNull(Me.txtStand11)
Debug.Print "IsNull(Me.txtStand12): " & IsNull(Me.txtStand12)

at the beginning of the subroutine. I wonder if there might be some white space in your data that could be causing your problem. Be wary of textboxes that allow returns in them as they can hide data.

 

[fat controller]

Your "double Cancel" construct is probably not right. You have only one Cancel parameter to return, either true or false, but you apply it twice and you have no IF or GOTO in the code, it is straight in-line top to bottom (which is actually a good design for the rest of that code).

Ask yourself what happens if the "11" group is null but the "12" group is not. You OVERWRITE the Cancel and, in effect, CANCEL the cancellation.

I might rewrite this as

Cancel = IsNull(me.txtStand11) OR IsNull(me.txtStand12)

Or I might use AND if you wanted the group to print as long as one of the two elements wasn't null. Move this test to the top of the code ... or the bottom, doesn't make a difference which one. But since Cancel is a scalar variable, it can only return one value and you therefore should only feed it once.

Aaarrgh! I cannot believe I have been so stupid! Thank you

It now works perfectly. All I have to do is go back and adjust the other groups accordingly.

I suggest putting in some Debug.Prints to see whats going on. You could start with

Debug.Print "IsNull(Me.txtStand11): " & IsNull(Me.txtStand11)
Debug.Print "IsNull(Me.txtStand12): " & IsNull(Me.txtStand12)

at the beginning of the subroutine. I wonder if there might be some white space in your data that could be causing your problem. Be wary of textboxes that allow returns in them as they can hide data.

And this helped prove that it is indeed working as it should be - thank you also