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

 

[Pat Hartman]

Your MoveNext is in the wrong place. It needs to be outside the If so that it is executed regardless of the path the If takes. Also get rid of the Exit Do. You don't want to exit the loop until you have read each record.

 

[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

 

[Pat Hartman]

I'm not sure where my head was when I answered the question last night. I think that the problem is that you are testing the value of the control rather than the value of the recordset field. The control can only contain the value for the current record. Stepping through the recordset clone does not change the current record pointer so your code only ever tests the "visible" record.

 

[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

 

[Pat Hartman]

You don't have a loop inside your new code.

 

[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