Unanswered: How to catch data type invalid error before Access does
GOOOOD DAY. Geeks
This is my first post and I do not know whether my title makes sense to you.
Here is the problem..
I have a Access database. There is a table contains two fields called Latitude and Longitude which are stored as numerical coordinate, Number (Double, decimal).
Based on this table, i crated a form to fill in these two values.
I would like to create an error handling to give specific error message, or to convert these two coordinates to number if people input text format data in these field eg, (linear format 51° 28' 38" N to numerical 51.47222).
However, before the error event kicks or even validation rule happens, the Access captures this data invalid error, and gives the message "The Value you entered isn't valid for this field,,, for example, you may have entered........".
I have tried at least several hours to search and read through other people's post, but no luck at all.
Hope I explained my problem clearly to you and all your response or feedback is much appreciated!!!
That's easy: Do not bound the textboxes, parse the entered values, process them if necessary then write them into the table. You could also keep the textboxes bound and act on the BeforeUpdate event of each textbox.
I would begin by writing several check functions:
IsValidNumericLatitude (AnyVariant) As Boolean
IsValidNumericLongitude (AnyVariant) As Boolean
IsValidLinearLatitude (AnyVariant) As Boolean
IsValidLinearLongitude (AnyVariant) As Boolean
Private Sub Text_Latitude_AfterUpdate()
Dim ValidLatitude as Boolean
Dim Latitude as Double
If IsValidNumericLatitude(Me.Text_Latitude.Value) = True Then
Latitude = Me.Text_Latitude.Value
ValidLatitude = True
ElseIf IsValidLinearLatitude(Me.Text_Latitude.Value) = True Then
' Process the input value to convert it to a double
Latitude = <Result of the process>
ValidLatitude = True
MsgBox Me.Text_Latitude.Value & " is invalid and cannot be converted to a latitude."
Me.TextLatitude.Value = ""
If ValidLatitude = True Then
' Write the value into the table (several methods are possible)