Loop through list of text values

[kiwichick]

I have a report where I'm using code to hide a label if the corresponding field is empty. This is the code:

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

    Me.[myfield1].Visible = Not IsNull(Me.[myfield1])
    Me.[myfield2].Visible = Not IsNull(Me.[myfield2])
    Me.[myfield3].Visible = Not IsNull(Me.[myfield3])
    Me.[myfield4].Visible = Not IsNull(Me.[myfield4])

End Sub

I've added a line for each of the necessary fields. This works but what I'd like to be able to do is have a list I can add the fields to and then loop through the list. Something like this:

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

    Dim strFieldNames as String
    strFieldNames =
        myfield1
        myfield2
        myfield3
        myfield4
    
    For each item in strFieldNames
        Me.[item].Visible = Not IsNull(Me.[item])

End Sub

I'm just not sure what code I need. If someone could help me out, I'd be very greatful.

[pbaldy]

I'd store them in a table for ease of maintenance. You can open a recordset on that table and loop it.

[pbaldy]

If you wanted to keep it in code, you could have a string like:

"myfield1,myfield2,myfield3"

and use the Split() function on it. It's more user-maintainable to have a table though.

[ssanfu]

In a report, it would not be a FIELD, but a CONTROL.

I have not had to make complicated reports, so I have limited experience with reports.

You might try having a table with 1 field (ControlName).
If you are doing this for multiple reports, you could have 2 fields in the table (ControlName, ReportName).
Open a record set, then loop though the record set setting the visible property of the control label in the report if the control value is empty/Null.


Edit:
Drat! Late to the party again.........

[kiwichick]

Thanks for all the suggestions about using a table instead. I don't know how to do that but will check it out

[Gicu]

Or use the tag property of the controls to mark each one you want to include then in a loop through all controls in the detail/ report check the tag value and process accordingly.
Cheers,