Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Thumbs up 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!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    IsValidNumericLatitude (AnyVariant) As Boolean
    IsValidNumericLongitude (AnyVariant) As Boolean
    IsValidLinearLatitude (AnyVariant) As Boolean
    IsValidLinearLongitude (AnyVariant) As Boolean
    Then:
    Code:
    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
        Else
            MsgBox Me.Text_Latitude.Value & " is invalid and cannot be converted to a latitude."
            Me.TextLatitude.Value = ""
            Me.TextLatitude.SetFocus
        End If
        If ValidLatitude = True Then
            ' 
            ' Write the value into the table (several methods are possible)
            '
        End If
    Have a nice day!

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    hey Sinndho, What you mentioned here makes absolute sense. Thanks so much. I am going to do what you just said and let you know if i had any success!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •