Rolling Scoreboard (1 Viewer)

Russp42 · Nov 30, 2017

I have a report which displays live scoring. I have two linked databases. One for input and the other displaying the report on a TV as a 2nd monitor. Data is updated on the report timer. It works fine until the screen is full. I have another function which scrolls down but stops when it reaches the end of the records. What I want is a continuous loop so that the results go back to the start when the end of the file is reached. Can all this be done on one procedure?

Private Sub Report_Timer()

Me.Requery

End Sub

Private Sub Report_Timer()
Dim rptCurReport As Report
Dim rstCurReport As Recordset
Set rptCurReport = Screen.ActiveReport

With rptCurReport
SendKeys "{DOWN}"
End With

End Sub

arnelgp · Nov 30, 2017

you need to determine first the number of records
of the report.
then you compare the current record to this number
and goto top page if they are equal.

BUT...
first thing first.
add an Unbound textbox to the header section.
name it txtCurrentRecord.
remove its label.
set it's visible property to No.

next on Current Event of the report,
set the value of the txtCurrentRecord to
you Primary key on the Report, eg:

Code:

Private Sub Report_Current()
    Me.txtCurrentRecord = Me.ID
End Sub

next, we create a conditional
format to Highlight the record
in the form that has Focus.

on your Report (still in design view),
select all textbox in detail section.
on the Ribbon->Format->Conditional Formatting.
create New rule. select "Expression is"
on the combobox. put [ID]=[txtCurrentRecord].
choose background color (bucket button) that
you like to hightlight the textboxes (i prefer
yellow or blue).

below is the the complete code:

Code:

Private lngRecordCount As Long

Private Sub Report_Open(Cancel As Integer)
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(Replace(Me.RecordSource, ";", ""))
    If Not (rs.BOF And rs.EOF) Then rs.MoveLast: lngRecordCount = rs.RecordCount
    rs.Close
    Set rs = Nothing
End Sub

Private Sub Report_Current()
    Me.txtCurrentRecord = Me.ID
End Sub

Private Sub Report_Timer()
    Call Report_Open(0)
    DoCmd.GoToRecord , , acNext, 1
    If Me.CurrentRecord >= lngRecordCount Then
        ' change to longer interval so
        ' user can see the top page
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        SendKeys "{PGUP}", True
        DoCmd.GoToRecord , , acFirst, 1
    End If
End Sub

Russp42 · Dec 4, 2017

I do not know if this has any bearing or not. Each competition has its own ID and the form used for entering the data has a check box which makes the ID for the Report the current ID.
Your code has produced the following error
Run-time error '3061;
Too few parameters. Expected 1
Debug highlights Set rs = CurrentDb line of code
Cheers
Russ

arnelgp · Dec 4, 2017

what is the recordsource of the report? and what is the unique id in the detail of report?

Russp42 · Dec 4, 2017

The record source of the report is a query called Results which has a field called 'Report' set to true. The ID comes from the CompID field in the Competition Table

Russp42 · Dec 7, 2017

Just a thought. I am trying to achieve a live scoreboard to display the current competition. We only run one competition each day. Instead of having to establish the ID of the competition could we not load the report having todays date. I have bought a VBA coding text book and may hopefully be able to sort some of these things out for myself

Rolling Scoreboard (1 Viewer)

Russp42

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Russp42

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Russp42

Registered User.

Russp42

Registered User.

Similar threads

Users who are viewing this thread