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:
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