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?


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

You should not need a recordset or a loop. Test the fields and set the visibility in the detail format event. That will fire for every record. Don't forget to include an Else clause to set it the other way.

__________________
Paul
MS Access MVP
www.BaldyWeb.com

Thanks for the reply.

However, I am very new to this and need help with the Else statement you talk about.

In pseudo code:

__________________
Paul
MS Access MVP
www.BaldyWeb.com

Well I am still having some trouble. Only the 1st record (row) in my table works. When it goes to the 2nd record in the table the visible code does not work . Here is my code. Thanks so much for your help

Option Compare Database
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

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
Else
Me.Scope_of_Review__1_.Visible = True
Me.Finding_Detail__1_.Visible = True
Me.Management_Response__1_.Visible = True
Me.Results_of_Follow_Up__1_.Visible = True

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
Else
Me.Scope_of_Review__2_.Visible = True
Me.Finding_Detail__2_.Visible = True
Me.Management_Response__2_.Visible = True
Me.Results_of_Follow_Up__2_.Visible = True


End If




End Sub

Like I said, there should be no need for a recordset, and the appropriate place for the code is the detail format event. That event fires for each record.

If IsNull(Me.Whatever) Then

__________________
Paul
MS Access MVP
www.BaldyWeb.com

I think I am slowly making progress here. I took out all references to the recordset, but now the code does not appear to be hiding anything like it should.

Maybe i just put it in the wrong place. I am not sure where the 'detail format event' place you mentioned above.

But I went to 'design view' 'event' 'on open' and put [event procedure] and put code there.

Here is my new code. Thanks for your patience.

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

If IsNull("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
Else
Me.Scope_of_Review__1_.Visible = True
Me.Finding_Detail__1_.Visible = True
Me.Management_Response__1_.Visible = True
Me.Results_of_Follow_Up__1_.Visible = True

End If


If IsNull("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
Else
Me.Scope_of_Review__2_.Visible = True
Me.Finding_Detail__2_.Visible = True
Me.Management_Response__2_.Visible = True
Me.Results_of_Follow_Up__2_.Visible = True



End If
End Sub

A quick way to test if the code is running is to either set a breakpoint or throw a message box in there. One thing I notice is that you want brackets rather than quotes:

If IsNull([Follow-Up Testing Required (1)]) Then

Brackets would designate a field, the quotes would be treated as a literal string, which would never be Null.

__________________
Paul
MS Access MVP
www.BaldyWeb.com

Great. The brackets were the trick. I am past that road block now. Thanks for all your help.

No problem. I should mention that the brackets are only required because of the spaces and symbols in your names. In the long run, you'll find those aren't worth the bother. I'd get rid of them if it's not too late.

Welcome to the site by the way!

__________________
Paul
MS Access MVP
www.BaldyWeb.com