How to compress a field that has no value

[itlearner]

There is a sub-report in a report (Access 97). I would like the fields in the sub-report to be compressed if the values are null, and eliminate the space from the sub-report.

I try to write the code to do it but never success:

Private Sub Report_Open(Cancel As Integer)

If Me.Action3 is Null Then
Me.Action3.Visible = False
End If

End Sub

Please help.

 

[pbaldy]

Probably not tough (the can grow property of the textbox if available in 97), but I'd wonder why you'd have a record with no value in the first place.

 

[itlearner]

A record in the sub-report has more than 20 fields. Some of the fields has no value but still display on the report. I would like to compress them for saving space and make the report more readable. Any help will be wellcome.

 

[pbaldy]

Already mentioned the can grow property, though as mentioned don't know if it was available in 97. To set the visibility as in your example, you'd want to be in the format event of the report section containing the textbox, not the open event.

 

[itlearner]

Hi, I put the code to the report detail section as follow, the error message is came up "Run time error '424', Object required" (Me.Action3). The code never works. Action3 is a text box with data control source Action3.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Action3 Is Not Null Then
Me.Action3.Visible = True
Else
Me.Action3.Visible = False
End If
End Sub

Please help.

 

[pbaldy]

If Not IsNull(Me.Action3) Then

 

[Adeptus]

may also have to use Me.Action3.Value to make sure it's not comparing the control object?

 

[boblarson]

Just a note -

If you set all of the controls to CAN SHRINK (not Can Grow as mentioned by PBaldy) - YES and the DETAILS SECTION to CAN SHRINK - YES, you can get this to work, but ONLY if there are no fields with data on the same physical horizontal position as a control with data AND you can't have labels otherwise they will show even if there is no data in the control they're tied to.

 

[pbaldy]

If you set all of the controls to CAN SHRINK (not Can Grow as mentioned by PBaldy)

Can Grow will work fine, presuming you set the control(s) to a negligible height to start with (which I should have mentioned earlier). That way, it only takes up space if there's something there. It will work your way too, it just a matter of which direction you want to approach the problem from.

 

[itlearner]

Hi, I have tried the cobmination of the "Can Shrink"/"Can Grow" of the controls and the Details of the report, all cobmination are not work. Anyway, it works now. Two things:

1) I renamed the control from Action3 to txtAction3
2) and worte the code under Detail_Format event.

The code is showed as below.

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

Me.txtAction3.Visible = Not IsNull(Me.txtAction3)

End Sub

Thanks for all the help.