The Old Bugaboo -- Updating Textbox2 When Textbox1 Changes

[Ellpee]

SITUATION: Access 2010, Form, two unbound text boxes.


-- Box1 default is Date()
-- Box2 default is a date three to five days later, generated by a custom function. (Considers holidays, weekends, etc.) Function is fine -- tested thoroughly in the Immediate window, produces the correct result.
-- But when Box1 is changed, either via the DatePicker button or by the user typing in a different date, Box2 doesn't update. Have played around with every imaginable (to me) combination of Refresh/Recalc/Requery/Repaint and the Enter, Exit, BeforeUpdate, AfterUpdate, OnChange, GotFocus, LostFocus, etc., etc., events on both boxes.

I think I could write some long, arcane VBA procedure to get where I need to go, but surely there must be a built-in solution to something this fundamental. Anyone?

[RuralGuy]

Have you tried Me.DateBox2.Recalc in the AfterUpdate event of the Date1 control yet?

[Ellpee]

Believe I did, though I'll try again later this evening. Seems to me the problem is that neither picking a new date from the calendar nor typing in a change to the default date (in Box1, that is) seems to trigger the AfterUpdate event. It's been awhile but I vaguely recall having fought this battle before, and MAYBE that time I wound up having to play around with Value and OldValue in VBA to get past the problem. That was back in A2003 days, though, and I really hope A2010 has recognized and dealt with that in some built-in way.

UPDATE: Me.DateBox2 doesn't offer the Recalc option at all. Me!DateBox2.Recalc says the object does not support this method. Just plain Me.Recalc produces no result in DateBox2. (In all of these, it makes no difference if the change to DateBox1 was typed in or "selected in" using the DatePicker calendar.)

[Missinglinq]

...Me.DateBox2 doesn't offer the Recalc option at all. Me!DateBox2.Recalc says the object does not support this method...

I believe that's correct...the Recalq has to be applied to the entire Form, not to a specific Control, Try

Me.Recalq

instead. Or, better yet, simply run the function in the AfterUpdate event of the Textbox1.

Linq ;0)>

[orange]

I just dummied up a form with 2 text boxes Box1 and Box2 both with default format Medium Date
That format gives me a DatePicker.
When I select a Date, I tried to use the AfterUpdate event to add 5 days to the value in Box1 and display in Box2.
Even if I type a Date into Box1, the afterUpdate is not fired?
It does not change the value in Box2 until I actually click on Box2.

[Ellpee]

For MissingLinq, as I wrote, I did also try simply Me.Recalc against the whole form and nothing happened in Box2, same as with all the other variations.

For orange, the "code" is actually the Default Value setting for Box2, which is =getSettleDate(Box1.Value). That custom function takes the Box1date and counts three days out (five if a weekend is involved) and returns the resulting date. It also allows an extra day if there is a holiday within that 3/5-day window. As I wrote, the function itself is fine. I ran it in the immediate window with all sorts of different dates -- with/without weekends, with/without holidays -- and it performed correctly in all cases. My problem is getting Access to recalculate, i.e., re-run the function, whenever Box1 is changed.

If you really think seeing the full code of the function will help, fine, I'll post it, but I definitely do not think the function is the problem -- it's finding the right Box1 or Box2 event to trigger the function any time Box1 is changed.

I'll add something interesting here that might help: Using a couple messageboxes in its BeforeUpdate event, I looked at the .OldValue and .Value properties for Box 1 when changes are made. Lo and behold, they both show the same value! To illustrate, when I changed 2/14/2014 to 2/10/2014, BOTH OLDVALUE AND VALUE FOR BOX1 SHOWED 2/10/2014. Didn't matter if I typed in the change or selected it from the DatePicker. How weird is that? If nobody here has a Eureka moment about this problem, I'm going to just add a couple more invisible text boxes to the form and some additional VBA to capture and evaluate the before and after dates, but it'll really annoy me if I have to do that.

[orange]

I was testing and adjusting my previous post while you responded.
Please see my post #5

[Xipooo]

You could do the calculate in VBA and assign it to the textbox2 value rather than relying on the control source to do it on a recalc.

[orange]

Attached is the code --all of it. In AfterUpdate of Box1???????
2 textboxes Box1 and Box2 (the real names)
there is a label over each box.

[Ellpee]

Since neither BeforeUpdate nor AfterUpdate for Box1 was recognizing there had been a change (i.e., both .OldValue and .Value were showing the same, CHANGED date), here's my successful workaround:

(1) I added two more textboxes, txtStartDate and txtNewDate. They'll be invisible, but I've left them visible below for show'n'tell purposes.
(2) When the form first opens, Box1 defaults to the current date, and VBA calls my function to set Box2 to the appropriate date three to five days later.
(3) In the Box1.Enter event I copy the existing date to txtStartDate.
(4) In the Box1.Exit event I copy the ?new? date to txtNewDate, then compare txtStartDate and txtNewDate. If they are different, VBA calls my function to update Box2.Value.

Would have preferred to get one or the other of the Box1/Box2 events and the .Recalc method working, but this'll do.

#######CODE######


Private Sub Form_Open(Cancel As Integer)
txtSettleDate.Value = getStockSettleDate(DATE)
End Sub


Private Sub txtTradeDate_Enter()
txtStartDate.Value = txtTradeDate.Value
End Sub


Private Sub txtTradeDate_Exit(Cancel As Integer)
txtNewDate.Value = txtTradeDate.Value
If txtNewDate.Value <> txtStartDate.Value Then txtSettleDate.Value = getStockSettleDate(txtNewDate.Value)
End Sub


#####SCREENSHOT#####

[orange]

Good stuff.
In my example I added a button. The button does nothing. It has a clickEvent only to move the cursor out of Box1.
And that allows the afterUpdate event of Box1 to fire, and the proper value to be displayed in Box2.
The me.Recalc is NOT required. I commented it.

Observation: You must physically exit box1 for the afterUpdate event to fire.

Here, attached is the form after clicking the button and below is the button click event code.

Private Sub btnClickHere_Click()
On Error GoTo Err_btnClickHere_Click

'this does nothing
'it serves only to get out of box1
  
Exit_btnClickHere_Click:
    Exit Sub

Err_btnClickHere_Click:
    Resume Next
    Resume Exit_btnClickHere_Click
    
End Sub

[June7]

Would the BeforeUpdate event serve for this? No, I guess not, still have to hit Enter or Tab. Probably the only events that will trigger while still in the box are Change, KeyPress, KeyDown, KeyUp.

[orange]

I'm not sure what event might work. Especially if you populate box1 via a selection from the DatePicker.
I did read a post that said the datePicker wouldn't do the AfterUpdate, but that manual entry of data would---
Well manual or DatePicker DID NOT Fire the AfterUpdate in my tests.

[Ellpee]

Yup, and for some reason moving the cursor out of my Box1 -- clicking elsewhere, tabbing, hitting enter -- didn't have that effect. I can vaguely recall having a similar problem some years ago, also involving date controls and the DatePicker thingie. As far as I'm concerned Gates and his boys have some work to do on that.

Your button is another clever workaround. In my case I didn't want to have the user do or see anything different than he has all along, which is typing in a different Box1 date if he didn't like the default date. Box1's .Enter and .Exit worked out okay in combination with the two hidden textboxes he'll never even be aware of. He'll just think it's all magic, which is always the desired effect!

[Ellpee]

"Would the BeforeUpdate event serve for this?"

No, it didn't appear to. That's where I got the odd result that .OldValue and .Value both showed the same, NEW, value. Crazy, huh? .Change, .KeyPress, etc., all fire as soon as the first activity takes place, so they won't do, as I need the user-provided alternative date to be 100% entered before doing anything with it. This has once again been a frustrating experience. Problem is solved with a workaround, but I sure wish Microsoft could get this whole date control/DatePicker/events/actions thing working in a much simpler, more consistent and understandable way. Meanwhile, folks like us will continue finding ways around their deficiencies.