catch data type invalid error and convert it before system does

[jwan142]

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 activated, I think the system 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!!!

[debasisdas]

you need to check the validity of data at front end before passing the same to database for processing.

[NeoPa]

Are you doing this in VB or in Access? If the latter then this is posted in the wrong forum.

Anyway, I think you're going about this the wrong way. It seems you have a control defined as accepting numeric data only, and you want to capture the error when (valid) non-numeric data is entered. What you need to do surely is to change the control so that it doesn't expect only numeric data.

[jwan142]

I am doing this in Access. Unfortuanately the post i put up on Access forum got deleted, because i accidently post it up here first.

Anyway. Thank you people, i think i understand the situation now which I should not bound the object for numberic data only from the "table" level.

Have a great coming week!

[NeoPa]

jwan142: I am doing this in Access. Unfortuanately the post i put up on Access forum got deleted, because i accidently post it up here first.

No. It got deleted because you posted the same question twice. You should have posted a link from one forum to the question already posted in the other. Alternatively, as in this case, you could have requested it be moved as this has nothing to do with the VB language. I'll move this across for you now.

jwan142: i think i understand the situation now which I should not bound the object for numberic data only from the "table" level.

If you do understand then that's a strange response. It's about throwing away restrictions on the data being numeric - not about whether or not the control is bound.

[jwan142]

What am I probably going to do is to change the attribute type in the original from number (long) to text. Then set up some conditions/catches in the "beforeupda te" event.

Please correct me if i am on a totally wrong direction.

[WackoZacho]

What am I probably going to do is to change the attribute type in the original from number (long) to text. Then set up some conditions/catches in the "beforeupda te" event.

Please correct me if i am on a totally wrong direction.

That sounds about right. You can have the textbox as unbound, so it will accept any data type. I would use the after update event to go back and check the value, if the value is in lat/longitude format, leave it, if it is in degrees, make a conversion function to convert it to lat/long format, and if it's not in degrees or lat/long format, msgbox the error then return focus to that field.

You would also probably want to add the same sort of check for when you click on the button or whatever triggers your form to save the data whether it's a save btn or next record btn, etc, have it recheck the value of that box one last time before saving the value of the textbox into the associated table.

[NeoPa]

That's a good idea Wacko.

I would consider changing the type of the underlying field to Text though. This way one needn't complicate matters with unbound controls (which would properly create a need to manage a related but hidden bound control anyway), and can apply the updates you suggest within a single bound control.

Welcome to Bytes!

[NeoPa]

jwan142: What am I probably going to do is to change the attribute type in the original from number (long) to text. Then set up some conditions/catches in the "beforeupda te" event.

Please correct me if i am on a totally wrong direction.

That seems very sensible. WackoZacho has posted some good ideas about how to handle the BeforeUpdate part of it. That should be all you need, but if further assistance is required post back :)