Text Box's BeforeUpdate Event Not Firing

[MikeC]

I'm using an Access XP form as a popup calendar that enables the user to
select dates and thereby pass the date data to various bound text box
controls on other continuous and non-continous forms and subforms and
subforms of subforms...

On one of my "regular" continuous forms, I want to immediately detect when a
new value entered into the text box falls outside a date range defined by
two other unbound text boxes in the header section of the same form. Once
this condition occurs, I want to undo the change, prevent any other changes,
and notify the user that a different date needs to be entered.

The problem is that when the date is *programatically* passed to the text
box control, the text box doesn't "know" it has been populated, so the *text
box's* BeforeUpdate event does not fire. The same is true for the text
box's Dirty, Change and AfterUpdate events. The only event I have found so
far that detects the change is *form's* BeforeUpdate event. However, this
event occurs later than I prefer. (Also, worth noting is that the form's
Dirty event does not fire in spite of the fact that the form "knows" to fire
the BeforeUpdate event.)

I've also tried the text box's LostFocus event to compare the old and new
values, but this technique gets cumbersome and also requires that the
control lose focus after it's value has changed and this isn't necessarily
going to be true.

Is there a preferred method, for the above scenario, to immediately detect
when the value of a control has been programmatically changed?

 

[AlCamp]

Mike,
After passing the date value, "call" (force) the BeforeUpdate event...
(use your own field names)
[YourDateField] = [TheNewDate]
YourDateField_AfterUpdate
hth
Al Camp

 

[MikeC]

In order for the calendar form to fire the calling control's BeforeUpdate
event, I believe the following things need to happen:

1) The calling control needs to identify itself by passing a *complete*
reference to itself, including forms, subforms, etc. I say this because
*if* the calling control is on a subform (not in this particular example,
but on other forms), then a simple reference to it's form (Me.Name) won't
work because the subform's name is not exposed to the calendar form.
Therefore, I would need to reference the control through its lineage. i.e.
I would need to reference its parent or its parent's parent or whatever.
Maybe the solution here is to make this input variable *optional* in the
calendar form so that I am not always forced to use it, paticularly in cases
where the control is burried.

2) I need to make the calling control's BeforeUpdate event *public*,
otherwise the calendar form won't see the procedure. As mentioned above, I
could make the reference to the control optional and then execute the
calling control's BeforeUpdate event procedure *only if* a reference to the
calling control has been passed. Using this approach, I would need to make
the BeforeUpdate event *Public* only for those controls where I specifically
want the BeforeUpdate event to fire.

If you have better alternatives to the above, please let me know. Thanks.

Also, if you know of a convenient function (or property??) that will return
the complete reference to any control, please let me know.

Mike,
After passing the date value, "call" (force) the BeforeUpdate event...
(use your own field names)
[YourDateField] = [TheNewDate]
YourDateField_AfterUpdate
hth
Al Camp

I'm using an Access XP form as a popup calendar that enables the user to
select dates and thereby pass the date data to various bound text box
controls on other continuous and non-continous forms and subforms and
subforms of subforms...

On one of my "regular" continuous forms, I want to immediately detect
when a new value entered into the text box falls outside a date range
defined by two other unbound text boxes in the header section of the same
form. Once this condition occurs, I want to undo the change, prevent any
other changes, and notify the user that a different date needs to be
entered.

The problem is that when the date is *programatically* passed to the text
box control, the text box doesn't "know" it has been populated, so the
*text box's* BeforeUpdate event does not fire. The same is true for the
text box's Dirty, Change and AfterUpdate events. The only event I have
found so far that detects the change is *form's* BeforeUpdate event.
However, this event occurs later than I prefer. (Also, worth noting is
that the form's Dirty event does not fire in spite of the fact that the
form "knows" to fire the BeforeUpdate event.)

I've also tried the text box's LostFocus event to compare the old and new
values, but this technique gets cumbersome and also requires that the
control lose focus after it's value has changed and this isn't
necessarily going to be true.

Is there a preferred method, for the above scenario, to immediately
detect when the value of a control has been programmatically changed?

 

[MikeC]

....Looks like I found a pretty good solution. I'll try to post the code
later when I have a little time.

In order for the calendar form to fire the calling control's BeforeUpdate
event, I believe the following things need to happen:

1) The calling control needs to identify itself by passing a *complete*
reference to itself, including forms, subforms, etc. I say this because
*if* the calling control is on a subform (not in this particular example,
but on other forms), then a simple reference to it's form (Me.Name) won't
work because the subform's name is not exposed to the calendar form.
Therefore, I would need to reference the control through its lineage.
i.e. I would need to reference its parent or its parent's parent or
whatever. Maybe the solution here is to make this input variable
*optional* in the calendar form so that I am not always forced to use it,
paticularly in cases where the control is burried.

2) I need to make the calling control's BeforeUpdate event *public*,
otherwise the calendar form won't see the procedure. As mentioned above,
I could make the reference to the control optional and then execute the
calling control's BeforeUpdate event procedure *only if* a reference to
the calling control has been passed. Using this approach, I would need to
make the BeforeUpdate event *Public* only for those controls where I
specifically want the BeforeUpdate event to fire.

If you have better alternatives to the above, please let me know. Thanks.

Also, if you know of a convenient function (or property??) that will
return the complete reference to any control, please let me know.

Mike,
After passing the date value, "call" (force) the BeforeUpdate event...
(use your own field names)
[YourDateField] = [TheNewDate]
YourDateField_AfterUpdate
hth
Al Camp

I'm using an Access XP form as a popup calendar that enables the user to
select dates and thereby pass the date data to various bound text box
controls on other continuous and non-continous forms and subforms and
subforms of subforms...

On one of my "regular" continuous forms, I want to immediately detect
when a new value entered into the text box falls outside a date range
defined by two other unbound text boxes in the header section of the
same form. Once this condition occurs, I want to undo the change,
prevent any other changes, and notify the user that a different date
needs to be entered.

The problem is that when the date is *programatically* passed to the
text box control, the text box doesn't "know" it has been populated, so
the *text box's* BeforeUpdate event does not fire. The same is true for
the text box's Dirty, Change and AfterUpdate events. The only event I
have found so far that detects the change is *form's* BeforeUpdate
event. However, this event occurs later than I prefer. (Also, worth
noting is that the form's Dirty event does not fire in spite of the fact
that the form "knows" to fire the BeforeUpdate event.)

I've also tried the text box's LostFocus event to compare the old and
new values, but this technique gets cumbersome and also requires that
the control lose focus after it's value has changed and this isn't
necessarily going to be true.

Is there a preferred method, for the above scenario, to immediately
detect when the value of a control has been programmatically changed?

 

[AlCamp]

Good deal Mike...
I guess I kinda lost you on this one. I thought it was a simpler matter
than it turned out to be.
There are so many "newbie" questions that I often go for the simplest
solution.
I tried to boil you question down to "how can I get BeforeUpdate to
"fire" on a programmatical update?"
Al Camp

...Looks like I found a pretty good solution. I'll try to post the code
later when I have a little time.

In order for the calendar form to fire the calling control's BeforeUpdate
event, I believe the following things need to happen:

1) The calling control needs to identify itself by passing a *complete*
reference to itself, including forms, subforms, etc. I say this because
*if* the calling control is on a subform (not in this particular example,
but on other forms), then a simple reference to it's form (Me.Name) won't
work because the subform's name is not exposed to the calendar form.
Therefore, I would need to reference the control through its lineage.
i.e. I would need to reference its parent or its parent's parent or
whatever. Maybe the solution here is to make this input variable
*optional* in the calendar form so that I am not always forced to use it,
paticularly in cases where the control is burried.

2) I need to make the calling control's BeforeUpdate event *public*,
otherwise the calendar form won't see the procedure. As mentioned above,
I could make the reference to the control optional and then execute the
calling control's BeforeUpdate event procedure *only if* a reference to
the calling control has been passed. Using this approach, I would need
to make the BeforeUpdate event *Public* only for those controls where I
specifically want the BeforeUpdate event to fire.

If you have better alternatives to the above, please let me know.
Thanks.

Also, if you know of a convenient function (or property??) that will
return the complete reference to any control, please let me know.

Mike,
After passing the date value, "call" (force) the BeforeUpdate event...
(use your own field names)
[YourDateField] = [TheNewDate]
YourDateField_AfterUpdate
hth
Al Camp

I'm using an Access XP form as a popup calendar that enables the user
to select dates and thereby pass the date data to various bound text
box controls on other continuous and non-continous forms and subforms
and subforms of subforms...

On one of my "regular" continuous forms, I want to immediately detect
when a new value entered into the text box falls outside a date range
defined by two other unbound text boxes in the header section of the
same form. Once this condition occurs, I want to undo the change,
prevent any other changes, and notify the user that a different date
needs to be entered.

The problem is that when the date is *programatically* passed to the
text box control, the text box doesn't "know" it has been populated, so
the *text box's* BeforeUpdate event does not fire. The same is true
for the text box's Dirty, Change and AfterUpdate events. The only
event I have found so far that detects the change is *form's*
BeforeUpdate event. However, this event occurs later than I prefer.
(Also, worth noting is that the form's Dirty event does not fire in
spite of the fact that the form "knows" to fire the BeforeUpdate
event.)

I've also tried the text box's LostFocus event to compare the old and
new values, but this technique gets cumbersome and also requires that
the control lose focus after it's value has changed and this isn't
necessarily going to be true.

Is there a preferred method, for the above scenario, to immediately
detect when the value of a control has been programmatically changed?

 

[MikeC]

Al,

Sorry for any lack of clarity in my previous posts.

I developed a 95% solution and then I re-read your post and realized that I
mis-read what you had written. Here it is again:

After passing the date value, "call" (force) the BeforeUpdate
event... (use your own field names)
[YourDateField] = [TheNewDate]
YourDateField_AfterUpdate

Originally, I had thought you were referencing the *control* and then I
realized that you were actually referencing the bound *field*. I actually
prefer this approach if it forces the BeforeUpdate event to occur.
Conceptually, this seems to work for the first line of code above, but not
the second. For the second, I would need to reference the *control's*
BeforeUpdate event. I'm assuming that "AfterUpdate" was a mistake. If not,
please elaborate.

Just for a simple test, I added a temporary command button to my form to
execute the below code:

ctlTxtBox = #10/17/2004#
Me.[TMS_DATE] = ctlTxtBox
Call txtDate_BeforeUpdate(False)

This particular snippet of code works just fine, except that this example
does not seem to translate well to my application.

The code that generates and passes the date is executed in the module behind
the the popup calendar form. This is the purpose of the calendar form. So,
to achieve the above goal, I need a generic way to reference the *field* for
any given text box and then to somehow pass the data to the underlying field
instead of to the control. This is where I am experiencing problems. I've
been trying the below variations using Eval:

(Note: ctlTxtBox is a public variable referencing the text box that called
the calendar form.)

Dim strCtlSource As String

strCtlSource = "Forms!" & ctlTxtBox.Parent.Name & ".[" &
ctlTxtBox.ControlSource & "] = " & "#" & Screen.ActiveControl & "#"

Eval (strCtlSource)

Eval ("Forms!frmTimeEntry.[TMS_DATE] = #10/17/2004#")

Eval (Forms!frmTimeEntry.[TMS_DATE] = #10/17/2004#)

Eval ("Forms!frmTimeEntry.[TMS_DATE] = " & #10/17/2004#)

....and Eval does absolutely *nothing*. I get no errors and I use error
trapping everywhere. The above code just runs and the value of the field
remains *unchanged*. My string could be incorrect or maybe Eval does not
work in this particular situation. I'm not sure. The rest of the code in
the same procedure runs fine.

However, if I directly execute the exact contents of the strCtlSource
variable (shown below) instead of executing the Eval() function, the field
is successfully updated and the BeforeUpdate event runs using the *new*
field value.

Forms!frmTimeEntry.[TMS_DATE] = #10/17/2004#

This variation which uses Screen.ActiveControl (= selected date from
calendar) also works:

Forms!frmTimeEntry.[TMS_DATE] = Screen.ActiveControl

I do not want to hard-code this section of code. I need to somehow
construct a generic reference that will actually execute from the code
behind the calendar form. Any ideas?

Below is the line of code that I execute to run the BeforeUpdate procedure.
This one works fine.

CallByName ctlTxtBox.Parent, ctlTxtBox.Name & "_BeforeUpdate", VbMethod,
False

Good deal Mike...
I guess I kinda lost you on this one. I thought it was a simpler matter
than it turned out to be.
There are so many "newbie" questions that I often go for the simplest
solution.
I tried to boil you question down to "how can I get BeforeUpdate to
"fire" on a programmatical update?"
Al Camp

...Looks like I found a pretty good solution. I'll try to post the code
later when I have a little time.

In order for the calendar form to fire the calling control's
BeforeUpdate event, I believe the following things need to happen:

1) The calling control needs to identify itself by passing a *complete*
reference to itself, including forms, subforms, etc. I say this because
*if* the calling control is on a subform (not in this particular
example, but on other forms), then a simple reference to it's form
(Me.Name) won't work because the subform's name is not exposed to the
calendar form. Therefore, I would need to reference the control through
its lineage. i.e. I would need to reference its parent or its parent's
parent or whatever. Maybe the solution here is to make this input
variable *optional* in the calendar form so that I am not always forced
to use it, paticularly in cases where the control is burried.

2) I need to make the calling control's BeforeUpdate event *public*,
otherwise the calendar form won't see the procedure. As mentioned
above, I could make the reference to the control optional and then
execute the calling control's BeforeUpdate event procedure *only if* a
reference to the calling control has been passed. Using this approach,
I would need to make the BeforeUpdate event *Public* only for those
controls where I specifically want the BeforeUpdate event to fire.

If you have better alternatives to the above, please let me know.
Thanks.

Also, if you know of a convenient function (or property??) that will
return the complete reference to any control, please let me know.


Mike,
After passing the date value, "call" (force) the BeforeUpdate
event... (use your own field names)
[YourDateField] = [TheNewDate]
YourDateField_AfterUpdate
hth
Al Camp

I'm using an Access XP form as a popup calendar that enables the user
to select dates and thereby pass the date data to various bound text
box controls on other continuous and non-continous forms and subforms
and subforms of subforms...

On one of my "regular" continuous forms, I want to immediately detect
when a new value entered into the text box falls outside a date range
defined by two other unbound text boxes in the header section of the
same form. Once this condition occurs, I want to undo the change,
prevent any other changes, and notify the user that a different date
needs to be entered.

The problem is that when the date is *programatically* passed to the
text box control, the text box doesn't "know" it has been populated,
so the *text box's* BeforeUpdate event does not fire. The same is
true for the text box's Dirty, Change and AfterUpdate events. The
only event I have found so far that detects the change is *form's*
BeforeUpdate event. However, this event occurs later than I prefer.
(Also, worth noting is that the form's Dirty event does not fire in
spite of the fact that the form "knows" to fire the BeforeUpdate
event.)

I've also tried the text box's LostFocus event to compare the old and
new values, but this technique gets cumbersome and also requires that
the control lose focus after it's value has changed and this isn't
necessarily going to be true.

Is there a preferred method, for the above scenario, to immediately
detect when the value of a control has been programmatically changed?