Group multiple fields for ease of VBA code?

[ZBKAI189]

I've got a report that currently has 35 fields on it. Often, some of them are null... if this is the case, I don't want the field to be displayed. I can write 35 lines of VBA code, 1 visible=false line for each field... but is there an easier way? Can I group 30 of these fields together somehow so I only have to write one visible=false line?

 

[msrichards]

I've got a report that currently has 35 fields on it. Often, some of them are null... if this is the case, I don't want the field to be displayed. I can write 35 lines of VBA code, 1 visible=false line for each field... but is there an easier way? Can I group 30 of these fields together somehow so I only have to write one visible=false line?

You might try setting the control source of the field to =NZ([FieldX],"")

 

[boblarson]

Try this out:

Dim ctl As Control

For Each ctl In Me.Controls
   Select Case ctl.ControlType
      Case acTextBox, acComboBox, acListBox
           ctl.Visible = (Not IsNull(ctl.Value))
   End Select
Next ctl

 

[ZBKAI189]

Try this out:

Dim ctl As Control
 
For Each ctl In Me.Controls
   Select Case ctl.ControlType
      Case acTextBox, acComboBox, acListBox
           ctl.Visible = (Not IsNull(ctl.Value))
   End Select
Next ctl

Worked... sort of. It doesn't display the null fields... bravo... but it now also doesn't display some other fields that are not null.

 

[boblarson]

It is hard to display things based on code. Have you tried using conditional formatting?

 

[ZBKAI189]

It is hard to display things based on code. Have you tried using conditional formatting?

To the limit of my ability, yes. Still, I can't find a way to omit the field from the report if it's value is null.

 

[boblarson]

To the limit of my ability, yes. Still, I can't find a way to omit the field from the report if it's value is null.

Can you upload a couple of screenshots? One of the report in design view and one in report view?

 

[ZBKAI189]

Can you upload a couple of screenshots? One of the report in design view and one in report view?

The black boxes in the report view attachment are covering fields that are not null, they are displaying properly (just covered for privacy)... the fields toward the bottom (Nonconformity #2, RCA Discussion, etc) are null in this case... these are the ones I would like not to appear. Sometimes, though, they are not null.

 

[boblarson]

So the problem is not really the null fields but the LABELS. You would have to either change the way you do the labels or use code to hide them as well.

 

[ZBKAI189]

So the problem is not really the null fields but the LABELS. You would have to either change the way you do the labels or use code to hide them as well.

Yes... labels... sorry. Ok, new direction. I can write a line for each item I want to be visible=false if it's null... but after my 'else' line is there a way to say "else don't do anything I just told you to do" as opposed to writing a visible=true line for all of them?

 

[boblarson]

Just tie it to the null:

Me.Label1.Visible = (Not IsNull(Me.YourTextBoxName))
Me.YourTextboxName.Visible = (Not IsNull(Me.YourTextBoxName))

that code essentially says to display the label if the control is not null and to hide it if it is null.

 

[ZBKAI189]

Just tie it to the null:

Me.Label1.Visible = (Not IsNull(Me.YourTextBoxName))
Me.YourTextboxName.Visible = (Not IsNull(Me.YourTextBoxName))

that code essentially says to display the label if the control is not null and to hide it if it is null.

Got it... bravo.

Thanks!