Error handling during entry

[twgonder]

... The latest is just another complaint about the same sort of thing - this time it's about using Date functions in table validation - an easily searchable subject that should take you 2 minutes to discover that it isn't allowed. Instead, the perception is that the tool you are using is so inferior to everything else you've used and it can't be that you aren't reading the assembly instructions for it.
Good luck. Signing off.

So I'm really curious, where do you think it's documented that date functions don't work in date validations? Change the extended date to normal and it works fine. As shown in another post above, Microsoft doesn't imply in any way, that I can see, that there should be a problem with their own example with extended dates.

But go ahead, change the field type in the Error.accdb, as see for yourself that all the documentation that you could find on the error, in two minutes, is wrong.



However, you are correct! It's another complaint about the same sort of thing---THAT SEEKS A LOGICAL SOLUTION (despite my reading the "assembly instructions" [assuming you mean those on-line helps that aren't actually written by Microsoft employees that would have source code and designs available to them when writing documentation])!

[twgonder]

This time it's not a dent, but a hole the size of a bowling ball in my brick wall.

Last night, I took the file from post #28 and added Before Update procedures in the form module that just did a stop on the three textboxes.
This was to see the order that procedures ran when a bad entry was put in for MyDate in the form.

I ran this test more than a dozen times, trying different inputs.
Each time, the Before Update procedure happened and then the Form On Error fired off, often twice. Even with having cancel = true in the Before Update procedure.
I based my rant on this behavior.
Given the number of times I ran it, and how carefully I checked which procedure ran, I'm sure of what I saw.

This morning, after just a hibernation, I continued with proof of concept changes. Nothing drastic that should change the order of the events. Mostly just adding variables.

Then I started a quick test, and this time Form On Error ran and the Before Update for the field didn't with an entry error.
I tested on existing and new records like last night.
I don't have a copy of the file as it was last night and have spent two hours trying to duplicate the Before Update procedure running before the Form On Error procedure.
I'm at a total loss at this point as to why the order of the procedures has been different in less than twelve hours (most of that sleeping).

WTF?!!!

[orange]

You may find some helpful tips in this form events material from SourceDaddy.
If you get a test database set up, I'm sure others would appreciate info related to events and issues.

[moke123]

So I'm really curious, where do you think it's documented that date functions don't work in date validations? Change the extended date to normal and it works fine. As shown in another post above, Microsoft doesn't imply in any way, that I can see, that there should be a problem with their own example with extended dates.

here for one...

https://support.microsoft.com/en-us/...0-9851042e0024

Warning When you create expressions and use date/time functions based on the Date/Time Extended data type in Access, you may lose precision in calculations or encounter other problems with the results. We are aware of this issue and plan to better support expressions and functions in an upcoming release. As a workaround, you can Create a pass-through query to use the equivalent SQL Server expression and date/time functions. For more information, see Comparing Access SQL with SQL Server TSQL.

As far as I can see the only current use for Date/Time Extended is for compatibility with sql server date/Time2 datatype.

[Minty]

As far as I can see the only current use for Date/Time Extended is for compatibility with sql server date/Time2 datatype.

That is the only use for it, and it's rubbish for that as well.

I've turned off compatibility with the DateTime2 data type in most of my Access front ends connected to SQL servers. It's useless.
It interprets it as text most of the time, and breaks loads of date related functionality.

Avoid at all costs.

[twgonder]

... If you get a test database set up, I'm sure others would appreciate info related to events and issues.

I've been working on a proof of concept db to do just that.

I've hit a little snag and Google hasn't provided what I need.
On an existing record, I'm entering an "a" in the MyNumber textbox (example above, post #28) and it causes a form error.
I breakpoint on the first line of Form_Error and what is in me!MyNumber is the old value from the existing record and not what I entered in the textbox.
How can I get what was entered in the textbox for my own version of validation?

[Minty]

That's because the Form_error is firing before the data is saved. Try using

Me.MyNumber.Text

As that will might return the text value of the control.

[twgonder]

@ Minty I knew what the problem most likely was, just have never hit this situation of needing to use ".Text" before. Thanks

[Minty]

It's a property that should be used sparingly as it isn't necessarily the saved control value.
But handy for this type of situation.

[twgonder]

Another little glitch in the prototype (which I will share when done).

The save button from the Error.accdb above in post #28, uses the Me.Dirty = False
I picked this up along the way in another thread.

The problem is, Me.Dirty = False bypasses the Form_Error and the Cancel = True in Before_Update for the form messes up too (run-time error).
If one uses a Save button, is there a better way than Me.dirty = False to initiate the save record process?
Or a way to best escape the dirty save, because it gets caught in a loop.



The debugger then goes to the Me.Dirty line.
I could set variables to bypass the Me.Dirty after the first instance, but I'm wondering if there is a better method.

Note: I tried Me.Refresh after some research, and it has the same problem.
I tried the stopping the Me.Dirty on a second instance, but it traps to the line after the run-time error (meaning it can't be avoided).

[twgonder]

I've been working on a proof of concept db to do just that.

Attached is a down-and-dirty-imperfect sample .accdb that may help those in a similar situation of dealing with data-entry errors at entry and save.
There are comments in the properties of Options.

There are a few inconsistencies for the purpose of testing.
There are some stops and then breakpoints can be added for further analysis and stepping through the code.
Depending on the order that you intentionally make "bad" entries, you may get some very strange results, and maybe even run-time errors that go to debug.
If you're new to VBA, you'll want to know how to use F5, F8, breakpoints during testing.

Try changing values in existing records. Add new records. Delete data in textboxes, tab through fields (or click or enter). Save the records in the various ways and see what procedures run and don't run by comparison.

This was created using Office 2021, your results may vary.

Now don't say all I ever do is complain and ignore advice!