Results 1 to 10 of 10
  1. #1
    kmajors is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    5

    How to loop code to run through entire table

    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

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    kmajors is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    5
    Thanks for the reply.

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

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In pseudo code:

    Code:
    If Whatever Then
      set fields to not visible
    Else
      set fields to visible
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    kmajors is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    5
    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

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    kmajors is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    5
    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

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    kmajors is offline Novice
    Windows XP Access 2000
    Join Date
    Apr 2010
    Posts
    5
    Great. The brackets were the trick. I am past that road block now. Thanks for all your help.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    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 (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Expand entire row not just single cell in a Report
    By CityOfKalamazoo in forum Reports
    Replies: 2
    Last Post: 07-28-2016, 09:22 AM
  2. VBA code - questions table onto forms
    By Tman in forum Programming
    Replies: 3
    Last Post: 04-26-2010, 05:47 AM
  3. Replies: 9
    Last Post: 12-28-2009, 04:01 PM
  4. update table in loop based on IF statement
    By LALouw in forum Access
    Replies: 0
    Last Post: 07-26-2009, 08:46 PM
  5. Loop a table to use field in query
    By jdubp in forum Programming
    Replies: 0
    Last Post: 03-04-2008, 11:48 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums