none
Looping Through Records RRS feed

  • Question

  • Hello - I am looking for some assistance in looping through records on a Single form.  I have pieced together a little code below but does not seem to work.

    I am just looking to loop through all records. Don't need to do anything with the records, but view them. This is going to be kind of like a banner type thing, that displays the form and just cycles through all of the records based on the forms timer interval. This is for show only. User will just be able to view the various records as they cycle.

    Anyone know how to get something like below to work? Or possible another suggestion? Again, just need a simple, continuous loop, to continuously cycle;e through the records...

    Here is what I have come up with thus far:

    Private Sub Form_Timer()

    Dim rs  As Recordset
    Dim iCount As Integer
     
        With rs
            If .RecordCount <> 0 Then
                    .MoveLast
                iCount = rs.RecordCount
     
                Do While Not .BOF
                    .MovePrevious
                Loop
            End If
        End With

    End Sub

    Any ideas or suggestions are always greatly appreciated. Thank you in advance for your time!

    Thursday, August 22, 2019 5:44 PM

Answers

  • Hello all -

    Ok so I figured it out :-)  At least what I was trying to accomplish. I added a "Pause" function that slowed down the changing between records....the below works perfectly. I can adjust the time of the pause to leave the record on screen for as long as I want....can also adjust the amount of time to re-trigger the event using the Form_Timer event.

    Works well together:

    Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Err_Pause

        Dim PauseTime As Variant, start As Variant

        PauseTime = NumberOfSeconds
        start = Timer
        Do While Timer < start + PauseTime
        DoEvents
        Loop

    Exit_Pause:
        Exit Function

    Err_Pause:
        MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
        Resume Exit_Pause

    End Function

    ------------------------------------------------------------------------------------------------------

    Private Sub Form_Timer()

    Dim rs  As Recordset
    Dim iCount As Integer
        
        Set rs = Me.Recordset
        
            With rs
                If .RecordCount <> 0 Then
                    iCount = rs.RecordCount
                        .MoveFirst
                    Do While Not .EOF
                        Pause (5)
                        .MoveNext
                    Loop
                End If
            End With
            
        Set rs = Nothing
        
    End Sub

    Thanks to everyone who contributed! I know your time is valuable and is much appreciated!

    • Marked as answer by rstreets2 Thursday, August 22, 2019 8:24 PM
    Thursday, August 22, 2019 8:24 PM

All replies

  • You need to set the rs object before you can use it.

    Dim rs  As Recordset
    Dim iCount As Integer
        set rs = Me.Recordset
        With rs
            If .RecordCount <> 0 Then
                    .MoveLast
                iCount = rs.RecordCount
                .MoveFirst
            
                Do While Not .EOF
                    .MoveNext
                Loop
            End If
        End With
        
    Set rs = Nothing
    End Sub


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Thursday, August 22, 2019 6:15 PM
  • Hi Bill and thank you for your response!

    I tried your suggestion, however, the loop doesn't really continue to loop...stops on the last record? Any ideas how to get this to continuously loop?

    Added note: Actually jumps to the Last record and never cycles to the First record?


    • Edited by rstreets2 Thursday, August 22, 2019 7:16 PM Added note
    Thursday, August 22, 2019 7:06 PM
  • The code provided starts with the first records and stops at the last. To go the other direction use:

    Dim rs  As Recordset

    Set rs = Me.Recordset
    With rs
        If .RecordCount <> 0 Then
            .MoveLast
            Do While Not .BOF
                .MovePrevious
            Loop
        End If
    End With
        
    Set rs = Nothing

    Having said that, I don't understand what the use of this will be. Access will loop through the records that fast, that you won't be able te see what is shown.


    Groeten, Peter http://access.xps350.com/

    Thursday, August 22, 2019 7:57 PM
  • Hello all -

    Ok so I figured it out :-)  At least what I was trying to accomplish. I added a "Pause" function that slowed down the changing between records....the below works perfectly. I can adjust the time of the pause to leave the record on screen for as long as I want....can also adjust the amount of time to re-trigger the event using the Form_Timer event.

    Works well together:

    Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Err_Pause

        Dim PauseTime As Variant, start As Variant

        PauseTime = NumberOfSeconds
        start = Timer
        Do While Timer < start + PauseTime
        DoEvents
        Loop

    Exit_Pause:
        Exit Function

    Err_Pause:
        MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
        Resume Exit_Pause

    End Function

    ------------------------------------------------------------------------------------------------------

    Private Sub Form_Timer()

    Dim rs  As Recordset
    Dim iCount As Integer
        
        Set rs = Me.Recordset
        
            With rs
                If .RecordCount <> 0 Then
                    iCount = rs.RecordCount
                        .MoveFirst
                    Do While Not .EOF
                        Pause (5)
                        .MoveNext
                    Loop
                End If
            End With
            
        Set rs = Nothing
        
    End Sub

    Thanks to everyone who contributed! I know your time is valuable and is much appreciated!

    • Marked as answer by rstreets2 Thursday, August 22, 2019 8:24 PM
    Thursday, August 22, 2019 8:24 PM
  • Can I ask the purpose of this?

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, August 23, 2019 3:25 PM
  • Hi Bill - What I was trying to accomplish was to have a form basically scroll through all of the records, pausing between changes, to allow users a "sneak-peak" of sort of the data contained in the records.

    In this case, users can see other users "Success Stories" as shown in the screenshot below. The "form" is actually placed on the Welcome Screen Dashboard. User will also have the ability to click on the displayed Story to view in full context. When the users close the full Story Screen, the Dashboard view will begin scrolling through all of the records.

    It actually turned out pretty good. Works well, again allowing me to set the intervals between records changes.

    Friday, August 23, 2019 3:48 PM
  • Lol...next I think I will create a "Pause" button so users can pause the story displayed on the screen ;-)
    Friday, August 23, 2019 4:31 PM
  • Very clever! Thanks for explaining.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, August 23, 2019 7:45 PM
  • It actually turned out pretty good.

    Hi rstreets2,

    For me it would look even better when the "Total Hours" of the Service Categories were formatted with the same number of decimals. But it is my personal preference.

    Imb.

    Friday, August 23, 2019 8:14 PM
  • Hi lmb-hb -

    Thank you for bring that to my attention; the decimal positioning. I had been meaning to circle back around to that and fix. I agree and it has been all taken care of!

    Thanks for the reminder ;-)



    • Edited by rstreets2 Monday, August 26, 2019 4:39 PM Update screenshot
    Monday, August 26, 2019 4:33 PM