Duane,
I must have had something that was causing an error. I recopied my code into a new module and it work. I typed in your code and it worked with Options Explicit. I did some reading on "Option Explicit" for future reference and will use it from now on.
Thanks, now back to the book.
Bill
Bill Singer
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Friday, March 12, 2021 10:20 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Bill,
I copied and pasted the same code and it compile as expected. I was surprised the sub name contained "modUtilties..."
I would have a sub named DebugAssertExample which would be saved in a module named modUtilities.
I assume you have Option Explicit at the top of the module in the declarations section. Consider commenting out all of the lines and then attempt to compile.
Consider deleting the module and creating a new one with this sub:
Sub DebugAssertExample()
' Example showing Debug.Assert
Dim lngCount As Long
For lngCount = 1 To 10
Debug.Print lngCount
Debug.Assert lngCount <> 5
Next
End Sub
Then compile and save the module as modUtilities.
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Friday, March 12, 2021 9:15 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
I would suspect I will not use debug.assert but I will use the debug.print.
When I Debug->Compile
This statement turns yellow with an arrow… Sub modUtilities_DebugAssertExample()
This statement turns red… Dim lngCount As Long
When I hit continue I get a compile error box saying Syntax error. I believe it is referring to the … Dim lngCount As Long statement.
I have put this code in a module called DebugPrint just to practice.
Bill
Bill Singer
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Friday, March 12, 2021 9:09 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Hi Bill,
I have never used Debug.Assert and had to look it up at https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/assert-method. According to the documentation the code is expected to stop at 5.
What happens when you select Debug->Compile from the VBA menu?
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Friday, March 12, 2021 8:41 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Duane,
I am working my way through some example in the book and I copied this module into my sample database to watch debug.print and debug.assert. The code does not compile/run. It gets stuck on "Dim lngCount As Long"
It appears as if the code will loop and print numbers until it gets to 5 and them stop. What am I doing wrong?
Sub modUtilities_DebugAssertExample()
' Example showing Debug.Assert
Dim lngCount As Long
For lngCount = 1 To 10
Debug.Print lngCount
Debug.Assert lngCount <> 5
Next
End Sub
Bill
Bill Singer
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Thursday, March 11, 2021 7:58 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Thanks Paul,
Bill, if you uncomment the debug.print strSQL line you will get the SQL statement in the debug window. You can copy and paste this into a new query SQL view window to see the results. If the game was sport where a low score was better you would want to sort the recordset ascending to get the lowest score first.
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Paul Baldy <Pbaldy@gmail.com>
Sent: Wednesday, March 10, 2021 5:57 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
I haven't seen a response to this. That code examines what was passed to the function and determines how to sort the recordset. strSort is used at the end of this:
strSQL = "SELECT gteGteID,gtePoints FROM t_EventParticipants WHERE gteFscID = " & lngFscID & " ORDER BY gtePoints " & strSort
"DESC" will sort descending, unspecified (the "") will sort ascending.
Paul
------ Original Message ------
From: "Bill Singer" <Bill.Singer@at-group.net>
Sent: 3/10/2021 12:55:28 PM
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Duane,
Thank you. I have put this code in the previously mentioned module. It compiles fine. The day has been busy so I have not had enough time to figure out how to trigger the code in the "After update" property of the points field (gtePoints). I will work on that tomorrow.
As I read through the code I can figure out everything (I believe) except one part.
Just for my education, can you explain what the following code does. I have not seen any out put so maybe that would help.
If strGL = "G" Then
strSort = "DESC"
Else
strSort = ""
Bill
Bill Singer
Minnesota
Office 365
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Wednesday, March 10, 2021 9:08 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Hi Bill,
Try this function. I have provided a fair amount of documentation
Public Function IsWinner(lngFscID As Long, lngGteID As Long, Optional strGL As String = "G") As Integer
' lngFscID is the "game" and corresponds to the gteFscID column of t_EventParticipants
' lngGteID is the primary key of t_EventParticipants
' strGL is a string value where "G" means the winner has the Greater/higher score
'Function should return:
' winner = 1
' loser = 2
' tie = 3
' NA = -1 only one result
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSort As String
Dim intFirstScore As Integer
Dim intSecondScore As Integer
Dim lngFirstGteID As Long
Dim lngSecondGteID As Long
If strGL = "G" Then
strSort = "DESC"
Else
strSort = ""
End If
Set db = CurrentDb
strSQL = "SELECT gteGteID,gtePoints FROM t_EventParticipants WHERE gteFscID = " & lngFscID & " ORDER BY gtePoints " & strSort
'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 1 Then 'more than one score recorded
rs.MoveFirst
lngFirstGteID = rs!gteGteID 'grab the first GteID which should be winner or tied
intFirstScore = rs!gtePoints 'grab the first score/result
rs.MoveNext
lngSecondGteID = rs!gteGteID
intSecondScore = rs!gtePoints 'grab the first score/result
Select Case True
Case intSecondScore = intFirstScore 'it's a tie
IsWinner = 3
Case lngGteID = lngFirstGteID 'the winning participant should be the first record
IsWinner = 1
Case Else
IsWinner = 2
End Select
Else
IsWinner = -1 'only one result so winner can't be determined
End If
rs.close
End Function
Regards,
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Wednesday, March 10, 2021 7:47 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Bill,
I now have the book. Learning already but I can tell this will take while to get this to do what I want.
Thanks.
Bill Singer
Minnesota
Office 365
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Bill Mosca
Sent: Tuesday, March 9, 2021 1:37 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Hi Bill
My go-to book for Access is Microsoft Access 2010 Inside Out. This is a well-organized book with everything you need to know about Access development. I know both of the authors personally and can attest to their expertise. Don't let the version scare you away. Little has changed as far as VBA programming goes from 2010.
On Tue, Mar 9, 2021 at 09:01 AM, Bill Singer wrote:Duane,
For a tie gteResID should equal a 3
I understand the concept of saving a variable but getting it to work is my issue.
I am going to have to do some reading. Any good book suggestions?
Bill
Bill Singer
Group Benefits Consultant
Phone: 763-754-8898
Fax: 763-754-8496
Toll Free: 877-902-8898
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Tuesday, March 9, 2021 10:29 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Hi Bill,
What do you want to happen if it's a tie? What values would you place in the gteResID (results) field? You could save the first score to a variable and then use rs.MoveNext to check the next score to see if they are the same.
Regards,
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Tuesday, March 9, 2021 10:21 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Duane,
I finally got to this. I have entered it into a module in a test database. I Compiled the database (found an unrelated error). This compiles fine. I have been picking through it to try to understand what it is doing.
It appears to open record set for the participants of a game where the getFscID (Facility Schedule ID) matched. This would be a record set of two. It sorts by gtePoints (Scores input)
If two records are counted it continues. If one record is counted it stops.
I did realize that periodically we have games that tie game and I failed to mention that. How do I accommodate that? I am guessing that changes everything since it would be hard to sort two records with the same value.
I have not run this yet so I have not seen output.
I appreciate your help.
Bill Singer
Minnesota
Microsoft 365
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Friday, March 5, 2021 2:01 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Hi Bill,
I tried to create a table named t_EventParticipants in my sandbox database but there was already a table with that name 😉
Copy and paste this function into a new blank module and save the module with the name "modCalculations". Make sure you compile the code.
This function accepts the gteFscID, gteGteID, and optionally a "G" for greater or any other letter for less. It will return True for a winner, False for a loser, and Null if there are no records in the table matching gteFscID. You might get false winners if only one score has been entered so
Public Function IsWinner(lngFscID As Long, lngGteID As Long, Optional strGL As String = "G") As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSort As String
If strGL = "G" Then
strSort = "DESC"
Else
strSort = ""
End If
Set db = CurrentDb
strSQL = "SELECT gteGteID FROM t_EventParticipants WHERE gteFscID = " & lngFscID & " ORDER BY gtePoints " & strSort
'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL) 'open recordset of participants in the game
If rs.RecordCount > 1 Then 'more than one score recorded
rs.MoveFirst
IsWinner = rs(0) = lngGteID 'first record will be winner since the recordset is sorted by points
Else
IsWinner = Null 'either no participants or only one recorded
End If
End Function
You can call this function in the after-update event of the score entries. You may need to save the record prior to running the function.
If IsWinner(gteFSCID, gteGteID) Then
gteResID = 1 'winner
Else
gteResID = 2 'loser
End If
Duane
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Friday, March 5, 2021 1:17 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
Paul,
I like your idea and Duane agrees, which is always good.
I understand exactly what you are saying, which is good and bad. It is good that I understand but it is bad that I am not so good at passing variables and writing functions. I am good at tables, forms, queries, reports, but functions are difficult. I will try to do some reading and figure out how to get started. It is going to be painful. Any tips you can provide would be greatly appreciated.
The first question is, where do I put the function? Do I put it in the "After update" property of the score field, (gtePoints)?
I told you it would be painful.
Thanks,
Bill
Bill Singer
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Paul Baldy
Sent: Thursday, March 4, 2021 1:58 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
My first thought is a function to which you pass gteFscID. It would open a recordset sorted by score, filtered by gteFscID. Presuming there can be no ties, the first record is the winner and the second record is the loser (presuming the sort is by score descending). You can loop the recordset and assign those values. I'd have code to make sure there are 2 records and both have valid scores entered in case the function is called before those conditions are met.
Paul
------ Original Message ------
From: "Bill Singer" <Bill.Singer@at-group.net>
Sent: 3/4/2021 11:45:54 AM
Subject: [MSAccessProfessionals] Winning team auto calculate
I have a sports tracking database. I track game and practice schedules, scores, standing etc. The database has been working pretty well for a few years with the exception of the human element, which is me. For each game I have to enter the score of each team and then indicate if each team was the winner or loser, by entering a 1 or 2. Periodically I will enter the scores correctly by I will enter the results wrong by putting a 1 in place of a 2 or the other way around. This creates grief for me an frustration for those coaches that see the records as incorrect. I was wondering if I could get some help in trying to have the database calculate the winner or loser automatically.
I have a table that records the scores and game results. Each game gets two records, one for the home team and one for the visitor team. All the records are viewed on a continuous form.
The table is t_EventParticipants
The fields in the table are:
gteGteID – Key field
gteFscID – This is the ID for the Facility schedule, there one ID number per game, which would mean there two records with the same ID, one for Home one for Visitor.
gteTnaID – This is the Key field that referees to the team name
gteDesID – This is the key field that referees to the Home or Visitor designation
gtePoints – This is the field where the points are recorded.
gteResID – this is a Key field where a 1 or 2 is entered to designate winner or loser.
What I am hoping to do is to get the gteResID filed to automatically calculate but the problem is that I am comparing the scores in the gtePoints field from two records, which are the two records with the same gteFscID number one for the Home team and one for the Visitors team. If it was comparing field in the same record I think I could figure it out.
Any help you could provide would be wonderful.
Bill Singer
Minnesota
Windows 10
Microsoft 365
--Regards,
Bill Mosca, Founder - MS_Access_Professionals
Microsoft Office Access MVP 2010-2016
My nothing-to-do-with-Access blog
Tidak ada komentar:
Posting Komentar