Error trapping 1

[cyberbob999]

I have a date field on my form. I would like to keep all of the functionality associated with the field being a date; however, I have one problem. If I enter a non-date string in to the field, I would like to be able to deal with the error handling before Access pops up saying "The value you entered isn't valid for this field." At first I thought the beforeupdate event would be sufficient, but apparently access checks the data type before reaching the beforeupdate event.

So, my question is, is there any place I can put code that will execute after the user has finished entering a value and before Access checks the data format?

Thanks

 

[Rolliee]

It sounds like what you really want is to shut off ACCESS warnings TEMPORARILY. Being an old programming, I cannot just think of the code. Check with HELP and shut them (the warnings ) off do your check and then turn them back on.

rollie@bwysy.net

 

[cyberbob999]

You're right, that does sound like what I want to do. Unfortunately, I tried it and I still get the "warning" even with:

docmd setwarnings false

My uneducated guess is that this message ("The value you entered isn't valid for this field.&quot isn't actually a warning, but of some other nature. Any other thoughts on how to get around this?

Thanks

 

[Gazonice]

Hi,

You can set the validation Rule for the field in the table, and then assign some validation text that is displayed if the rule is broken.

Garry

 

[cyberbob999]

Let me tell you exactly what I'm trying to do... I have a date field that I am formatting as yymmdd. However, even though it is displayed in this format, the user cannot enter the date in that format because Access doesn't accept it as a valid date. So, what I want to be able to do is to test the date (using Isdate()) after it is entered and prior to Access rejecting it as invalid for the field and then, if it is not in correct date format, convert it using CDate(). The only work around I can think of is to use an unbounded field, test the date and then write to the table as a date. But, by doing this, I am forced code something that makes the unbounded field act as a date (i.e. accepts all possible date formats). I think is is achievable, but it seems like there should be an easier way around it (especially since I have about 20 date fields on my form that I would have to code).

 

[Gazonice]

surely you have to have some kind of seperator between parts of the date?

eg: 04-02-07 - this accepts without problem.

Maybe you could specify an input mask?

Garry

 

[cyberbob999]

Actually, there are no separtors of any sort. And, unfortunately, the format that I put the dates in is not flexible--more specificially, the format that I DISPLAY the dates in is not flexible. The underlying tables all store the dates in a conventional manner (i.e. mm/dd/yyyy).

 

[Gazonice]

Personally I think I would be looking at collecting the data in a conventional way (or using Calendar Control), then presenting it in a pre-defined format, control.

I can't think of anything else.

Garry

 

[cyberbob999]

Unfortunately, most of the dates are manually transcribed from paperwork (written in the yymmdd format) so I think it would be cumbersome for the person entering the data to have to mentally convert each date to a conventional format, then enter it vice just being able to mindlessly type the yymmdd number that is written on the paper.

I think what I'm going to do is make the table entry a NUMBER vice DATE and then use CDate to convert it to a date whenever I need to perform an operation with the value. I think I see a way around but it seems very cumbersome oh well! Thanks for the help...

 

[Terpsfan]

I would create a field with the date/time data type and then specify a format of yy\/mm\/dd in the format property on your table property sheet. That should allow you to enter the date in the format you specified.

 

[cyberbob999]

hmmm...either I don't understand or it doesn't work. I tried yy\/mm\/dd in in the table properties format for the field in question and I still got the invalid data type message. I haven't had a chance to do any research yet, but what is the backslash-forward slash combination do?

 

[Rolliee]

I would write a public function and its mate in the global module and pass that back and forth to store what I want and see what I want. If you need help with this, just let me know.

rollie@bwsys.net

 

[cyberbob999]

Rollie:

I don't really follow--could you elaborate please?

Thanks!

 

[Terpsfan]

You shouldn't be getting any errors from that because it's working fine in my application. Perhaps it's the version of Access you are using...I'm using Access 2000. You might have to delete the control on the form and drop it back on from the field list box. Then test it out again. Are you using any validation rules? The back slash is an escape sequence that indicates the next character is not alphanumeric. It displays the next character as a literal character.

 

[Rolliee]

Bob,

Here are two companion public functions from a global module.

Public Function daytIn(date1 As Date) As String
daytIn = Format(date1, "yymmdd&quot
End Function

Public Function daytOut(daytIn1 As String) As String
' input example "041202"
daytOut = "#" & Mid(daytIn1, 5, 2) & "/" & Mid(daytIn1, 3, 2) & "/" & Mid(daytIn1, 1, 2) & "#"
End Function

Put a date in one and get out your special form of date. Put your form of date in the other and get out a date. These work in forms and in queries.

Rollie

 

[cyberbob999]

Very helpful Rolliee--thank you VERY much!

 

[jazzz]

How to handle errors is in the Error Event for either the object or the Form you can display your own error message based on the error number you receive. I assume you are using error handling throughout your form events? So you should then be receiving the Error Number. Once you know where the error is being generated then you know where to handle it. Therefore all event procedures should have a error handler.

In the Form Error Event you could use something like this:

On Error GoTo ProcedureError
'*create a flag to handle if we show the access error.
Dim fshowIt As Boolean

Err.Number = DataErr
Err.Description = AccessError(DataErr)

'*set our flag to true we'll turn it off if we have an error and handle it.
fshowIt = True

Select Case DataErr
'*handle form errors starting here.

Case 2279 'invalid input mask used for dates
If Screen.ActiveControl.Name = "txtdate" Then
Msgbox "The item you entered is not a valid date." & vbCrLf _
& "Please enter a valid date!"
Me("txtdate&quot.Undo '* you can set focus here instead.
fshowIt = False
End If


End Select

'* if a form error occurs and we are not traapping it then show the error so it can be handled by us.

If fshowIt = True Then
MsgBox "Error:" &" "& Me.Name & ": " & Err.Number & vbCrLf _
& Err.Description
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If


Good Luck!



Life's a journey enjoy the ride...

jazzz

 

[faeryfyrre]

The other way of doing this would be to use the BeforeUpdate event of the control storing the Date. This way you can test the dates entered before Access attempts to update the underlying record and therefore before access throws up its invalid datatype error.

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"