Starting Handicaps

In the Handicap UDF, there’s a call to another UDF: GetStartingHC. Every golfer has a handicap to start and it’s stored in TblPlayers. For most golfers, it’s a carry over from last season. For new members, it can be their USGA handicap. When a golfer doesn’t have three scores, the starting handicap is used as one of the scores.

Public Function GetStartingHC(ByVal lPlyr As Long) As Long
   
    Dim rsPlayers As ADODB.Recordset
   
    Const sBEGHC As String = “BegHC”
   
    If gadoCon Is Nothing Then
        InitGlobals
    End If
   
    Set rsPlayers = New ADODB.Recordset
    rsPlayers.Open “TblPlayers”, gadoCon, adOpenDynamic
   
    rsPlayers.MoveFirst
    rsPlayers.Find “Player = “ & lPlyr
   
    If Not rsPlayers.EOF Then
        GetStartingHC = CLng((rsPlayers.Fields(sBEGHC) / 0.8) + 36)
    End If
   
End Function

Nothing too fancy, just using the Find method to get to the proper record quickly. This recordset is definitely a candidate for moving global so it doesn’t need to be created and destroyed every time the function is called. I so skillfully created a constant to refer to the field in the recordset, but I leave 0.8 and36 as literals. Shameful. If this program will ever be useful outside of my league, those are going to be user settings. I need to make them user settings right now instead of being so lazy.

Posted in Uncategorized

One thought on “Starting Handicaps

  1. DK: “I so skillfully created a constant to refer to the field in the recordset, but I leave 0.8 and36 as literals. Shameful. If this program will ever be useful outside of my league, those are going to be user settings. I need to make them user settings right now instead of being so lazy.”

    If you think of the tables and their columns in your database as being ‘classes’ and their columns as ‘members’ for your front end application use, you’ll see it is reasonable to refer to them as literals (you don’t use CallByName too much, do you :) ?). What might start out as a maintenance-assisting exercise (i.e. if the column name ever changed you’d only need to amend one place in client code) may end up with code such as:

    Const TABLE_PLAYERS_NAME As String = “TblPlayers”
    Const TABLE_PLAYERS_COL_PLAYER_ID As String = “Player”
    Const TABLE_PLAYERS_COL_BEGHC_NAME As String = “BegHC”

    Then you realize you could organize this in a collection class of tables each with dependent collection class of columns, just to store meta data element names. Then you realize you can query the database’s data dictionary, schema system tables or equivalent to dynamically populate your schema objects. Then you realize you have an application that seemingly has no clue about its database until run-time.

    That is not the way to create tiered architecture. Changing the underlying database structure will always have non-trivial implications for a front end application.

    My take on it is that referring to members as literals such as “SELECT Player, BegHC FROM TblPlayers” and Fields(“BegHC”) is entirely acceptable. I think putting 0.8 and 36 into user settings is correct. I’d also suggest changing the global variables for your connection and recordset objects into respective Property Get’s e.g. would be able to test the state etc of the underlying ADODB object before returning a reference to it.

    Jamie.

    –


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.