loop through fields?

[LucasH]

Hi,

Is there a way to loop through all fields in a report section in the OnFormat event?

Something like

For each Field in Me.Report
blah blah blah.whatever = something
Next

im pulling my hair out.

 

[dhookom]

Reports don't have fields, they have sections and controls that can be looped through. You should be able to use code like:

    Dim ctl As Control
    For Each ctl In Me.Section(0).Controls
       ctl.whatever = something
    Next

Duane
Hook'D on Access
MS Access MVP

 

[LucasH]

Ok, cool, I'll check that out.

I am just trying to loop through the controls and check if they're null or "" and set their visible property to No. I saw you're other method and I don't feel like entering code for each control, way too many (like 20). I almost got the loop but i was going...

For each field/control in Me.Report
if field.visible = len(field.value)>0
End If
Next

I will try for each ctl in Me.Section(?) - which one is details?

Thanks again.

 

[dhookom]

Sections are numbered:
0 Detail
1 Report Header
2 Report Footer
3 Page Header
4 Page Footer
5 1st Group Header
6 1st Group Footer

I'm not sure why you need to be making a control invisible if it doesn't display a value. Are you using borders or background colors that display when the value is blank or null?

Have you considered just setting the controls' Can Shrink property to Yes?

    'in the detail section On Format event
    On Error Resume Next
    Dim ctl As Control
    For Each ctl In Me.Section(0).Controls
       ctl.Visible = Len(ctl.Value & "") = 0
    Next

Duane
Hook'D on Access
MS Access MVP

 

[LucasH]

Cool thanks. Setting the properties to shrink was my first thought, but it didn't seem to hid the control (and collapse it). As far as borders, no, i don't have much, it's a basic report. I didn't spend too much time playing with the shink property, but the control has a label associated and that wasn't going away either. I tested doing it in code, and it worked if I explicitly said control.visible = No or something, so that's what led me to the loop idea.

Just saw that my code sample was pretty ugly, im not that bad.

thanks again, i'll give it a shot.

 

[dhookom]

but the control has a label associated and that wasn't going away either

If you want to hide an associated label when its text box is null, you can change the label control to a text box, set it to Can Shrink: Yes and set its Control Source to something like:
[tt][red] ="My Previous Caption " + [TextBoxControlSource][/red][/tt]
If [TextBoxControlSource] is null, then the entire expression becomes null and will shrink to nothing. Make sure the text box displays only the caption part and isn't allowed to grow. You don't want the control source value to display in the "caption" text box.

Duane
Hook'D on Access
MS Access MVP

 

[LucasH]

Yeah I saw that but didn't want to go through each label and enter that into each one.

The loop statement worked perfectly. I set the can shrink to all the text box and label controls, and when the loop goes through and finds a 0 length value, it hides the control and collapses it. Perfect.