Formatting Latitude Longitude for a Text Box and update events

[Mariner81]

Have a subroutine I created for validating a string entered in a textbox for latitude and longitudes It does its job just fine. It checks for degrees and minutes being within the proper range, that there is a valid separator between the degrees and minutes, that N.S, E, and W are properly identified and that the sum of degrees and minutes together do not exceed what hey are supposed to.

The subroutine is called from the BeforeUpdate event of a textbox and a TempVar is used to pass whether to CanceUpdate or not.

I also have a Function that converts latitude or longitude entered as a string into decimal degrees for use in navigation calculations.

What I would like to do next is to have a Function that formats the Lat/Lon string and standardizes it so all fields look essentially the same. For example if a user enters 5-7.09N It gets saves 05-07.090N with leading and trailing zeros as appropriate. I can write the string functions to accomplish this. My question is where is the best place to call this from? Perhaps on the LostFocus event??? I don't think I can do it from within the BeforeUpdate Event. But if I Update the field after losing focus do I get stuck in a continuous Update loop? I'm puzzled.

Any thoughts appreciated.



My validation code:

'------------------------------------------------------------
' 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 ValidateLatitude_Err


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


' Check for Null value, if so Exit Sub


If strLatValue = "" Then
    TempVars("CancelUpdate") = "True"
    Exit Sub
End If




TempVars("CancelUpdate") = "False"


' Fill Array
arrDirection = Array("N", "S", "E", "W")


' Initialize Counter
intNumCount = 0




    ' If we are passed a null value exit subroutine
    If IsNull(strLatValue) Then
        Exit Sub
    Else
        ' Get rid of leading or trailing blanks
        strLatValue = Trim(strLatValue)
        ' assign value to direction variable
        strLatDirection = Right(strLatValue, 1)
    End If




'''''' 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)
    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)


' ------------------------------------------------------
' ---------------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
    
    
ValidateLatitude_Exit:
    Exit Sub


ValidateLatitude_Err:
    MsgBox Err.Number & " " & Err.Description
    Resume ValidateLatitude_Exit
    
End Sub

[pbaldy]

I'd probably use the after update event of the textbox.

[Mariner81]

I'd probably use the after update event of the textbox.

AfterUpdate worked. I thought I was going to get caught in an endless loop but that did not happen. Thank you. I worried a lot for nothing.

I've gone through a great deal of trouble with string manipulation in order to allow my users to enter geographical coordinates in the same way as if they would write them down. I actually added another check for non-numeric characters in the degrees and minutes.

Thanks for the help.

[pbaldy]

No problem, glad you got it working.