How to display fields for some records in a report but not others.

[weightinwildcat]

Hi. I am working on a report file that needs to display some fields when a value is less than zero and other fields when a value is equal to or greater than zero. I can put code into the Report_Current property that will format records depending on whether or not the current record has a value less than zero. If I click on another record where the value is equal to or greater than zero, fields will appear or disappear for ALL the records on my report. I need to have this happen on a record-by-record basis rather than for all the records. Any help would be greatly appreciated.

This is the sort of thing I have now:

Private Sub Report_Current()
If Me.txtGroupCounter.Value = 1 Then
Me!OrderNumberBox.visible = True
Else
Me!OrderNumberBox.visible = False
End If
End Sub

 

[MajP]

You can use conditional formatting. You can fake invisibility by making the backcolor and forecolor of the control the same as the background of the form. Although unreliable you can possibly with a report (not a form) use the on_paint event.

 

[MajP]

So I went to test this because I remember reading that this was unreliable, but for my simple report it worked fine. The only issue is that this works only in print preview and not report view. You need to put the code in the on format event. Normally you can do modifications (color, font, etc) in the on paint event and it will work for report view as well. However, you can not change visibility in the on paint event. If you need to use the report view then you are limited to conditional formatting I believe.

 

[MajP]

BTW here is a little trick. You can write your code in one line

Me!OrderNumberBox.visible = (Me.txtGroupCounter.Value = 1)

The right side resolves to True or False.

 

[weightinwildcat]

Your advice is not bad, but when we make some objects disappear we need to have some other objects appear in their place. Since the objects are not truly invisible they can overlap each other. Your idea on code seems to make sense, but so far we have not been able to implement it successfully. Any further thoughts?

 

[MajP]

I assume you are talking about using conditional formatting. Probably no way around that.
If you use the format event and print preview you can stack the controls on top of each other. When the condition is met make some visible and the others invisible.

 

[weightinwildcat]

I tried code like this in the format method of the Detail section of my report. The formatting works, but for the section AFTER it is supposed to. Any further thoughts?

Me!TextPurchLBL.visible = (IIf(IsNull([PendSum]), 0, [PendSum]) = 0)
Me!TextBuildLBL.visible = (IIf(IsNull([PendSum]), 0, [PendSum]) = 0)
Me.TextLabelLBL.visible = (IIf(IsNull([PendSum]), 0, [PendSum]) > 0)
Me.TextListNameLBL.visible = (IIf(IsNull([PendSum]), 0, [PendSum]) > 0)