Using Cutom Functions in a Query (form recordset)

[Salty Mariner]

I am continuing to move along with my voyage planning DB that computes course distance and time for given Latitude/Longitude waypoints. I have a working prototype that uses calculated controls and basically does exactly what it is supposed to do. Calculations are slow as expected but it is basically a proof of concept at this point.



I am now trying to encapsulate some of the functionality in the calculated controls to custom functions. My intention is to try and run these functions inside of the query which forms the recordset for my subform. I created the functions , placed them in their own module and made them Public. However when I try to run then since they reference my form and subform I get a message that I am attempting to reference an object which is closed or does not exist. My interpretation is that the query is running before the forms have opened, hence the error.

How else can I run these? I was wanting calculated distance and time on leg to be part of my forms recordset so that i could use domain aggregate functions. to summarize both for the form and for future reports. I assume that I may have do some much more complex recordset manipulation in code stretching my skill levels to the max or likely beyond

Here is what the "functioning" form looks like with the calculated controls and below that are my functions. I created copies of my form and subform to experiment on.

Option Compare Database
Option Explicit


'------------------------------------------------------------
' NAME      : GetNextLat()
' PURPOSE   : Gets the next latitude record (if it exists)
' REQUIRES  : A Longitude String
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE      : April 2024
'-------------------------------------------------------------
Public Function GetNextLat()
Dim strNextLat As String
Dim rst As DAO.Recordset
Dim intLegNo As Integer


    Set rst = Forms!frmVoyagePlan!subVoyPlanLegs.Form.RecordsetClose
    intLegNo = Forms!frmVoyagePlan!subVoyPlanLegs.Form!txtLegNo.Value
    
    With rst
        If Not .EOF Then
            .FindFirst ("LegNo = " & intLegNo + 1)
            If Not .NoMatch Then strNextLat = ![Latitude]
        End If
    End With
    
    GetNextLat = strNextLat
    
    rst.Close
    Set rst = Nothing
End Function
'------------------------------------------------------------
' NAME      : GetNextLon()
' PURPOSE   : Gets the next longitude record (if it exists)
' REQUIRES  : A Longitude String
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE      : April 2024
'-------------------------------------------------------------
Public Function GetNextLon()
Dim strNextLon As String
Dim rst As DAO.Recordset
Dim intLegNo As Integer


    Set rst = Forms!frmVoyagePlan!subVoyPlanLegs.Form.RecordsetClone
    intLegNo = Forms!frmVoyagePlan!subVoyPlanLegs.Form!txtLegNo.Value
    
     With rst
        If Not .EOF Then
            .FindFirst ("LegNo = " & intLegNo + 1)
            If Not .NoMatch Then strNextLon = ![Longitude]
        End If
    End With
    
    GetNextLon = strNextLon
    
    rst.Close
    Set rst = Nothing
End Function
'------------------------------------------------------------
' NAME      : GetCourse()
' PURPOSE   : Calculates and returns the Mercator course
' REQUIRES  : Lat1, Lon1, Lat2, Lon2
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE      : April 2024
'-------------------------------------------------------------
Public Function GetCourse()


Dim dblLat1 As Double
Dim dblLon1 As Double
Dim dblLat2 As Double
Dim dblLon2 As Double


    ' Assign Latitude1 and longitude1 to variables as decimal degrees
    dblLat1 = LatLonToDegs(Forms!frmVoyagePlan!subVoyPlanLegs.Form!txtLatitude)
    dblLon1 = LatLonToDegs(Forms!frmVoyagePlan!subVoyPlanLegs.Form!txtLongitude)
    
    ' Assign Latitude2 and longitude2 to variables as decimal degrees
    dblLat2 = LatLonToDegs(GetNextLat())
    dblLon2 = LatLonToDegs(GetNextLon())
    
    ' If Empty strings are returned we are past the last Waypoint so return an empty string
    If GetNextLat() = "" And GetNextLon() = "" Then
        GetCourse = ""
        Exit Function
    End If
    
    ' Return the course
    GetCourse = MercatorCse(dblLat1, dblLon1, dblLat2, dblLon2)
    
End Function
'------------------------------------------------------------
' NAME      : GetDistance()
' PURPOSE   : Calculates and returns the Mercator course
' REQUIRES  : Lat1, Lon1, Lat2, Lon2, Cse
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE      : April 2024
'-------------------------------------------------------------
Public Function GetDistance()
Dim dblLat1 As Double
Dim dblLon1 As Double
Dim dblLat2 As Double
Dim dblLon2 As Double


    ' Assign Latitude1 and longitude1 to variables as decimal degrees
    dblLat1 = LatLonToDegs(Forms!frmVoyagePlan!subVoyPlanLegs.Form!txtLatitude)
    dblLon1 = LatLonToDegs(Forms!frmVoyagePlan!subVoyPlanLegs.Form!txtLongitude)
    
    ' Assign Latitude2 and longitude2 to variables as decimal degrees
    dblLat2 = LatLonToDegs(GetNextLat())
    dblLon2 = LatLonToDegs(GetNextLon())
    
    ' If Empty strings are returned we are past the last Waypoint so return an empty string
    If GetNextLat() = "" And GetNextLon() = "" Then
        GetCourse = ""
        Exit Function
    End If


    ' Return the Distance
    GetDistance = MercatorDist(dblLat1, dblLon1, dblLat2, dblLon2, MercatorCse(dblLat1, dblLon1, dblLat2, dblLon2))


End Function

[June7]

Why do you want to call these calculations in query used as form Recordsource?

You show a typo in the first function: RecordsetClose should be RecordsetClone.

Pass the LegNo via an argument.

Instead of RecordsetClone, open a normal recordset with WHERE clause to return just the one desired record. Or consider using DLookup() in the function instead of any recordset.

Public Function GetNextL(intL, strL)
GetNextL = DLookup(strL, "tablename", "LegNo=" & intL + 1)
End Function

Call function in query:

SELECT *, GetNextL(Lat, "Lat") AS NextLat, GetNextL(Lon, "Lon") AS NextLon FROM tablename;

Do something similar in GetCourse and GetDistance. Pass Lat and Lon as function arguments.

[madpiet]

If I understand you right your "race path" consists of a series of (longitude, latitude) points which form a line or polygon, and you're just trying to determine the length of some/all of the legs and sum them. You basically add the (long, lat) points to your recordset in your subform, and then I suppose you need to string the points together using a recordset and get the distance between each point and the previous point. (oh, my KINGDOM for LAG()!!!) and then you sum up the distances between the points. Well, I supposed you could use a wonky join like RecordID = DMAX("Table", [RecordID])-1 kind of thing (See Allen Browne's website). allenbrowne.com for all that.

(Personally, this is infinitely easier in SQL Server. It can do this stuff out of the box.) Oh right... for each record, you get the distance between that point and the previous one. Then you add that calculation to your subform (even if you don't show it) and then you sum it and show it in your main form.

[madpiet]

Hmm... what type does MercatorDist() return? Double? Then you'd make your function return the same type. Also, since the Starting/ending points of a race are static, you'd assign them once. (I'd be lazy and just use an update query to do it. I'd use a recordset if someone had a gun or if I didn't have a choice).

[Salty Mariner]

MercatorCse and MercatorDist return Double. I also have functions for converting and validating Latitude and Longitude strings to (and from) decimal degrees with West and South as negative numbers. I'll post these functions below since your interested. I wrote them over 20 years ago along with the help of a "VB for Dummies" book when I was just learning VBA. I'm sure my code could likely be far more efficient but they are well tested and work as intended. I originally wrote them to use in Excel to replace some very cumbersome Excel worksheet formulas. I reworked them slightly to use in Access mostly by adding a Tempvar to cancel updates when string validation fails. The reason I did it this way was so that users would not get confused by having to deal with decimal degrees as most mariners want to use their geographic coordinates in the same format as displayed on a GPS. The Mercator Sailing Formulas I adapted from "Bowditch - The American Practical Navigator" I took that methodology and tried to translate it into VBA.

Option Compare Database
Option Explicit
Public Const Pi As Double = 3.14159265358979
Public Function DegreesToRadians(dblDegrees As Double)
    DegreesToRadians = dblDegrees * (Pi / 180)
End Function
Public Function RadiansToDegrees(dblRadians As Double)
    RadiansToDegrees = dblRadians * (180 / Pi)
End Function




'------------------------------------------------------------
' NAME: ValidateLatitude()
' PORPOSE: Validates Latititude entered as a string to ensure
' it is properly structured for use in navigation calculations
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
'-------------------------------------------------------------
Public Sub ValidateLatitude(strLatValue As String)
On Error GoTo Err_Handler


Dim strLatDegrees As String
Dim strLatMinutes As String
Dim strLatDirection As String
Dim strSeparator As String
Dim dblLatDeg As Double
Dim arrDirection As Variant
Dim intNumCount As Integer
Dim i As Integer
Dim booFoundMatch As Boolean
Dim strMsg As String
Dim booNonNumeric As Boolean


    ' Check for Null value, if so Exit Sub
    If strLatValue = "" Then
        TempVars("CancelUpdate") = "True"
        Exit Sub
    End If
    
    ' Set CancelUpdate to False as default
    TempVars("CancelUpdate") = "False"
    
    'Set nonNumeric to False as default
    booNonNumeric = False
    
    ' Fill Array
    arrDirection = Array("N", "S", "E", "W")
    
    ' Initialize Counter
    intNumCount = 0






    ' Get rid of leading or trailing blanks
    strLatValue = Trim(strLatValue)
    ' assign value to direction variable
    strLatDirection = Right(strLatValue, 1)




'''''' Get values for degrees and minutes''''''
    
    ' searches for "-" or " " (space or dash)
    If InStr(strLatValue, "-") > 0 Then
        strSeparator = "-"
    ElseIf InStr(strLatValue, " ") > 0 Then
        strSeparator = " "
    Else
        'Warn user and cancel Update
        MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbInformation, "Missing separator"
        TempVars("CancelUpdate") = "True"  ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If
     
    ' Assign Latitude degrees and minutes to variables
    strLatDegrees = Left(strLatValue, InStr(strLatValue, strSeparator) - 1)
    
    ' Check to see if user entered a non-numeric character in place of a number
    If Not IsNumeric(strLatDegrees) Then booNonNumeric = True
    
    strLatMinutes = Left(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator)), _
        Len(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator))))
        
    ' strip direction
    strLatMinutes = Left(strLatMinutes, Len(strLatMinutes) - 1)
    
    ' Check to see if user entered a non-numeric character in place of a number
    If Not IsNumeric(strLatMinutes) Then booNonNumeric = True
    
    ' Warn user, cancel update, and exit
    If booNonNumeric = True Then
        MsgBox "You enetered a non-numeric charactor in place of a number" & vbCrLf & "Please Correct", vbOKOnly + vbExclamation, "Invalid entry"
        TempVars("CancelUpdate") = "True"
        Exit Sub
    End If
    




' ------------------------------------------------------
' ---------------VALIDATION FOR LATITUDES---------------
' ------------------------------------------------------


' Set Initial Match Value and Check for direction sign


    booFoundMatch = True


    For i = 0 To 1  ' We only need to check the 1st two array elements ("N" or "S")
            If strLatDirection = arrDirection(i) Then
                booFoundMatch = True
                GoTo NEXTCHECK
            Else
                booFoundMatch = False
            End If
    Next i


' Display message if no direction sign.


    If booFoundMatch = False Then ' Build a message
        strMsg = "The latitude you entered must have" & vbCrLf
        strMsg = strMsg & "a valid N/S direction indicator "
        strMsg = strMsg & vbCrLf & vbCrLf & "Please try again!"
    ' Warn user and cancel update
        MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
        End If


NEXTCHECK:


    ' Validate Degrees
    If Val(strLatDegrees) > 90 Then
        '     Build a message
        strMsg = "The latitude can not exceed 90 degrees "
        strMsg = strMsg & vbCrLf & "Please try again!"
        
        ' Warn user and Cancel Update
        MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If
    
    
    ' Validate Minutes
    If Val(strLatMinutes) > 59.9999 Then
        '     Build a message
        strMsg = "The latitude minutes cannot exceed 59.999"
        strMsg = strMsg & vbCrLf & "Please try again!"
        
        ' Warn user and Cancel Update
        MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If
    
    ' Finally we combine degrees and minutes and
    ' check that the total does not exceed 90
    dblLatDeg = Format(CDbl((Val(strLatDegrees) + Val(strLatMinutes) / 60)), "00.0000")
    
    If dblLatDeg > 90 Then
     '     Build a message
        strMsg = "The latitude degrees and minuntes" & vbCrLf
        strMsg = strMsg & "can not exceed a total of 90 degrees"
        strMsg = strMsg & vbCrLf & "Please try again!"
    ' Warn user
        MsgBox strMsg, vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"  ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If
    
    
Exit_Handler:
    Exit Sub


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "ValidateLatitude"
        Resume Exit_Handler
    
End Sub


'------------------------------------------------------------
' NAME: ValidateLongitude()
' PORPOSE: Validates Longitude entered as a string to ensure
' it is properly structured for use in navigation calculations
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
'-------------------------------------------------------------
Public Sub ValidateLongitude(strLonValue As String)
On Error GoTo Err_Handler


Dim strLonDegrees As String
Dim strLonMinutes As String
Dim strLonDirection As String
Dim strSeparator As String
Dim dblLonDeg As Double
Dim arrDirection As Variant
Dim intNumCount As Integer
Dim i As Integer
Dim booFoundMatch As Boolean
Dim strMsg As String
Dim f As String
Dim booNonNumeric As Boolean


    ' Check for Null value, if so Exit Sub
    If strLonValue = "" Then
        TempVars("CancelUpdate") = "True"
        Exit Sub
    End If
    
    ' Set CancelUpdate to False as default
    TempVars("CancelUpdate") = "False"
    
    
    ' Fill Array
    arrDirection = Array("N", "S", "E", "W")
    
    ' Initialize Counter
    intNumCount = 0




    ' Get rid of leading or trailing blanks
    strLonValue = Trim(strLonValue)
    ' assign value to direction variable
    strLonDirection = Right(strLonValue, 1)
        


'''''' Get values for degrees and minutes''''''
    
    ' searches for "-" or " " (space or dash)
    If InStr(strLonValue, "-") > 0 Then
        strSeparator = "-"
    ElseIf InStr(strLonValue, " ") > 0 Then
        strSeparator = " "
    Else
        'Warn user and cancel Update
        MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbExclamation, "Missing Separator"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If


    ' Assign Longitude degrees and minutes to variables
    strLonDegrees = Left(strLonValue, InStr(strLonValue, strSeparator) - 1)
    
    'Check to see if user entered a non-numeric character in place of a number
    If Not IsNumeric(strLonDegrees) Then booNonNumeric = True
    
    strLonMinutes = Left(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator)), _
        Len(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator))))
        
    ' strip direction
    strLonMinutes = Left(strLonMinutes, Len(strLonMinutes) - 1)
        
    ' Check to see if user entered a non-numeric character in place of a number
    If Not IsNumeric(strLonMinutes) Then booNonNumeric = True
        
    ' Warn user, cancel update, and exit
    If booNonNumeric = True Then
        MsgBox "You enetered a non-numeric charactor in place of a number" & vbCrLf & "Please Correct", vbOKOnly + vbExclamation, "Invalid entry"
        TempVars("CancelUpdate") = "True"
        Exit Sub
    End If
        




' ------------------------------------------------------
' ---------------VALIDATION FOR LONGITUDE---------------
' ------------------------------------------------------


' Set Initial Match Value and Check for direction sign


    booFoundMatch = True


    For i = 2 To 3  ' We only need to check the 1st two array elements ("E" or "W")
            If strLonDirection = arrDirection(i) Then
                booFoundMatch = True
                GoTo NEXTCHECK
            Else
                booFoundMatch = False
            End If
    Next i


' Display message if no direction sign.


    If booFoundMatch = False Then ' Build a message
        strMsg = "The Longitude you entered must have" & vbCrLf
        strMsg = strMsg & "a valid E/W direction indicator "
        strMsg = strMsg & vbCrLf & vbCrLf & "Please try again!"
    ' Warn user and cancel update
        MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"  ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
        End If


NEXTCHECK:


    ' Validate Degrees
    If Val(strLonDegrees) > 180 Then
        '     Build a message
        strMsg = "The longitude cannot exceed 180 degrees"
        strMsg = strMsg & vbCrLf & "Please try again!"
        
        ' Warn user and Cancel Update
        MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If
    
    
    ' Validate Minutes
    If Val(strLonMinutes) > 59.9999 Then
        '     Build a message
        strMsg = "The longitude minutes cannot exceed 59.999"
        strMsg = strMsg & vbCrLf & vbCrLf & "Please try again!"
        
        ' Warn user and Cancel Update
        MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If
    
    ' Finally we combine degrees and minutes and
    ' check that the total does not exceed 90
    dblLonDeg = Format(CDbl((Val(strLonDegrees) + Val(strLonMinutes) / 60)), "00.0000")
    
    If dblLonDeg > 180 Then
     '     Build a message
        strMsg = "The longitude degrees and minuntes " & vbCrLf
        strMsg = "Cannot exceed a total of 90 degrees"
        strMsg = strMsg & vbCrLf & "Please try again!"
    ' Warn user
        MsgBox strMsg, vbExclamation, "Data Entry Error"
        TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
        Exit Sub
    End If




Exit_Handler:
    Exit Sub


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "ValidateLongitude"
        Resume Exit_Handler


End Sub


'------------------------------------------------------------
' NAME: Function LatLonToDegs()
' PORPOSE: Convert latitude or longitude entered as a string
' into decimal degrees for use in navigational calculations
' Depends on the ValidateLatitude or ValidateLogitude subroutine
' to insure that the string is entered in the proper format
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
'-------------------------------------------------------------
Public Function LatLonToDegs(strCoordinate As String) As Double
On Error GoTo Err_Handler


Dim strCoords As String
Dim strDirec As String
Dim intNegativeDirection As Integer
Dim dblHaveDegrees As Double
Dim dblHaveMinutes As Double
Dim dblMyVal As Double
Dim strMyCell As String
Dim booNonNumeric As Boolean


TempVars("CancelUpdate") = "False"


' Multiplier variable is positive for North Latitude and East Longitude
intNegativeDirection = 1


    ' If string is null then set TempVars("CancelUpdate") to False and Exit
    If Len(Nz(strCoordinate, "")) = 0 Then
        'MsgBox "No Coodinate provided." & vbCrLf & "Exiting...", vbOKOnly + vbCritical, "Missing Coordinates"
        TempVars("CancelUpdate") = "True"
        Exit Function
    End If
    


    ' Get rid of leading or trailing blanks and asign a N/S/E/W direction to variable
    strCoords = Trim(strCoordinate)
    strDirec = Right(strCoords, 1)


    ' Multiplier variable for Latitude/Longitude sign
    ' Latitude is South (-1)
    If strDirec = "S" Then
        intNegativeDirection = -1
    End If


    'Longitude is West (-1)
    If strDirec = "W" Then
        intNegativeDirection = -1
    End If




    'strip direction
    strCoords = Left(strCoords, Len(strCoords) - 1)




    'searches for "-"
    If InStr(strCoords, "-") > 0 Then


        dblHaveDegrees = Left(strCoords, InStr(strCoords, "-") - 1)
        dblHaveMinutes = Left(Right(strCoords, Len(strCoords) - InStr(strCoords, "-")), _
            Len(Right(strCoords, Len(strCoords) - InStr(strCoords, "-"))))


    'searches for " " Space
    ElseIf InStr(strCoords, " ") > 0 Then


        dblHaveDegrees = Left(strCoords, InStr(strCoords, " ") - 1)
        dblHaveMinutes = Left(Right(strCoords, Len(strCoords) - InStr(strCoords, " ")), _
            Len(Right(strCoords, Len(strCoords) - InStr(strCoords, " "))))


    End If


' Finally add Degrees and Decimal Portion and output result
    LatLonToDegs = (dblHaveDegrees + dblHaveMinutes / 60) * intNegativeDirection


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "LatLonToDegrees"
        Resume Exit_Handler


End Function




'-------------------------------------------------------------------
' NAME      : Function MercatorCse()
' PURPOSE   : Returns course in degress by Mercator Sailing
' REQUIRES  : Lat1, Lon1 = lat and lon for position 1
'           : Lat2, Lon2 = lat and lon for position 2
'           : Assumes input is North = + , East = +
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE      : Feb 2002 (Excel) Revised for Access August 2023
' MISC      : Original formulas from The American Practical Navigator (Bowditch)
'--------------------------------------------------------------------
Public Function MercatorCse(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double)
On Error GoTo Err_Handler


Dim l As Double
Dim Dlo As Double
Dim NS As String
Dim EW As String
Dim m1 As Double
Dim m2 As Double
Dim m As Double
Dim Lon1Sin As String
Dim Lon2Sin As String
Dim LongIndicator As String
Dim Bearing As Double
Dim CseAngle As Double




' Determine if Cse is due north or due south
    If (Lon1 = Lon2) Then
        If (Lat1 < Lat2) Then
            Bearing = 0
        Else
            Bearing = 180
        End If
        
' If Bearing is either due N or due S then go straight to end
        GoTo SkipCalcs
        
' If not then we continue on with calculations
    Else
    
' Assign E/W Direction Sign for Lon1 and Lon2


        If Lon1 < 0 Then
            Lon1Sin = "W"
        Else
            Lon1Sin = "E"
        End If
        
        If Lon2 < 0 Then
            Lon2Sin = "W"
        Else
            Lon2Sin = "E"
        End If


' Convert Longitudes to absolute numbers
        
    Lon1 = Abs(Lon1)
    Lon2 = Abs(Lon2)
       
'--------------------------------------------------------
' DETERMINE which of 4 cases we have for longitude, and
' CALCULATE Dlo and DETERMINE E/W componant of Cse Angle
'--------------------------------------------------------
LongIndicator = Lon1Sin & Lon2Sin
    
Select Case LongIndicator
        
    Case "EW"   ' Going from East to West Longitude
            
        Dlo = Lon1 + Lon2
            
        If Dlo > 180 Then   ' If Dlo exceeds 180 degrees it is shorter to go East
            Dlo = 360 - Dlo
            EW = "E"
        Else
            EW = "W"    'Otherwise we go west
        End If
        
    Case "WE"   'Going from West to East Longitude
            
        Dlo = Lon1 + Lon2
            
        If Dlo > 180 Then   ' If Dlo exceeds 180 degrees it is shorter to go West
            Dlo = 360 - Dlo
            EW = "W"
        Else
            EW = "E"    'Otherwise we go west
        End If
            
    Case "WW"   ' We stay in West Longitude
        If Lon2 > Lon1 Then
            Dlo = Lon2 - Lon1
            EW = "W"    ' We are proceeding in a Westerly direction
        Else
            Dlo = Lon1 - Lon2
            EW = "E"    ' We are proceeding in a Easterly direction
        End If
            
    Case "EE"   ' We stay in East Longitude
        If Lon2 > Lon1 Then
            Dlo = Lon2 - Lon1
            EW = "E"    ' We are proceeding in a Easterly direction
        Else
            Dlo = Lon1 - Lon2
            EW = "W"    ' We are proceeding in a Westerly direction
        End If
            
End Select
    
' Multiply Dlo by 60 for use in further Calculations
    
    Dlo = Dlo * 60


'----------------------------------------------------------------------
' CALCULATE MERIDIONAL PARTS:


' CALCULATION NOTES..........
' Formula for meridional parts is
' m = 7915.7 * Log Tan (45 + (Lat/2) - (Sin(Lat) * 23)
' Used a constant value of 0.017453293 for converting degrees to Radians.
' Used a constant of 0.785398 for 45 in Radians
' Must divide Log of number by Log(10).
' Example: Log(Number) / Log(10)
'-------------------------------------------------------------------------


       m1 = 7915.7 * Log(Tan(0.785398163 + (Lat1 / 2) * 0.017453293)) / Log(10) - (Sin(Lat1 * 0.017453293) * 23)
       m2 = 7915.7 * Log(Tan(0.785398163 + (Lat2 / 2) * 0.017453293)) / Log(10) - (Sin(Lat2 * 0.017453293) * 23)
       m = m1 - m2
       m = Abs(m)
    
' CALCULATE COURSE ANGLE:
' The formula from Bowditch is ... Tan C = Dlo/m


If Lat1 = Lat2 Then
    CseAngle = 90
    GoTo Skip3
End If


    CseAngle = (Dlo * 0.07453) / (m * 0.07453)
    CseAngle = Atn(CseAngle)
    ' Convert to degrees
    CseAngle = RadiansToDegrees(CseAngle)
Skip3:
    ' Set Decimal places to 1
    CseAngle = Round(CseAngle, 1)
    ' Make it a postive number so that the math works properly
    CseAngle = Abs(CseAngle)


' CALCULATE THE COURSE ANGLE:


' Determine if North or South
If Lat2 > Lat1 Then
        NS = "N"
    Else
        NS = "S"
End If




' Determine how to apply the course angle


If NS = "N" And EW = "E" Then
    Bearing = 0 + CseAngle
ElseIf NS = "S" And EW = "E" Then
    Bearing = 180 - CseAngle
ElseIf NS = "N" And EW = "W" Then
    Bearing = 360 - CseAngle
ElseIf NS = "S" And EW = "W" Then
    Bearing = 180 + CseAngle
End If


' FINALLY WE OUTPUT THE RESULT:


End If
SkipCalcs:
MercatorCse = Bearing


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "MercatorCse"
        Resume Exit_Handler


End Function
'-------------------------------------------------------------------
' NAME: Function MercatorDist()
' PORPOSE: Returns Distance in nautical miles by Mercator Sailing
' Lat1, Lon1 - lat and lon for position 1
' Lat2, Lon2 - lat and lon for position 2
' Course as calculated prior in Function MercatoCse()
' Assumes input is North = + , East = +
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
' original formulas from The American Practical Navigator (Bowditch)
'--------------------------------------------------------------------
Public Function MercatorDist(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double, Cse As Double) As Double
On Error GoTo Err_Handler


Dim Lon1Sin As String
Dim Lon2Sin As String
Dim LongIndicator As String
Dim Dlo As Double
Dim l As Double
Dim RadCse As Double
Dim Dist As Double


' Assign E/W Direction Sign for Lon1 and Lon2


If Lon1 < 0 Then
    Lon1Sin = "W"
Else
    Lon1Sin = "E"
End If


If Lon2 < 0 Then
    Lon2Sin = "W"
Else
    Lon2Sin = "E"
End If




' Convert Longitudes to absolute numbers
        
    Lon1 = Abs(Lon1)
    Lon2 = Abs(Lon2)
       
       
' DETERMINE which of 4 cases we have for longitude, and
' CALCULATE Dlo and DETERMINE E/W componant of Cse Angle
       
LongIndicator = Lon1Sin & Lon2Sin
    
Select Case LongIndicator
        
    Case "EW"
            
        Dlo = Lon1 + Lon2
            
        If Dlo > 180 Then
            Dlo = 360 - Dlo
        End If
        
    Case "WE"
            
        Dlo = Lon1 + Lon2
            
        If Dlo > 180 Then
            Dlo = 360 - Dlo
        End If
            
    Case "WW"
        If Lon2 > Lon1 Then
            Dlo = Lon2 - Lon1
        Else
            Dlo = Lon1 - Lon2
        End If
            
    Case "EE"
        If Lon2 > Lon1 Then
            Dlo = Lon2 - Lon1
        Else
            Dlo = Lon1 - Lon2
        End If
            
End Select


Select Case Cse ' Used a constant value of 0.017453293 for converting degrees to Radians


    Case 90
            Dist = Dlo * 60 * Cos(Lat1 * 0.017453293)
    
    Case 270
            Dist = Dlo * 60 * Cos(Lat1 * 0.017453293)
    
    Case 0
            Dist = Abs((Lat1 - Lat2) * 60)
        
    Case 180
            Dist = Abs((Lat1 - Lat2) * 60)
    
    Case Else
    
        RadCse = Cse * 0.017453293  ' Course in Radians
    
        l = Abs((Lat1 - Lat2) * 0.017453293)    ' Get Difference of Latitude in Radians
    
            Dist = l / Cos(RadCse)              ' Divide Differnce of latitude by Cos of course in Radians
            Dist = RadiansToDegrees(Dist)       ' Convert back to Deerees for angular distance on earths surface
            Dist = Abs(Dist) * 60               ' Multiply by 60 to get Nautical Miles
    
End Select


' display result


MercatorDist = Dist


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "MercatorDist"
        Resume Exit_Handler
End Function
'------------------------------------------------------------
' NAME: Function FormatLatitude()
' PORPOSE: Format a latitude string as 00-00.000N (include leading and trailing zeroa)
' REQUIRES: A latitude string value
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: August 2023
'-------------------------------------------------------------
Public Function FormatLatitude(strLatValue As String)
On Error GoTo Err_Handler


Dim strLatDirection As String
Dim strLatDegrees As String
Dim strLatMinutes As String
Dim strSeparator As String
Dim booNonNumeric As Boolean


    ' If we are passed an empty string then exit
    If strLatValue = "" Then
        Exit Function
    End If


    ' Get rid of leading or trailing blanks
    strLatValue = Trim(strLatValue)
    ' assign value to direction variable
    strLatDirection = Right(strLatValue, 1)




'''''' Get values for degrees and minutes''''''
    
    ' searches for "-" or " " (space or dash)
    If InStr(strLatValue, "-") > 0 Then
        strSeparator = "-"
    ElseIf InStr(strLatValue, " ") > 0 Then
        strSeparator = " "
    Else
        'Warn user and cancel Update
        MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbInformation, "Missing separator"
        Exit Function
    End If
     
    ' Assign Latitude degrees and minutes to variables and format
    strLatDegrees = Left(strLatValue, InStr(strLatValue, strSeparator) - 1)
    strLatDegrees = Format(strLatDegrees, "00")
       
    strLatMinutes = Left(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator)), _
        Len(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator))))
    
    ' strip direction and format minutes
    strLatMinutes = Left(strLatMinutes, Len(strLatMinutes) - 1)
    strLatMinutes = Format(strLatMinutes, "00.000")


    FormatLatitude = strLatDegrees & "-" & strLatMinutes & strLatDirection


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "FormatLatitude"
        Resume Exit_Handler
End Function
'------------------------------------------------------------
' NAME: Function FormatLongitude()
' PORPOSE: Format a longitude string as 000-00.000N (include leading and trailing zeroa)
' REQUIRES: A Longitude string value
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: August 2023
'-------------------------------------------------------------
Public Function FormatLongitude(strLonValue As String)
On Error GoTo Err_Handler


Dim strLonDirection As String
Dim strLonDegrees As String
Dim strLonMinutes As String
Dim strSeparator As String


' If we are passed an empty string then exit
If strLonValue = "" Then
    Exit Function
End If


   ' Get rid of leading or trailing blanks
    strLonValue = Trim(strLonValue)
    ' assign value to direction variable
    strLonDirection = Right(strLonValue, 1)
        


'''''' Get values for degrees and minutes''''''
    
    ' searches for "-" or " " (space or dash)
    If InStr(strLonValue, "-") > 0 Then
        strSeparator = "-"
    ElseIf InStr(strLonValue, " ") > 0 Then
        strSeparator = " "
    Else
        'Warn user and cancel Update
        MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbExclamation, "Missing Separator"
        Exit Function
    End If


    ' Assign Longitude degrees and minutes to variables and format
    strLonDegrees = Left(strLonValue, InStr(strLonValue, strSeparator) - 1)
    strLonDegrees = Format(strLonDegrees, "000")
    
    strLonMinutes = Left(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator)), _
        Len(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator))))
    ' strip direction
        strLonMinutes = Left(strLonMinutes, Len(strLonMinutes) - 1)
    strLonMinutes = Format(strLonMinutes, "00.000")


    FormatLongitude = strLonDegrees & "-" & strLonMinutes & strLonDirection


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "FormatLongitude"
        Resume Exit_Handler
End Function
'------------------------------------------------------------
' NAME: Function DecLatToDegMin()
' PORPOSE: Converts Decimal Latitude degrees to a string
' REQUIRES:Decimal Degrees
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: January 2024
'-------------------------------------------------------------
Public Function DecLatToDegMin(ByVal Degs As Double)
On Error GoTo Err_Handler


Dim strNS As String


    If IsNull(Degs) Then Exit Function
    If Degs < 0 Then
        strNS = "S"
    Else
        strNS = "N"
    End If
    
    Degs = Abs(Degs)
    DecLatToDegMin = Int(Degs) & "-" & Format((Degs - Int(Degs)) * 60, "00.000") & strNS


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "DecLatToDegMin"
        Resume Exit_Handler
End Function
'------------------------------------------------------------
' NAME: Function DecLonToDegMin()
' PORPOSE: Converts Decimal Longitude degrees to a string
' REQUIRES:Decimal Degrees
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: January 2024
'-------------------------------------------------------------
Public Function DecLonToDegMin(ByVal Degs As Double)
On Error GoTo Err_Handler


Dim strEW As String


    If IsNull(Degs) Then Exit Function
    
    If Degs < 0 Then
        strEW = "W"
    Else
        strEW = "E"
    End If
    
    Degs = Abs(Degs)
    DecLonToDegMin = Int(Degs) & "-" & Format((Degs - Int(Degs)) * 60, "000.000") & strEW


Exit_Handler:
    Exit Function


Err_Handler:
    clsErrorHandler.HandleError "modLatLon", "DecLatToDegMin"
        Resume Exit_Handler
End Function

[Salty Mariner]

Why do you want to call these calculations in query used as form Recordsource?

You show a typo in the first function: RecordsetClose should be RecordsetClone.

Pass the LegNo via an argument.

Instead of RecordsetClone, open a normal recordset with WHERE clause to return just the one desired record. Or consider using DLookup() in the function instead of any recordset.

Public Function GetNextL(intL, strL)
GetNextL = DLookup(strL, "tablename", "LegNo=" & intL + 1)
End Function

Call function in query:

SELECT *, GetNextL(Lat, "Lat") AS NextLat, GetNextL(Lon, "Lon") AS NextLon FROM tablename;

Do something similar in GetCourse and GetDistance. Pass Lat and Lon as function arguments.

Thank you for the Response June. I will take your suggestions under advisement and try to come up with something.

[Welshgasman]

Subforms load before mainform as well.

[Salty Mariner]

Hello June,

I had an opportunity to look at your comments and while I love the simplicity of the methods you provided my thoughts are that these would be a little more difficult to implement due to how my data is structured.

I'm not dealing with a single table here and tblVpLegs is essentially a join table between tblVoyPlan and tblWapoints. I did this to enable waypoint re-use and the field legNo serves as a sort field for the voyage legs. That makes it a little more difficult to use a simple DLookup. and was why I defaulted to using a recordsetclone. See the relationships below.


Basically, any Voyage Plan can have any number of legs which always start at 1 up to however many legs are in the Voyage plan. Waypoints are normalized as many will be re-used over and over. Think a vessel leaving port until it reaches open water will usually follow the same path on every voyage, many times going to some of the same places.

I'm guessing I'm going to have to practice up on my rather nominal SQL authoring skills. But hey, practice makes perfect right?

[June7]

So what is the form's RecordSource? You can use the same SQL in code to construct Recordset, just add WHERE clause to open with only the one desired record.

Or build a query object that the DLookup can reference.

[Salty Mariner]

The Forms recordset is:

SELECT tblVpLegs.VLeg_ID, tblVpLegs.VP_ID, tblVpLegs.LegNo, tblVpLegs.Wpt_ID, tblWaypoints.WptName, tblWaypoints.Latitude, tblWaypoints.Longitude, tblVpLegs.LegSpeed, tblWaypoints.Remarks
FROM tblWaypoints INNER JOIN tblVpLegs ON tblWaypoints.Wpt_ID = tblVpLegs.Wpt_ID;

I'm trimming that down for use in my function and buildig a recordset to look something like this:

    strSQL = "SELECT tblVpLegs.VP_ID, tblVpLegs.LegNo, tblWaypoints.Latitude, tblWaypoints.Longitude " _
        & "FROM tblWaypoints INNER JOIN tblVpLegs ON tblWaypoints.Wpt_ID = tblVpLegs.Wpt_ID " _
        & "WHERE tblVpLegs.VP_ID = intVpID AND tblVpLegs.LegNo = intLegNo;"

But Yes, I guess I could also build a query, have the VP_ID reference the form and then use DLookup to get the Lat and Long via the Where Clause of LegNo +1 as outlined above.

[June7]

Have to concatenate variables to construct SQL.

& "WHERE tblVpLegs.VP_ID = " & intVpID & " AND tblVpLegs.LegNo = " & intLegNo

[Salty Mariner]

I got it working.

I created a query and ran DLookup against it and all of my other functions are now working inside of the forms recordset.

I should be able to run Domain aggregate functions in the subform footer to sum total distance and elapsed time. Major hurdle overcome and now I can work on other things. Thanks for steering me in the right direction.

[June7]

Can't you use Sum() in footer?

[Salty Mariner]

Can't you use Sum() in footer?

I did just that this morning. Working like a champ.

[Welshgasman]

The totals diaplayed do not add up to the values being displayed.?

Is that going to cause you an issue?