Rabu, 31 Maret 2021

Re: [MSAccessProfessionals] Winning team auto calculate

I would add a new record to the T_Results table for NA or whatever. Set the ID value to -1.

Duane


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Tuesday, March 30, 2021 11:44 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate
 

Duane,

I have put this code in the after update event of the Score field.   I am reposting the code below because there was a typo and one other error based on a mistake I previously made.

 

The code compiles and runs now I believe.  It gets stuck when is tries to update the gteResID field.  It says "You cannon add or change a record because a related record is needed in t_Results.    T_Results is the table which is linked to the gteResID field.  It is linked by the Key field.  The key field has a 1, 2, or 3 in it for the 3 possible results.  The trouble is that when I try to update the first record I believe it is trying to put a -1 in the gteResID field…and there is no -1 key field.   

 

So the two options I am coming up with are

  1. is to create another field in the t_Results table and input the 1,2,and3 and also include a 1- and then link the filed and the other table to that new field
  2. To change the code in the original module to return another number such as 10   and the add more records to the t_Results table until I get to 10.  This should allow the gteResID field to update since there is a related record in t_Results.

 

Do you have a suggestion on what change to make?

 

 

CODE BELOW

 

Private Sub gtePoints_AfterUpdate()

    ' 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

    Me.Dirty = False

    Me.gteResID = IsWinner(Me.fscFscID, Me.gteGteID, "G")

End Sub

 

 

 

 

Thanks for your help.

Bill

Minnesota

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Saturday, March 27, 2021 4:44 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

Hi Bill,

 

You would add code to the after update event of the score control. I copied in the comments from the function for documentation.

 

Private Sub gtePoints_AfterUpdate()

    ' 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

    Me.Dirty = False

    Me.getResID = IsWinner(Me.gteFscID, gteGteID, "G")

End Sub

 

This assumes your score is entered into a text box with the name gtePoints.

 

The issue with this will be caused by entering first one score which will return -1 and then updating this when the second score is added. Once you have this in place I can explain how to update the previously entered record to something other than -1.

 

Duane


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Saturday, March 27, 2021 1:20 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

Duane,

The reason I used a macro is because I could not figure out to run the function from the "after update" property of the Score field with using a sub.  I guess I could move all of the code to the after update property.  I try a few things and see if I can get it to populate the correct field.

 

Thanks for your help.  I will let you know how it goes.

Bill

Minnesota

Office 365

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Saturday, March 27, 2021 10:54 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

I seem to remember the code will return an integer rather than Boolean so I would use Select Case rather than If. 

 

I'm not sure how a macro got in scope of any reply. Please post the entire VBA Sub when asking for clarification. 

 

Duane

Sent from my mobile

 

On Mar 26, 2021, at 11:44 AM, Bill Singer <Bill.Singer@at-group.net> wrote:



Duane,

Partial good news.  The function works in the Immediate window.  Here are my tests with the form open and changing the scores.

?IsWinner(23946,42442,"G")

2

 1  

 1

 3

 2

 1

They were all correct.

 

Here is the rest of the story.  Here is what I did to get the function to not give me an error "after update" of the points field.

I am using the RunCode function in a Macro.

If I put this exact information in     If IsWinner(Me.gteFscID , Me.gteGteID, "G") Then      I get a message that says "The object doesn't contain the Automation object 'If.' "         I am not sure why the  If-Then   is included in this but I tried it.

If I put in this exact information     IsWinner(Me.gteFscID , Me.gteGteID, "G")            I get a message that says "The object doesn't contain the Automation object 'Me' "

If I put in this exact information    IsWinner(gteFscID , gteGteID, "G")              I get a message that says "The object doesn't contain the Automation object gteFscID' "

 

It looks like I had an error.  When I looked at my form the reference to the "game" (the two records that have the same game number)  on the form is the fscFscID field  so I changed gteFscID to fscFscID in the code to

IsWinner(fscFscID , gteGteID, "G")              and when I go to the form and input scores and tab to the next field, I get no error, but I get nothing so I do not know if it is working. 

 

 

 

I am guessing in the results field  I will have to and IIF statement based on the results of the Function IsWinner.

 

The dots are connecting slowly.

 

Thanks,

 

Bill

 

Bill Singer

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Friday, March 26, 2021 9:02 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

The function is set up like:

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

 

 

To call this from the form in an after update you would need to reference the controls on the form as arguments;

 

If IsWinner(Me.gteFscID , Me.gteGteID, "G") Then

 

Typically these arguments are values in either fields or controls in the query or on a form/report. For instance if you have a control in a report footer with a control source of:

=Sum([Your Field Name]) 

The argument is a field in the report's record source. The same is true for using any function that requires or accepts arguments. 

 

You can test any of your functions in the immediate window (press Ctrl+G). Then enter something like:

 

? Format(Date(),"yyyymm")

When you press enter you will see:

202103

 

Days until the end of the year:

? DateDiff("d",Date(),#12/31/2021#) 

280

 

I used the immediate window to test and debug the IsWinner() function by typing in actual values from the tables gteFscID and gteGteID.

 

Duane

 


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Friday, March 26, 2021 8:20 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

 

 

Bill

Duane,

Back to the IsWinning Function.

I have been doing a few tests.  I have written a few functions.  I have run those functions from a field on a form using the "After update" property. So far so good.  My problem is that all my functions do not have arguments such as the function you wrote.  After multiple hours of trying to figure out how to pass an argument to a user defined function via a macro all I have is less hair on my head.

 

I used the macro "RunCode"  I put in   IsWinning    and the left had  (    comes up.     If I just close the parameters with the   )   I get an error message .. incorrect amount of parameters.

 

I know I have to pass the IngFscID and the IngGteID to the function but I can't figure out how to do that.     The argument strGL appears to = G  so I am guessing I am not going to have to pass that optional argument.

 

Thanks for your patience and help.

 

Bill Singer

Access 365

 

 

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

Bill.Singer@at-group.net

P1websiteLogo2

 

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

 

Selasa, 30 Maret 2021

Re: [MSAccessProfessionals] Winning team auto calculate

Duane,

I have put this code in the after update event of the Score field.   I am reposting the code below because there was a typo and one other error based on a mistake I previously made.

 

The code compiles and runs now I believe.  It gets stuck when is tries to update the gteResID field.  It says "You cannon add or change a record because a related record is needed in t_Results.    T_Results is the table which is linked to the gteResID field.  It is linked by the Key field.  The key field has a 1, 2, or 3 in it for the 3 possible results.  The trouble is that when I try to update the first record I believe it is trying to put a -1 in the gteResID field…and there is no -1 key field.   

 

So the two options I am coming up with are

  1. is to create another field in the t_Results table and input the 1,2,and3 and also include a 1- and then link the filed and the other table to that new field
  2. To change the code in the original module to return another number such as 10   and the add more records to the t_Results table until I get to 10.  This should allow the gteResID field to update since there is a related record in t_Results.

 

Do you have a suggestion on what change to make?

 

 

CODE BELOW

 

Private Sub gtePoints_AfterUpdate()

    ' 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

    Me.Dirty = False

    Me.gteResID = IsWinner(Me.fscFscID, Me.gteGteID, "G")

End Sub

 

 

 

 

Thanks for your help.

Bill

Minnesota

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Saturday, March 27, 2021 4:44 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

Hi Bill,

 

You would add code to the after update event of the score control. I copied in the comments from the function for documentation.

 

Private Sub gtePoints_AfterUpdate()

    ' 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

    Me.Dirty = False

    Me.getResID = IsWinner(Me.gteFscID, gteGteID, "G")

End Sub

 

This assumes your score is entered into a text box with the name gtePoints.

 

The issue with this will be caused by entering first one score which will return -1 and then updating this when the second score is added. Once you have this in place I can explain how to update the previously entered record to something other than -1.

 

Duane


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Saturday, March 27, 2021 1:20 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

Duane,

The reason I used a macro is because I could not figure out to run the function from the "after update" property of the Score field with using a sub.  I guess I could move all of the code to the after update property.  I try a few things and see if I can get it to populate the correct field.

 

Thanks for your help.  I will let you know how it goes.

Bill

Minnesota

Office 365

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Saturday, March 27, 2021 10:54 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

I seem to remember the code will return an integer rather than Boolean so I would use Select Case rather than If. 

 

I'm not sure how a macro got in scope of any reply. Please post the entire VBA Sub when asking for clarification. 

 

Duane

Sent from my mobile

 

On Mar 26, 2021, at 11:44 AM, Bill Singer <Bill.Singer@at-group.net> wrote:



Duane,

Partial good news.  The function works in the Immediate window.  Here are my tests with the form open and changing the scores.

?IsWinner(23946,42442,"G")

2

 1  

 1

 3

 2

 1

They were all correct.

 

Here is the rest of the story.  Here is what I did to get the function to not give me an error "after update" of the points field.

I am using the RunCode function in a Macro.

If I put this exact information in     If IsWinner(Me.gteFscID , Me.gteGteID, "G") Then      I get a message that says "The object doesn't contain the Automation object 'If.' "         I am not sure why the  If-Then   is included in this but I tried it.

If I put in this exact information     IsWinner(Me.gteFscID , Me.gteGteID, "G")            I get a message that says "The object doesn't contain the Automation object 'Me' "

If I put in this exact information    IsWinner(gteFscID , gteGteID, "G")              I get a message that says "The object doesn't contain the Automation object gteFscID' "

 

It looks like I had an error.  When I looked at my form the reference to the "game" (the two records that have the same game number)  on the form is the fscFscID field  so I changed gteFscID to fscFscID in the code to

IsWinner(fscFscID , gteGteID, "G")              and when I go to the form and input scores and tab to the next field, I get no error, but I get nothing so I do not know if it is working. 

 

 

 

I am guessing in the results field  I will have to and IIF statement based on the results of the Function IsWinner.

 

The dots are connecting slowly.

 

Thanks,

 

Bill

 

Bill Singer

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Friday, March 26, 2021 9:02 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

The function is set up like:

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

 

 

To call this from the form in an after update you would need to reference the controls on the form as arguments;

 

If IsWinner(Me.gteFscID , Me.gteGteID, "G") Then

 

Typically these arguments are values in either fields or controls in the query or on a form/report. For instance if you have a control in a report footer with a control source of:

=Sum([Your Field Name]) 

The argument is a field in the report's record source. The same is true for using any function that requires or accepts arguments. 

 

You can test any of your functions in the immediate window (press Ctrl+G). Then enter something like:

 

? Format(Date(),"yyyymm")

When you press enter you will see:

202103

 

Days until the end of the year:

? DateDiff("d",Date(),#12/31/2021#) 

280

 

I used the immediate window to test and debug the IsWinner() function by typing in actual values from the tables gteFscID and gteGteID.

 

Duane

 


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Bill Singer <Bill.Singer@at-group.net>
Sent: Friday, March 26, 2021 8:20 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Winning team auto calculate

 

 

 

Bill

Duane,

Back to the IsWinning Function.

I have been doing a few tests.  I have written a few functions.  I have run those functions from a field on a form using the "After update" property. So far so good.  My problem is that all my functions do not have arguments such as the function you wrote.  After multiple hours of trying to figure out how to pass an argument to a user defined function via a macro all I have is less hair on my head.

 

I used the macro "RunCode"  I put in   IsWinning    and the left had  (    comes up.     If I just close the parameters with the   )   I get an error message .. incorrect amount of parameters.

 

I know I have to pass the IngFscID and the IngGteID to the function but I can't figure out how to do that.     The argument strGL appears to = G  so I am guessing I am not going to have to pass that optional argument.

 

Thanks for your patience and help.

 

Bill Singer

Access 365

 

 

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

Bill.Singer@at-group.net

P1websiteLogo2

 

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