Auto Sort Scores (1 Viewer)

kevnaff

Member
Local time
Today, 23:36
Joined
Mar 25, 2021
Messages
141
Hi All.

I am hosting a quiz and was hoping to have a live scoreboard to make things a bit more interesting. So far I've added a spin button to add and subtract from the scoreboard. I was hoping to have a scoreboard that automatically updates and sorts the scores from highest to lowest.

Is this possible or easy to do?

I found the following code somewhere which said work:

Code:
On Error Resume Next
Application.EnableEvents = False
Me.UsedRange.Sort _
Key1:=[C1:C15], Order1:=xlAscending, Header:=xlYes, Orientation:=xlSortColumns
Application.EnableEvents = True
On Error GoTo 0
End Sub

However it doesn't appear to work for me. This is an example of the scoreboard so far:

1637597780869.png


Thanks for all input in advance.
 

bob fitz

AWF VIP
Local time
Today, 23:36
Joined
May 23, 2011
Messages
4,717
Rather than showing us the score board it might be better to tell/show us what tables you have for tracking the scores of each team.
 

kevnaff

Member
Local time
Today, 23:36
Joined
Mar 25, 2021
Messages
141
Rather than showing us the score board it might be better to tell/show us what tables you have for tracking the scores of each team.

Hi Bob,

Maybe that's where I'm going wrong. I am just storing the team names in Cells B6:B15 and the Points in Cells C6:C15.

Is it best to create a table to store this?
 

bob fitz

AWF VIP
Local time
Today, 23:36
Joined
May 23, 2011
Messages
4,717
Hi Bob,

Maybe that's where I'm going wrong. I am just storing the team names in Cells B6:B15 and the Points in Cells C6:C15.

Is it best to create a table to store this?
It sounds like you are using Excel?

I think Access would be better, but it's a steep learning curve to start with ;)
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:36
Joined
Sep 21, 2011
Messages
14,048
Use the macro recorder to get the basics, then amend to suit.
If the scores are c6:c15, why are you using c1:c15 for a sort?
 

Darrell

Registered User.
Local time
Today, 23:36
Joined
Feb 1, 2001
Messages
299
As Gasman states, your range is wrong to being with, but the syntax looks weird also. You really should end up with something like this

Code:
Range("B5:C16").Sort Key1:=Range("C5"), Order1:=xlDescending, Header:=xlYes
 

kevnaff

Member
Local time
Today, 23:36
Joined
Mar 25, 2021
Messages
141
It sounds like you are using Excel?

I think Access would be better, but it's a steep learning curve to start with ;)

Hi Bob,

I've created an Access database instead. Please see below:

1637664946915.png


I have a table called tblScoreboard with the fields "team name" and "points".

The command buttons are set to + 1 to the field and refresh the form to update the score automatically.

I just was hoping to find out how to reorder the teams depending on their points from highest to lowest.

If anybody has any advice that would be great, thanks.
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:36
Joined
Sep 21, 2011
Messages
14,048
Just use a query sorted by points descending.
Requery any time you add points.
 

kevnaff

Member
Local time
Today, 23:36
Joined
Mar 25, 2021
Messages
141
Just use a query sorted by points descending.
Requery any time you add points.

Thanks Gasman.

As I'm not too familiar with how to apply a query to a form, I used the following code:

Code:
Private Sub cmdPlus1_Click()

[Points] = [Points] + 1


DoCmd.SetOrderBy "Points DESC"

End Sub

Each button from +1 to +5 adds the points and then sorts the order by points descending. It seems to be working OK so hopefully it will stay that way.
 

bob fitz

AWF VIP
Local time
Today, 23:36
Joined
May 23, 2011
Messages
4,717
Thanks Gasman.

As I'm not too familiar with how to apply a query to a form, I used the following code:

Code:
Private Sub cmdPlus1_Click()

[Points] = [Points] + 1


DoCmd.SetOrderBy "Points DESC"

End Sub

Each button from +1 to +5 adds the points and then sorts the order by points descending. It seems to be working OK so hopefully it will stay that way.
Can you post a copy of the db
 

kevnaff

Member
Local time
Today, 23:36
Joined
Mar 25, 2021
Messages
141
Can you post a copy of the db
Certainly...

My next thing I want to develop is a random selector to randomly choose a question category.
 

Attachments

  • Ultimate Quiz.accdb
    840 KB · Views: 185

bob fitz

AWF VIP
Local time
Today, 23:36
Joined
May 23, 2011
Messages
4,717
Certainly...

My next thing I want to develop is a random selector to randomly choose a question category.
As others have suggested, I would bind the form to a query as in the attached db. I have also used an Update query to add points.

If you search the forum I'm sure you'll find many threads on random numbers. you would need to generate a random number that corresponds to one of the PD numbers in the tblBonusRounds table. IMHO that table name is poor. I think tblCats or even tblCategories would be better. Post back if you need more help with the random numbers.
 

Attachments

  • Ultimate Quiz Bob001.accdb
    840 KB · Views: 177

kevnaff

Member
Local time
Today, 23:36
Joined
Mar 25, 2021
Messages
141
As others have suggested, I would bind the form to a query as in the attached db. I have also used an Update query to add points.

If you search the forum I'm sure you'll find many threads on random numbers. you would need to generate a random number that corresponds to one of the PD numbers in the tblBonusRounds table. IMHO that table name is poor. I think tblCats or even tblCategories would be better. Post back if you need more help with the random numbers.
Thanks for the input Bob. I've changed the name to tblCategories.

For the Random Numbers, I've come up with the following makeshift way of doing it:

Created a Query based on tblCategories.
Added a field to generate a random number using the Primary Key, using the following Expr1: Rnd([CategoryID])
I then created a form based on the query so that each time it's loaded, it generates and opens a random record
Added a command button with a macro control, firstly to close the form, and then to open the same form again, this acts as a refresh

Query below:

1637684457911.png


I know it may not be the best way to do this, but it may only be used as a one-time thing, so it doesn't have to be perfect.

Thanks
,
 

bob fitz

AWF VIP
Local time
Today, 23:36
Joined
May 23, 2011
Messages
4,717
Thanks for the input Bob. I've changed the name to tblCategories.

For the Random Numbers, I've come up with the following makeshift way of doing it:

Created a Query based on tblCategories.
Added a field to generate a random number using the Primary Key, using the following Expr1: Rnd([CategoryID])
I then created a form based on the query so that each time it's loaded, it generates and opens a random record
Added a command button with a macro control, firstly to close the form, and then to open the same form again, this acts as a refresh

Query below:

View attachment 96328

I know it may not be the best way to do this, but it may only be used as a one-time thing, so it doesn't have to be perfect.

Thanks
,
I would use some code, a button and a text box on the form. Take a look at the attached db
 

Attachments

  • Ultimate Quiz Bob002.accdb
    516 KB · Views: 174

Users who are viewing this thread

Top Bottom