How to get VBA Code to Loop through rows in table

[kmajors]

Hi,

I am trying to get my report to hide certain fields from the table. I was successful to do this with data in the first row of the table, but the code does not seem to repeat to row 2 of the table.

I will post my code below, any help would be great. I know the table is not normalized, but for what we are working with that is the way it is. How do I get the code to loop through all the rows in the table and stop?
Thanks for your help!

Option Compare Database
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub
Private Sub Report_Open(Cancel As Integer)
Dim myrs As Recordset
Dim myvar1, myvar2, myvar3 As String
Dim conn As Connection
Set conn = CurrentProject.Connection
Set myrs = New Recordset

myrs.Open "[Audit Issues 2009]", conn, adOpenDynamic, adLockOptimistic
myrs.MoveFirst
While Not myrs.EOF

If IsNull(myrs("Follow-Up Testing Required (1)")) Then
Me.Scope_of_Review__1_.Visible = False
Me.Finding_Detail__1_.Visible = False
Me.Management_Response__1_.Visible = False
Me.Results_of_Follow_Up__1_.Visible = False

End If

If IsNull(myrs("Follow-Up Testing Required (2)")) Then
Me.Scope_of_Review__2_.Visible = False
Me.Finding_Detail__2_.Visible = False
Me.Management_Response__2_.Visible = False
Me.Results_of_Follow_Up__2_.Visible = False

End If


If IsNull(myrs("Follow-Up Testing Required (3)")) Then
Me.Scope_of_Review__3_.Visible = False
Me.Finding_Detail__3_.Visible = False
Me.Management_Response__3_.Visible = False
Me.Results_of_Follow_Up__3_.Visible = False

Exit Sub
End If

Wend
End Sub

 

[DCrake]

Everything in the detail section will be governed by this command as it is only read once, so to speak. You would have to move the detail to a spereate section that groups by record.

 

[kmajors]

Thanks for your quick reply. I am very new to Access and VBA, so I do not really now how to do what you said.

 

[HGMonaro]

I think you're going about this the wrong way.

A report should be linked to a table or query via the "Record Source" parameter of the report. There's no need to create your own loop. In your case this should point to "Audit Issues 2009". You then have all your IF statements in the Detail_Format event.