Data Validation and Event Handling for Form Controls

[Joe Cletcher]

Trying to validate data in a text box control on an Access 2007 form using the "on change" event and/or "after update" event. The text box (e.g. MyTxtBox) contains a date that
1) must be within a range based on the NOW() function date, and
2) must be <= another date (may be NULL) in a text box on the same form
Data for the text box can come from a date picker or keyboard entry.

The "on change" event for the MyTxtBox.value has the prior value (either NULL or a date) and not the date from keyboard entry or date picker.

The "after update" event is invoked if MyTxtBox loses focus and that handler has the desired MyTxtBox.value. However, if the date value does not meet the criteria (a modal msgbox is displayed) and if the user wishes to try again, the following happens:
1) MyTxtBox.Value is successfully set to NULL, and
2) an attempt to keep the focus on MyTxtBox by using MyTxtBox.SetFoc us fails (while debugging, no error messages occurred and it appears to execute the SetFocus successfully but didn't set the focus).

How can I get the new value of MyTxtBox, validate this value, and keep focus on MyTxtBox if a correction is warranted?

[ariful alam]

you can use embedded macro in those events to validate and show message box on error.

[TheSmileyCoder]

First welcome to Bytes. I can tell allready by the way your described your problem, and the steps you have taken to examine it yourself that we will be happy to help you with any problems. Good quality questions gets good quality answers.

The "on change" event for the MyTxtBox.value has the prior value (either NULL or a date) and not the date from keyboard entry or date picker.

That is because the change event is something that happens before the value is changed (thus your seing the old value as you explained). Still using the OnChange event, you need to use: MyTxtBox.TEXT instead of MyTxtBox.VALUE.

The .TEXT is the temporary value of the control before an attemp is made to write the information into the control. You could then color the textbox border for instance, based on whether or not its a valid value, and on top of that, perhaps use the Before_Update event to fire a msgbox if the date is invalid.


Note that the Change event fires each time a keystroke that changes the value is pressed. (So left/right does not fire the change event, but any letter/number or backspace WILL fire the change event), and that the beforeupdate event is fired only when user leaves the textbox or he clicks enter. Also note that the beforeupdate event can be cancelled.

I would think its a bad idea to tie a msgbox into the changeevent since if user is typing the date manually he might get a msgbox per keystroke, which would be annoying as ....

Hope that helps.

[Joe Cletcher]

Thanks!

Thanks again. I'm out of the office until Wednesday and will implement your solution then. Can always test MyTextBox.Text with IsDate in coordination with length. Can probably set validation clause for IsDate to be true thus preventing loss of focus before a valid date is present. In an extreme case, someone could type "I'm a dummy" followed by a <tab> or <enter>, which would fire the "After Update" event, facing me with the conundrum of not being able to set the focus back to the control. May have to use "Before Update" and cancel as you also suggested. You gave me a path forward.

First welcome to Bytes. I can tell allready by the way your described your problem, and the steps you have taken to examine it yourself that we will be happy to help you with any problems. Good quality questions gets good quality answers.




That is because the change event is something that happens before the value is changed (thus your seing the old value as you explained). Still using the OnChange event, you need to use: MyTxtBox.TEXT instead of MyTxtBox.VALUE.

The .TEXT is the temporary value of the control before an attemp is made to write the information into the control. You could then color the textbox border for instance, based on whether or not its a valid value, and on top of that, perhaps use the Before_Update event to fire a msgbox if the date is invalid.


Note that the Change event fires each time a keystroke that changes the value is pressed. (So left/right does not fire the change event, but any letter/number or backspace WILL fire the change event), and that the beforeupdate event is fired only when user leaves the textbox or he clicks enter. Also note that the beforeupdate event can be cancelled.

I would think its a bad idea to tie a msgbox into the changeevent since if user is typing the date manually he might get a msgbox per keystroke, which would be annoying as ....

Hope that helps.