[AccessD] RESOLVED: Dealing with downsteram effects in manual field information based on bound form controls

Thanks to those who read this in either List... resolved thanks to Emilia
Maxim (Access-L) steering me in the right direction with a reminder about
Form BeforeUpdate.

This response forced me to set up a test table and a test form, and try what
I have only until now, merely had some preconceived - and wrong -  notions
about.

I now understand field level Before and After Update events and how they
differ from the form's events of the same name. 

I had though that the control's beforeupdate event was the last place to
stop an unwanted change - not so!

I see that up to and including the Form's Before Update event, one has a
handle to OldValue for any bound control, which is also Null for an unsaved
record regardless how many times one might have passed through that
control's AfterUpdate event.

Thank You Emilia - I will use the Form BeforeUpdate event!

I will have more trouble before I am done with this application I am sure.

Here are some interesting(?) corollaries of this, for people who like me
have been using DLOOKUP and SQL and RecordsetClone to get values from a
recordsource in times past, due to more work with unbound controls:

Until Form BeforeUpdate has completed with cancel still False, DLOOKUP() and
Recordsetclone and any query of the field that is tied to a control's
ControlSource will retrieve the same information as the OldValue property of
the bound control, regardless how many times that control's BeforeUpdate
event has completed with Cancel = False.

Setting Cancel = TRUE in a control's BeforeUpdate event seems to have the
effect of forcing the user to be unable to leave that control during
editing, except to click Escape. Am I wrong in saying this has about the
same effect as Field.Locked = True?

Bill
-----Original Message-----
From: Microsoft Access Database Discussion List
[mailto:ACCESS-L at PEACH.EASE.LSOFT.COM] On Behalf Of Emilia Maxim
Sent: Saturday, May 24, 2014 6:15 PM
To: ACCESS-L at PEACH.EASE.LSOFT.COM
Subject: Re: Dealing with downsteram effects in manual field information
based on bound form controls

Bill,

> reminder text (and other free-form data) in the database. I feel it is 
> easier to handle this at a form level of validation and not at a field 
> level of validation. And that it is easier to do with unbound 
> controls, rather than have to put data checks in the BeforeUpdate 
> events of all controls that might impact a downstream record.

Then why not the form's BeforeUpdate event?


--
Regards
Emilia, TWIG

Emilia Maxim
PC-SoftwareService
Stuttgart, Germany



-----Original Message-----
From: Bill Benson [mailto:bensonforums at gmail.com] 
Sent: Friday, May 23, 2014 4:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Dealing with downsteram effects in manual field information based
on bound form controls

I am starting to run into some struggles with bound controls. I wonder if I
will find agreement in this crowd. My current situation is that I have
companies, drivers, events, and scheduled notifications (associated with
those events) in a CRM-like tool. The reminders can be auto-generated based
on data in those tables, or can be over-ridden. I am not storing the default
reminder text because it can be created via a formula, and does not need to
be referenced except as a basis for manual reminder text, or to create a
reminder at time of send. Manual text is beyond the effects of changes in
other table data; but this is not to say that it should not be re-validated
with the user as changes occur. For example, one default reminder might be
"Dear [William Benson], Your [truck needs to be inspected] on or before
[June 15th, 2014]. Please attend to this and notify [DriverUpdates at ABC.COM]
of the completion date and the updated due date. This is your [2nd]
reminder. Sincerely, [Joe at ABC.Com]". If alternate reminder at time=T1, is
"Dear Bill etc" instead of "Dear William Benson etc". this is no longer
linked in any way to the other raw data. The driver can later be changed
from William Benson to Jonathan Appleseed in one of two ways, without
impacting "Dear Bill". FKDriverID might get changed in the Event table to a
value that that retrieves the name Jonathan Appleseed from the Drivers
table; or the Drivers table name associated ID = Event.FKDriverID might get
updated from William Benson to Jonathan Appleseed. I recognize my duty as
programmer to be aware when these changes occur, and their impact on
reminder text (and other free-form data) in the database. I feel it is
easier to handle this at a form level of validation and not at a field level
of validation. And that it is easier to do with unbound controls, rather
than have to put data checks in the BeforeUpdate events of all controls that
might impact a downstream record. 

Anyone have comments on this?