looping through records in a subform

[craw]

Any help with this would be appreciated
Here's a bit of code i got from this site (ref this thread:http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=55336 ) which hides columns on a subform when there are no values present for a particular filter on the main form. well, all was fine and dandy until i realized IF there happens to be a a value in the column and it's not the first record, the whole column will not appear. I need it to show all columns that have values. I've tried the code below but it's still not looking for through each column. thanks in advance.

Private Sub Form_Current()
    On Error Resume Next
   
    Dim ctrl1 As Object
    Dim rs As Recordset
    
    Set rs = Me.RecordsetClone

    For Each ctrl1 In Me.sfrm_CToolInfo.Form
        If ctrl1.ControlType = acTextBox Then
            ctrl1.ColumnHidden = True
            'If Not rs.BOF And Not rs.EOF Then
            rs.MoveFirst
            Do Until rs.EOF
                If IsNull(ctrl1.Value) Or ctrl1 = "" Then
                    'ctrl1.ColumnHidden = True
                    rs.MoveNext
                    Else
                    ctrl1.ColumnHidden = False
                    
                    Exit Do
                End If
            Loop
                
            'End If
        End If
    Next ctrl1

 

[craw]

Pat, thanks for your response, but if i remove the 'end do' the decision to hide or not will only be based on the last record. essentially, it could toggle (for lack of a better word) back and forth between show/don't show and what ever the last column contains will dictate the result.

i'm trying this now and no matter what the columns have, it only shows the same 4 columns... strange

Dim ctrl1 As Object
    Dim ctrl2 As Object
    Dim rs As Recordset
    
    Set rs = Me.RecordsetClone

    For Each ctrl1 In Me.sfrm_CToolInfo.Form
        If ctrl1.ControlType = acTextBox Then
            If Not rs.BOF And Not rs.EOF Then
            rs.MoveFirst
            Do Until rs.EOF
                If IsNull(ctrl1.Value) Or ctrl1 = "" Then
                    ctrl1.ColumnHidden = True
                    Else
                    ctrl1.ColumnHidden = False
                End If
                rs.MoveNext
            Loop
                
            End If
        End If
    Next ctrl1

 

[craw]

I was afraid of that. And now I can't help but to ask you... is it possible to run through the recordset to test all the fields?

 

[craw]

looping through recordset

i've modified my code a bit but not having any luck. could someone please take a look and poiint me in the right direction.

Private Sub Form_Current()
    On Error Resume Next
   
    Dim ctrl1 As Object
    Dim ctrl2 As Object
    Dim rs As Recordset
    
    Set rs = RecordsetClone
    
    For Each ctrl1 In Me.sfrm_CToolInfo.Form
        ctrl1.ColumnHidden = True
        If ctrl1.ControlType = acTextBox Then
            If Not rs.BOF And Not rs.EOF Then
            rs.MoveFirst
            If IsNull(rs.Fields) Then
                rs.MoveNext
            Else
                ctrl1.ColumnHidden = False
            End If
        End If
        
    Next ctrl1

 

[craw]

Pat, i'm trying two places for the Loop... the code below doesn't appear to terminate. I tried another spot by moving the Loop just after rs.MoveNext - suggestions?

 For Each ctrl1 In Me.sfrm_CToolInfo.Form
        ctrl1.ColumnHidden = True
        If ctrl1.ControlType = acTextBox Then
            If Not rs.BOF And Not rs.EOF Then
            rs.MoveFirst
            Do Until rs.EOF
            If IsNull(rs.Fields) Then
                rs.MoveNext
                
            Else
                ctrl1.ColumnHidden = False
            End If
            Loop
        End If
        End If
    Next ctrl1