Timer, Record Selection, Loop, If statement ... Suggestions??? Hel

[Hammer]

I was trying to figure out how to open a form (that is in Continuous Forms
View) called 'frmDisplay' and scroll through the records using code. I was
hoping the visual effect would be a slow vertical scrolling of the records.
Once I get to the bottom of the records I want the form to close.

As a solution I've put the following code in the on On Timer property of a
form. When the form opens the code will step through the records starting at
the first record then when it hits the last record, an error occurs and the
form closes. I know this is a bad solution but it works and provides the
visual effect I want, but I suspect I might have to change it. (Varying the
Time Interval changes the speed of the scrolling)

Private Sub Record_Scrolling()
On Error GoTo LASTRECORD

If Not LASTRECORD Then
DoCmd.GoToRecord acDataForm, Me.Name, acNext

Else
DoCmd.Close
End If

Exit Sub

LASTRECORD:
DoCmd.Close

End Sub

There are 5 different groups (1 through 5) in the data I would like
displayed. To get the form to display just group 1 data, I use this code, it
filters for a specific group.

DoCmd.OpenForm "frmDisplay", , ,"Group = '1'"

Then the form open and scrolls through the data and closes when it reaches
the last record.

What I want to have happen is the form “frmDisplay†open and display the
Data for Group 1, then once it has scrolled through all the records and
closed I’d like it to open up again and display Group 2, etc … and go back to
group 1 after it has displayed group 5. Then continue looping until I stop it.

Can I use VBA to step through the records, rather than the On Timer
function? The groups will all have different numbers of records. Should I
open the form, count the records, scroll through them, then close the form?
I’m not sure how to proceed towards a solution.

 

[Dirk Goldgar]

I was trying to figure out how to open a form (that is in Continuous
Forms View) called 'frmDisplay' and scroll through the records using
code. I was hoping the visual effect would be a slow vertical
scrolling of the records. Once I get to the bottom of the records I
want the form to close.

As a solution I've put the following code in the on On Timer property
of a form. When the form opens the code will step through the records
starting at the first record then when it hits the last record, an
error occurs and the form closes. I know this is a bad solution but
it works and provides the visual effect I want, but I suspect I might
have to change it. (Varying the Time Interval changes the speed of
the scrolling)

Private Sub Record_Scrolling()
On Error GoTo LASTRECORD

If Not LASTRECORD Then
DoCmd.GoToRecord acDataForm, Me.Name, acNext

Else
DoCmd.Close
End If

Exit Sub

LASTRECORD:
DoCmd.Close

End Sub

There are 5 different groups (1 through 5) in the data I would like
displayed. To get the form to display just group 1 data, I use this
code, it filters for a specific group.

DoCmd.OpenForm "frmDisplay", , ,"Group = '1'"

Then the form open and scrolls through the data and closes when it
reaches the last record.

What I want to have happen is the form "frmDisplay" open and display
the
Data for Group 1, then once it has scrolled through all the records
and closed I'd like it to open up again and display Group 2, etc .
and go back to group 1 after it has displayed group 5. Then continue
looping until I stop it.

Can I use VBA to step through the records, rather than the On Timer
function? The groups will all have different numbers of records.
Should I open the form, count the records, scroll through them, then
close the form? I'm not sure how to proceed towards a solution.

You need the Timer event, but you may be able to get the behavior you
want with code like this in the form's module:

'----- start of code -----
Option Compare Database
Option Explicit

Dim mintGroup As Integer

Private Sub BumpFilter()

mintGroup = mintGroup + 1
If mintGroup > 5 Then
mintGroup = 1
End If

Me.Filter = "Group = '" & mintGroup & "'"
Me.FilterOn = True

End Sub


Private Sub Form_Open(Cancel As Integer)

mintGroup = 0

BumpFilter

End Sub


Private Sub Form_Timer()

With Me.Recordset
If .EOF Then
BumpFilter
Else
.MoveNext
End If
End With

End Sub

'----- end of code -----

 

[Hammer]

Dirk,

Thank you for your help. This 'seems' to work.

The strange think is, the timer pace seems to work for every other group ie.
One group it runs/bumps at the correct pace, then the next group is
runs/bumps to quickly, then the next group it works great, then too quickly
etc...

Any thoughts on this?

Pete

 

[Dirk Goldgar]

Dirk,

Thank you for your help. This 'seems' to work.

The strange think is, the timer pace seems to work for every other
group ie. One group it runs/bumps at the correct pace, then the next
group is runs/bumps to quickly, then the next group it works great,
then too quickly etc...

Any thoughts on this?

I'm not surre, because I'm not sure exactly what you're describing. You
might try turning off the timer while you switch groups:

'----- start of revised code -----
Private Sub BumpFilter()

Dim lngInterval As Long

lngInterval = Me.TimerInterval ' capture current setting
Me.TimerInterval = 0 ' disable timer

mintGroup = mintGroup + 1
If mintGroup > 5 Then
mintGroup = 1
End If

Me.Filter = "Group = '" & mintGroup & "'"
Me.FilterOn = True

Me.TimerInterval = lngInterval ' restore timer

End Sub

'----- end of revised code -----