Form calculating until click on form or control

Try moving your code from the control's Change Event to it's After Update event.

Just curious, ...
If you removed the "Totals Row", does this work as you would like...?

Instead of including the WHERE clause in the query that is the recordsource for the datasheet, have you tried just setting the datasheets Filter and FilterOn properties?

I would agree with Miriam that the Change event is not the right place for this update, as it will change with every character you type.  Use the AfterUpdate event.

if Len(me.txt_SentenceDate) = 0 then
     strCriteria = ""
elseif isdate(me.txt_SentenceDate) = false then
     msgbox "Sentence date must be blank or a valid date!"
     Exit Sub
Else
    strCriteria = "[Sentence Date] = #" & me.txt_SentenceDate & "#"
Endif

Me.frmqryMainSortSubformPSITab.Form.Filter = strCriteria
me.frmqryMainSortSubformPSITab.Form.FilterOn = Len(strCriteria) > 0

Just curious, ...
If you removed the "Totals Row", does this work as you would like...?

No.  As far as that strategy goes, I've tried a bunch of combinations.

I tried making it a continuous form (which can't use the built-in totals row), and added an unbound calculated text box in the footer, but that total, as well as the conditional formatting still doesn't show up until clicking somewhere.

I also tried just keeping the datasheet with no calculated total whatsoever to see if the conditional formatting would refresh on its own...but that was a no go too.

I also tried getting rid of the  conditional formatting, to see if the total would appear, but no go there too.

Have you tried the After Update event instead of the change Event?  

The change event fires as the user types in data (keystroke by keystroke), however the data in the underlying field is not updated until the user actually leaves the control (ie: sets focus somewhere else).

See this article for a more detailed explanation:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_10162-What-is-in-a-field-Value-vs-Text-and-the-differences-between-form-data-and-table-data.html

Try moving your code from the control's Change Event to it's After Update event.

I actually used to have it there before, but changed it for the following reason...
In the date field on the main form, the date defaults to todays date on form open.  The users have always just changed that date by using the calendar control.  I don't believe anyone types it in manually.  So the date always winds up being valid.  When they change the date using the calendar control, it doesn't fire the update event yet until they click in another field.  So the records in the datasheet subform wasn't updating until that happened, which was undesirable.  I want the records to update as soon as they pick the date.  So what I did was, put in the on-change event of the date field a txtcomments.setfocus.  The comments field is the only other field on the main form.  So the onchange even would fire after-change, which switched focus to the comments field, which fired the on-update event, which gave the desired results.

I figured that it was cleaner to just put the whole thing in the change event.  Since the users don't type in the date field, it has always worked fine.

However, I will keep it in the onupdate event per your suggestion.  However unfortunately, this doesn't fix the problem.

Instead of including the WHERE clause in the query that is the recordsource for the datasheet, have you tried just setting the datasheets Filter and FilterOn properties?

I saw this suggestion on another forum, but didn't want to try it because my query had subqueries and the like.  But I just spent some time changing it now, leaving out the subqueries to just return the essential main fields,  and unfortunately, even with the filter (instead of the where clause) it behaves exactly the same.

uggghhh.  This is driving me crazy.

Thanks for your suggestions so far.  I really hope this can be figured out.  Is there any way I could simulate a mouse click?  It's not really how I wanted to do this, but if there isn't a clean way, then I don't know what to do???

For kicks, try force-saving the record in the change event:

Docmd.Runcommand acCmdSaveRecord

Select allOpen in new window

Can you post a sample database?

Have you tried just setting the focus to the subform via code after the requery?

Me.frmqryMainSortSubformPSITab.Form.setfocus
me.frmqryMainSortSubformPSITab.Form.ControlName.setfocus

For kicks, try force-saving the record in the change event:

Tried the save command, but it didn't work.  But thanks for the suggestion.

Can you post a sample database?

I just asked my supervisor if I could send a copy of the database.  I told him that I would take out all sensitive information, put in fake names, etc., but I still got a no.

I work for the State of NJ (Judiciary branch at an NJ Superior courthouse).  I assume that has something to do with it.  I don't understand why I can't just send a stripped down version minus senstivie info, but I think it has something to do with state property or something like that.  Anyways, it doesn't matter what I think.  The powers that be said no.  I would love to work out a solution, but I understand if you can't continue to help without a sample copy of the database.

matchorno,

We are familiar with many different scenarios which don't allow users to upload a database, don't sweat it.  It just makes it easier to help you if we can actually test various ideas, rather than suggesting ideas to you to test.

Have you tried just setting the focus to the subform via code after the requery?

Yes...I tried setting the focus to all kinds of controls and still nothing.

Just confirming that you understand that you cannot set the focus to a control in a subform programmatically, without first setting the focus to the subform, right?

Try my code (above) and change the "ControlName" to the name of one of the controls in your subform.

Just confirming that you understand that you cannot set the focus to a control in a subform programmatically, without first setting the focus to the subform, right?

Yes, I tried your code and witnessed the focus changing to the control.

This subform is one of two subforms on a 2 page tab control.  I even tried switching the focus from this tab page to the other page and then back again...no go.

Is there a reason why it updates when you open the form for the first time?  It shows that it's calculating, but then quickly show the conditional formatting and total value.

Sorry, I'm out of ideas!

No problem.  I thank you for your time and suggestions.

matchorno,

I'm out of ideas too - but will forward this to some other Experts

Yes, I tried your code and witnessed the focus changing to the control.

This subform is one of two subforms on a 2 page tab control.  I even tried switching the focus from this tab page to the other page and then back again...no go.

That is highly odd

I get the same calculating message until I click somewhere

So, if in your Change() event, you put
Forms!frmSomeForm!SomeControl.SetFocus
It fires, and the cursor actually goes to the control -- but the desired results don't happen until you MANUALLY click into yet another control?
Is your SetFocus code moving the focus to the same control that works when clicked manually--or a different one?
Are there other events -- like LostFocus or GotFocus -- that may also be occurring when that manual click occurs, but not when you move the focus programmatically?

You do not have permission to upload any verison of the production db -- but can you construct a completely new sample db with forms, tabs, subforms and tables that have no relationship to the production db -- but that still display the bug you are encountering?

The act of building such a sample may lead you to your own solution.

Subforms on tabs can be very funny things.  I have a pair of Graph objects in a subform that are on a tab.  After they get updated, there are some hoops that I have found I have to jump through to get them to update and display

Private Sub gphJRPreviousWeek_Updated(Code As Integer)
On Error Resume Next
Dim Wait As Double
Wait = Timer
While Timer < Wait + 2
   DoEvents  'do nothing
Wend

Me.gphJRPreviousWeek.SetFocus
Me.gphJRYTD.SetFocus
Me.txtHolder.SetFocus
End Sub

Select allOpen in new window

The 2 second pause-up is a required element as well as moving the focus, not once, but twice, and moving it a placeholder control that is itself NOT on the tab control.

So, in your Change() you may want to try pausing up the code for a short interval and then move the focus twice -- and leave it on a different control.

So, if in your Change() event, you put
Forms!frmSomeForm!SomeControl.SetFocus
It fires, and the cursor actually goes to the control -- but the desired results don't happen until you MANUALLY click into yet another control?

That's correct.
I think it has more something to do with the mouse-click than the actual focus.
I actually just tried something just now.  I tested it out, and I don't even have to click within the form or field...I can click just on the Access ribbon and it updates.  Then I went even further...I clicked only on my taskbar...not even in the Access application...and it updated.  So it's almost like it has something to do with the updating of the entire form.  Before you ask, no, there is no code in that event.  I also tried me.dirty=false in the onChange...but no effect.

Is your SetFocus code moving the focus to the same control that works when clicked manually--or a different one?

I tried just about every combination that I could think of.

Are there other events -- like LostFocus or GotFocus -- that may also be occurring when that manual click occurs, but not when you move the focus programmatically?

That would make sense...but unfortunately, no.

You do not have permission to upload any verison of the production db -- but can you construct a completely new sample db with forms, tabs, subforms and tables that have no relationship to the production db -- but that still display the bug you are encountering?

That I could probably do.  Good idea.

Subforms on tabs can be very funny things.  I have a pair of Graph objects in a subform that are on a tab.  After they get updated, there are some hoops that I have found I have to jump through to get them to update and display

Tried your code out (changing the control names of course), but still no go.  Like I said, it seems to be related to something else...but I can't put my finger on it.

Here's another experiment to try.
Add a button to the form
Take the code that is in your OnChange event, and put it in the button's onClick event.
Disable the code in the onChange event.

When you click the new button, does the problem manifest as well?
Or do you get the desired result?

OnChange can be a funny event to work with when you are requerying a parameterized query.
Until AfterUpdate is complete, the value of a control hasn't really changed--you could still hit ESC and undo the change.  So while an OnChange event captures that you are altering the value in a control, that change is not committed to the database until the control loses the focus -- and even then can still be undone until the record is saved, the form is closed, or the form is navigated to a different record.

Another experiment to try is to put a hidden unbound textbox on the form.  Have your onChange event alter the value of this textbox.  Point your parameterized query at this textbox instead of the control it presently is aimed at.  When your requery is issued, the hidden unbound textbox will have no pending changes to muck up the works.

I finally got it to work...but I had to use a workaround that I'm not too keen of.  But if that's all I can do, at least it's working.  Maybe this might help figure out what is going on???

I simply built a dummy form that is sized to be almost invisible.  It is important to make it a pop-up form so it remains small.  That makes the visual effect a lot more seamless.  On the onChange event, I put:

Me.frmqryMainSortSubformPSITab.Form.Requery
DoCmd.OpenForm "frmDummyBlankForm"
DoCmd.Close acForm, "frmDummyBlankForm", acSaveNo

Select allOpen in new window

You can't really see the blank form opening/closing, but you sometimes see a little flash.  It also of course slows the code down a bit, but I'm not too worried about that at this point.

And really, you don't even have to close the dummy form for it to work.  You can just open it up and then it refreshes right away.  I of course put the code to close it anyway so the user doesn't have this new form on their screen.

Another weird thing about this is that the dummy form that I'm opening HAS to be MODAL for the calculation and cond formatting to update.

I'm hoping that maybe this might point you guys in the right direction...maybe help point you in a direction to figure out what part of this is actually making it work.

I don't mind doing the workaround at this point.  But something cleaner might speed things up a tad...and just make me feel better all around :o)

Thanks again for all of your help.

Hey,

Did you try either of the experiments from my last post?
What your workaround is doing is forcing your present form to lose the focus -- and therefore things like AfterUpdate are going to occur, and changed values will be committed to the app.

Another experiment.
Open your dummy form and leave it open
Throw
DoCmd.SelectObject acForm, "frmDummyBlankForm"
into your OnChange event

Is it the act of getting the focus off the subform that gets things going?

Here's another experiment to try.
Add a button to the form
Take the code that is in your OnChange event, and put it in the button's onClick event.
Disable the code in the onChange event.

I actually did this at one point.  And yes it does work...But I don't think it has anything to do with the code being behind a button.  It's working because I'm clicking somewhere in the database.  I tried keeping the code in the change event, and just put a button that does nothing.  When you click on the dummy button it refreshes the form...once again, I believe it has something to do with the clicking.  I tried setting the focus to this button, figuring it should accomplish the same thing...but unfortunately it didn't.  I was thinking of just making the users press this button...but their gonna moan if they have to go backwards and do more work.  And actually, if they forget to press the button, and update the old recordset that was there from the last update, they could mess something up in the database.

OnChange can be a funny event to work with when you are requerying a parameterized query.
Until AfterUpdate is complete, the value of a control hasn't really changed--you could still hit ESC and undo the change.  So while an OnChange event captures that you are altering the value in a control, that change is not committed to the database until the control loses the focus -- and even then can still be undone until the record is saved, the form is closed, or the form is navigated to a different record.

I did try putting the code in the on-update and forcing that fire by putting  setfocus to another control in the onchange event.  On most of my tries, I tried keeping it in the onchange event as well as moving the code to the onupdate, just to make sure.

OnChange can be a funny event to work with when you are requerying a parameterized query.

I also tried basing it on a filter instead of a parameterized query, and kept getting the same problems despite my experimenting.

Another experiment to try is to put a hidden unbound textbox on the form.  Have your onChange event alter the value of this textbox.  Point your parameterized query at this textbox instead of the control it presently is aimed at.  When your requery is issued, the hidden unbound textbox will have no pending changes to muck up the works.

This sounds like something good to try.  I'm leaving work for the day, but will give this a shot in the morning.

I really do appreciate all the time you have put into this, and the suggested solutions.  They all seemed like great ideas, but just didn't seem to work for whatever reason.  I'm really curious as to WHY it behaves the way it does...like why does clicking in the taskbar work, or why does opening up a modal form work.  But I suppose it may remain a mystery.  At least I have it working with one workaround, even if it's not the most ideal one.

Thanks again and have a good evening.

matchorno

Another experiment.
Open your dummy form and leave it open
Throw
DoCmd.SelectObject acForm, "frmDummyBlankForm"
into your OnChange event

I think I might of mentioned (I know I get pretty wordy at times :o), that I did try just leaving the form open.  I didn't even have to add the code

DoCmd.SelectObject acForm, "frmDummyBlankForm"

Select allOpen in new window

and it worked anyway.  Does a newly opened form automatically get focus when it is opened?  Because it works just doing that.  Or do you have to select it with the code you threw in there?

Is it the act of getting the focus off the subform that gets things going?

The subform never does get the focus.  Unless it does automatically when I requery it.  The date that is changed is on the main form, when it changes, the subform changes it's records, and that's when I need to update the formatting, etc.
I do believe that it has something to do with this...maybe it's when the main form and subform loses focus?  It would make sense, since just clicking on my taskbar worked.

Except, why when I click on the form itself (whether it be the main form or subform) or any control contained on either of them, does it update?  In a way, that's not losing focus but setting it.  And what about all the setfocus code that I tried that is not working.  Why does the focus changing have to be done with the mouse? ?? sheesh.

well, now i really better get going.  I'm not getting paid for this anymore :o)  I will try that other solution tomorrow.  Thanks again.

Does a newly opened form automatically get focus when it is opened?

Yuuuuuuuuuuup

That's why I wanted you to try and manually open and leave open the dummy form and then throw the focus to it through SelectObject and perhaps Forms!frmDummyBlankForm!SomeDummyControl.SetFocus

I'm really curious as to WHY it behaves the way it does...like why does clicking in the taskbar work, or why does opening up a modal form work.

All of these things cause SentenceDate and frmQryMainSortParent to lose the focus and to permit the application to use the new value.  When you issue your requery, Access is aware that the value of SentenceDate has changed, but because you haven't definitively moved the focus out of control (off of Access completely i.e., the taskbar or onto a different form) it waits for you to either commit to that change (by moving the focus off that control) or bail on it.

I suspect that pressing ESC when it is all paused up will revert the changed value back, and permit the requery to complete.

And having all of it on a Tab control makes stuff extra complex, because Tab controls themselves have OnChange events that need to be taken into account.

Is SentenceDate a bound control?  That is to say, changing it changes some value in a table?  Or is it an unbound control?

Looking at your code

Me.frmqryMainSortSubformPSITab.Form.Requery 

Select allOpen in new window

I am thinking it may be a bit squirrely too.

frmqryMainSortSubformPSITab is the tab control, is it not?  If so, then the .Form.Requery bit is probably not right.

What sort of error handler is in the OnChange? Hopefully, as you are testing, none at all.
On Error Resume Next
will keep any meaningful 'gronk' from bopping you!

I am thinking you need
Me.frmqryMainSortSubformPSI.Form.Requery

Testing...

I just wrote out a long long response and it didn't seem to go through.  This also happened to me last night.  So testing here to see if I can get a post up.

Go figure...the posting is now working after I already wrote out my super long response to the previous questions/suggestions.

But in any event...maybe it's not so important at this point.  I ran across something really werid (to me at least), that may lead someone in the right direction...I feel like the answer is so close...

I took all code out of the onchange event of the unbound sentencedate text box and created an unbound text field on the main form.  Instead of the onchange event, I put the code into the OnLostFocus event of this new "dummy" unbound text field.  And sure enough, when I tab out of the new text field, it updates the subform AND the formatting.  But here is the catch...

I have to first click inside of the new text field, and then when I tab out of it, everything updates.  So here is the procedure...I change the sentenceDate field to a new date, nothing happens as there is no code for the sentenceDate.  I now click inside of the new dummy text box.  Still nothing happens.  Then I tab out (with the keyboard) of the text box which fires the OnLostFocus event, which runs the code to update the subform...and the other items refresh as well.  So note that I don't have to click anywhere after the subform changes, which I've had to do in the past.  But I do have to click inside it, before the requery code is run.

I have no idea what's going on here, but maybe this shines some light on what's going on?

I can't believe this...
I'm pretty sure that I've narrowed it down to the sentence date text box...and even further narrowed it down to the datepicker calendar.

I'm boggled that it was this small of a detail...
I moved my code from the onchange event to the afterupdate event so I could type in a date without something firing with every character.  So instead of using the built-in datepicker to choose my date, I manually typed in a date.  When I tabbed out of the field (no mouse click necessary), EVERYTHING updated.  So it's gotta be related to how the datepicker works somehow, right??

I would really rather use the date picker if possible, everyone is soooo used to it.  Is there something else I can use...I think they got rid of the active x calendar control, right?  Unfortunately I can't really use any third party software due to policy here.  Well, it's really due to what can be bought from the budget.  If I can find a free calendar picker control that's from a decently reputable third party, I might be able to get/use it.

Or maybe someone might have a solution now that we know it has something to do with how the datepicker works?

:)

You are now discovering why OnChange wasn't the happiest place for your code to run -- which @mbizup suggested at the very beginning :)

No matter, your users have gotten used to the datepicker, but that leaves you in a bit of a pickle.  You HAVE to get the focus off of SentenceDate for the db to see the changed value--but your users don't want to use the UI to do that.

Now, I run a mixed 2003/2010/2013 environment.  The old ActiveX calendar control can be made to work--but involves copying files to System32 and/or SysWOW64 and merging a bunch of settings into the registry--and I don't know that you'll be able to get that done in your environment.  I can supply the registry files if you want them.

More simply, you could have the OnGotFocus event of SentenceDate open your 'dummy' form, and have a textbox with the datepicker and a command button on it.  The command button would update SentenceDate on the main form, run the requery and close the dummy form.  It fools the users into doing on a modal form what they don't want to do on the main form -- click a control to indicate that they have definitively updated SentenceDate.

A fourth experiment to try is to have the OnChange event test the new value of SentenceDate with IsDate(), and if true, move the focus to a new control, fire a sub to run the requery, and then have the sub return the focus to wherever it needs to go

You haven't directly commented on some of the other things I have mentioned.  Is that because you've tried them and they were dead-ends?

You are now discovering why OnChange wasn't the happiest place for your code to run -- which @mbizup suggested at the very beginning :)

I'm not sure if this had anything to do with the problem at hand.  I had the code in the onupdate for a while.  Why I didn't like that was because that event didn't run when the user changed the date with the date picker.  It didn't update until you tabbed out of that field.  Tabbing out of it did update the subform, but didn't update the rest anyway.  The only reason I changed it to on update at this time was because the date picker was causing the problem and so i couldn't have it firing each time I typed in a number.  But hopefully I can get it working without them having to do that.  I'm sure I could be missing something.  I can't claim to understand everything that's going on here.  I've been using Access/VBA for a few years, but am by no means an expert...which is why I'm here :o)

You HAVE to get the focus off of SentenceDate for the db to see the changed value--but your users don't want to use the UI to do that.

When the user changes the date with the date-picker, the sentence date changes right away and with the onchange event, the subform requeries right away.  I can get the focus off of the SentenceDate with a simple setfocus statement.  Or I could even keyboard tab out of it...but this doesn't work...only a click works. Or I could put the code in the onupdate event, then I could put in the onchange event "if isdate(txtsentencedate), then setfocus somewhere else" to fire the update event.  But that didn't work either.  I even got it to work by clicking back into the sentence date field...i.e., I changed the sentence date with the date-picker. This updated the subform.  The focus never changes , thus the sentencedate retains the focus.  The subform updates, but nothing else.  Just for giggles, I clicked on the sentencedate field which already had the focus...and that worked!  go figure :o)

Now, I run a mixed 2003/2010/2013 environment.  The old ActiveX calendar control can be made to work--but involves copying files to System32 and/or SysWOW64 and merging a bunch of settings into the registry--and I don't know that you'll be able to get that done in your environment.  I can supply the registry files if you want them.

Unfortunately, I'm not allowed to mess around with the registry at all.  But thank you for the offer.  I certainly appreciate it.  If it was up to me, I'd love to give it a go.

A fourth experiment to try is to have the OnChange event test the new value of SentenceDate with IsDate(), and if true, move the focus to a new control, fire a sub to run the requery, and then have the sub return the focus to wherever it needs to go

Just gave this a whirl, but unfortunately it didn't work.  It was a good idea though, I thought it just might do the trick.

You haven't directly commented on some of the other things I have mentioned.  Is that because you've tried them and they were dead-ends?

Yeah, I'm really sorry about that.  I actually put together a long reply, responding to all your comments last night and thought I hit submit, but when I came back on this morning, it wasn't there.  So then I put together another long response, submitting it this morning...but alas, that didn't show up either...it just disappeared.  After that, I just couldn't bring myself to writing it all out again.  But to answer your question, yes, still nothing seemed to work unfortunately.

More simply, you could have the OnGotFocus event of SentenceDate open your 'dummy' form, and have a textbox with the datepicker and a command button on it.  The command button would update SentenceDate on the main form, run the requery and close the dummy form.  It fools the users into doing on a modal form what they don't want to do on the main form -- click a control to indicate that they have definitively updated SentenceDate.

This would definitely work and sounds like a good workaround.  But I fear it would take longer than the other workaround.  If I required them to click on a button, they would string me up.  I know that they would eventually get over it.  Using that idea, I could also forget the dummy form altogether and just put the button on the main form.  The subform could be disabled by default, but when they clicked on the button to verify sentencedate, the button could simply enable the subform and perform the requery...that would save opening up another form.  But again, they'd have to hit a button.

I think what I'll do at this point is to just use my dummy form.  I'll make it nice and small.  I'll put it far in one of the corners somewhere, so the "flash" of opening/closing isn't too noticeable.  At this point, it seems the easiest workaround until a "cleaner" solution can be found.

I'll do this for now, and then maybe when I get caught up on things, I can work on putting together a simple database at home which could produce the same problem.  That way someone could take a hands-on look at it.  I'm sure that's much easier than typing back and forth.

I really do appreciate all of your efforts...your time, your suggestions and dedication to solving this issue.  If you think of anything else, I'd be happy to try it out.

I can't believe a little date picker feature threw me so far off the tracks!! :o)

Thank you again.

I built a sample to test things.
Putting the requery in the OnChange just squirrels everything up massively and I can't get the subform to requery without clicking around a bunch of places.

On the other hand, with the requery bit in the AfterUpdate, and an IsDate check in the Change event, it works like a hot damn.

Typing things manually into the SentenceDate isn't much fun, because as soon as you get ANYTHING that's a valid date the code fires -- but if they all use the DatePicker, it shouldn't be a problem.

The sample is in working order -- but uncomment the 'bad' in OnChange and comment out the 'good' in OnAfterUpdate and see if it is broken in a similar fashion to your production db.

In testing it on A2013, I looked at clicks involved.  I needed one click to get the focus on SentenceDate and then one to activate the datepicker.  If you take the advice about using the modal form to change the date, the OnGotFocus will open the modal form (no new clicks) the click to open the datepicker will still be required, and if the modal form's textbox has an onChange to test for isDate(), you won't need a click to close it.

Same amount of work involved -- so hopefully no user whining.  The bonus is that they DEFINITELY will see that they have changed the date--and that visibility may be a good thing.

You MUST get the requery out of the OnChange event.  That is the root of the problem
DateRange.mdb

Here's a sample with a modal form to do the DatePicker bits
DateRangeWithModal.mdb

I don't know why, but if I put that requery code in my onchange event, it does indeed update the subform.  I can't say i know why or how...i must have something else set up a different way.  I tried stripping down my code to see what else could be allowing it to work, but I couldn't get it to not work.  But I see that it doesn't work in your database.  I do have to have a  me.refresh after the requery for mine to work.  But I threw that into your example and it still didn't work as you noted,.

Nonetheless, I already tried the same type of code that you have...the same exact method, jumping it to a different control during the onchange, then putting the requery in the onupdate event.  Either way works for me, whether I use the onchange event or the onupdate event.  For sake of argument, I'll start keeping it in the onupdate event from now on, just so we can all rule that out as the problem.

However, the problem at hand remains.  I can get the subform to requery using onchange or onupdate.  That isn't the problem.  It's that the conditional formatting doesn't update until I click somewhere.  I'm attaching a modified version of the first database that you sent me.  I added some basic conditional formatting.  Thankfully I was able to reproduce the same problem.

Open the main form and choose 8/21/13 from the date picker.  I have a conditional formatting that says, if the i.d. # > 10106, then highlight the date field with a red background.  So choose 8/21/13, you will see the data in the subform change, but the formatting won't kick in.  And you'll see "calculating" at the bottom-left of the status bar.  Go ahead and then click ANYWHERE on your screen...on the main form, on the subform, on the sentence date, on the button that you have visible, on your taskbar, etc..  You will then see the conditional formatting kick in.

As far as the modal form goes...I'll have to take a look at it later.  I leave work at 4:00pm.  I know this is really nitpicky...but technically without the modal form it's one less click than you mentioned.  On form open, the focus is sent to the sentence date.  And I usually (i think) reset the focus to the sentencedate after most events.  If the sentencedate already has the focus, the date picker icon will already be visible.  So you don't have to click in the date, and then click the date picker icon.  I know that's really nitpickin...but hey, a click's a click. :o)
DateRange-ConditionalFormatting.mdb

In Access 2003, if I change the date to 21-Aug-13 by keyboard in your new sample, BAM! it all changes.
The requery and the conditional formatting fire immediately.
The same result occurs if I change it BY KEYBOARD in Access 2013

The datepicker, however, does NOT make the conditional formatting happen.

Interesting!
I'll have to play with it a bit

In Access 2003, if I change the date to 21-Aug-13 by keyboard in your new sample, BAM! it all changes.
The requery and the conditional formatting fire immediately.
The same result occurs if I change it BY KEYBOARD in Access 2013

The datepicker, however, does NOT make the conditional formatting happen.

YES!  Exactly.  That's the same issue that I am having.  Mine behaves the same exact way.  Sorry...I didn't realize the problem would be that easy to reproduce or I would've uploaded something like this a long time ago.  I'm glad it is reproducable on your end...I was starting to think that I was crazy.  I am using 2010 if it's worth anything.

Here's another thing...I added a sum text box to the subform.  It's just a simple calculated control that sums up the dateids.  I also put a calculated textbox on the main form (which is how I'm going to do it on my application) which simply refers to the subform textbox value.  Continue to choose 8/21/13 so you can see the formatted records.  You'll see that the sums work the same way as the conditional formatting.  The totals (i also tried just a totals box in the subform, before adding the main form text box) don't update until you click somewhere.  I've attached a modified version of your database with the added sum to see if you wanted to play around with that too.  This is exactly what I'm trying to do...update a sum and update conditional formatting.

The modal form works, but I just don't think I want to use something like that.

The bonus is that they DEFINITELY will see that they have changed the date--and that visibility may be a good thing.

I don't think that having them pick a date on the main form is compromising the integrity of the database at all.  They already definitely see that they have changed the date.  I think I'm probably missing something that you are trying to explain...it wouldn't be the first time :o)

I think the best workaround for now, is to just quickly open/close a tiny form in the corner.  A little flash (that they may not even see) isn't so bad and it seems to work quickly enough.

But boy, am I curious as to what's going on.  If you figure anything out, please let me know.  It would make my week for sure :o)

And maybe at some point, I can upload a simple database to show you how I can get things to update in the onchange event.  I just checked it again, and I took out the requery and it STILL updates the subform.  It seems that all it needs is a me.refresh.  I understand what you mean...It just doesn't seem like it should work like that.  It must have something to do with the way the queries/tabs/form/subforms are set up.  But I'm pretty sure that this is unrelated to the main problem since we could reproduce it in your example database.  But like I said before, for sake of argument, I will leave things in the on update event.
DateRange-ConditionalFormattingA.mdb

BTW, if you do a google search, you will see other forums where people mention similar problems (calculated field doesn't update until click...with the word "calculating" hanging in the status bar).  However, they either had a different problem, or it was unsolved.

I'm starting to wonder if this could possibly be a microsoft bug?

:)
Deprecating the ActiveX calendar control and replacing it with the datepicker was meant to be a 'feature.'  I don't appreciate it much.  I don't know that I'll have time to poke at the problem today.  Things literally exploded at work today and I have messes to clean up.

Clearly, the use of the datepicker does not invoke events in the same fashion that the keyboard does.  You could, since you have a sample, send it to MS as a bug and see if they can supply you with some joy.

You could, since you have a sample, send it to MS as a bug and see if they can supply you with some joy.

Thanks for the tip.  I would have never thought of that.  I couldn't find anywhere to post a sample database.  However I did post the question on their forum, and offered to post the sample database if need be.  I'll let you know what comes of it.

Thanks again Nick67!!!

MS makes bug submission tough to do.  You can start a request here
https://support.microsoft.com/getsupport/hostpage.aspx?locale=en-us&supportregion=en-us&ln=en-us&pesid=13604&tenant=ClassicCommercial&sd=gn&oaspworkflow=start_1.0.0.0&wf=0&ccsid=635131018640685433

All one line URL of course.  Down at the bottom is a Start Request button.  They will ask for a credit card number to start a request.  They don't bill for real bugs.  The forums are nice -- and I have posted the sample up there as well, but the forums are the community and not really MS -- so a real bug will find no joy there.

I posted it on one of the microsoft forums and got a similar response.  They suggested to put me.recalc in the on-change (or on-update...works the same either way).  I threw that into the database you originally posted and I see that it works.  It solved part of it at least...the conditional formatting.

I see that you added...dummyvar = txtcalculatedField...that then fixes the calculated control.  Pretty elegant workaround.  I like it.
I did also figure out that you can just set the focus to the calculated field after the me.recalc and that will update the sum as well.\
But I think I like the dummyvar idea better though since there is no moving the focus around the screen.

Now, here's where I'm going to be a pain in the back side...
This does NOT work for my database.  I tried a couple things...
First I tried building a small sample database from scratch.  It is very similar to the one that you posted a few days ago.  Using the same techniques that you've discovered, I can get the calculated control to update, but NOT the conditional formatting.

Figuring that I had to be doing something wrong, I built another database from scratch.  This time I made it practically identical to yours.  And still...although the calculated control now updates right away...the conditional formatting still does not (until click).

Hoping to get to the bottom of this once and for all, I'm attaching 3 small databases.  

The one called "matchorno.mdb" is the one I built from scratch.  It's a little different than yours, but it should technically work with the same methods.

The one called "DateRange-ConditionalFormatting-nick67.mdb" is the one that you made + the new code (with some minor adjustments on my part).  This one works as it should.

The one called "DateRange-ConditionalFormatting-matchorno.mdb" is the one that I made identical (at least I think) to the "DateRange-ConditionalFormatting-nick67.mdb" database that I just mentioned.  Although identical, the CD does not update until click.

The only difference I can think of at this point, is that you made yours in 2003, correct?  I am using 2010.  Could it be a bug unique to 2010?

I know you're probably starting to get sick of this thread by now (if I am, then I know that you must be too :o)  But I'd really appreciate it if you wouldn't mind taking one more look at these databases...to tell me if they work (or don't work) as I described on your end as well...and/or if I'm missing some code or database structure, etc. somewhere that I just keep overlooking.

Thank you again.  I know I keep saying this...but I really do feel now that the problem is about to come to a close.  Seems like it's already half answered.  Just one more small mystery to solve.

Thanks again.
matchorno.mdb
DateRange-ConditionalFormatting-.mdb
DateRange-ConditionalFormatting-.mdb

I'll have a gander at them.
My test sample -- like everything I dev -- was built in A2003.  Very bad and unhappy corruption happens to objects created in A2007+ and then edited in A2003.  Editing wizard-created forms and reports in A2003 in short order causes them to become corrupt and unloadable.  So I build things in A2003, and test them in up-level versions before deployment.

I am tied up today, but I'll have a look as soon as I can.

 I did find just one, minor difference

:)
That's not a minor difference -- it's a major one.
I don't permit users to see or use datasheets.  There's too many ways for the users to foul up the data, and too few ways for me to cut that off at the knees.  Datasheets -- as you now see -- also do not necessarily play too nicely with many of the formatting and event options that you get with other form types.

I am glad you got it figured out.  You may also want to put a KeyPress event in for SentenceDate so that users cannot hand-edit it -- because with the OnChange event as presently structured, that could be a frustrating experience.

I see.  I didn't realize that there was such a big difference between datasheet forms and other forms.  I've never had a problem with them as far as functionality or security goes.  I was able to lock down the one I'm currently using very well.  I've disabled any data or record manipulation through the form's property sheet.  There are many options in the properties sheet that are consistent for all the form types.  And you can control the fields through vba as well.  I guess it's all about what you're using it for and how you're using it.

My datasheet subform is strictly for viewing purposes only.  Besides reviewing the data, the only thing they can really do is double click on a field that then runs code based on what field was double-clicked on.  I would never let a user go back and forth between a normal form and a datasheet form.  But I think in certain situations, it's the nicest way to view a recordset of data.  People are used to seeing spreadsheets.  However I would never use them for data entry or anything like that.  But I don't know if I would throw out datasheet forms altogether.

We've been using this particular database for 3 + years with thousands of records, and have never had any problems with it...at least until we wanted to add some conditional formatting to it :o)  So, it may be time to revisit the use of datasheet forms...especially...well really definitely, if we need any type of CD  :o)

There's always the open form/close form workaround...which to be honest seems just as fast as the .recalc method.  But I find it hard to accept the use of what we call "sleazy" code, when there's a more elegant solution out there.

Thanks again for your patience and help!

It's whatever works for you.  And if it has been working, so much the better.
For me, reports are for displaying data.
Forms are for entering, editing, selecting and manipulating data.
Datasheets are problematic for that stuff in the kind of app I've got.
YMMV

Cheers,

Nick67

Gotcha.  Makes sense with the reports for viewing.  I failed to mention that there are a couple of events that are linked to some of the datasheet fields.  The double click is pretty much all they can do with the datasheet.  It brings up a subform that's populated by the datasheet row, etc.

Anyway...that's neither here nor there.  Sorry to get all defensive.  After reading my comments, I could see how they might appear defensive, and that wasn't my purpose.  I'm more just trying to learn.  So sometimes I "think" out loud too much.

I just want to say again that I really appreciate your help.  I wouldn't have come close to figuring it out without your help.  You are truly an expert, and more.  The original people bailed on the question when it got complicated, but you stuck it through.  You certainly earned and do deserve the points.  I don't really know what the points are for, I just joined this forum to solve this particular problem.  But I hope some good can come from them for you.

matchorno signing off :o)

No biggie.
You can't know my skill level, and I can't know yours--until we discuss things.  It sounds like you've gotten things well sewn-up.  With many Askers, that isn't necessarily the case.  Some Answerers do not take much interest in 'why' and Asker is posing the question that they do.  Others, like everyone who posted on your question (and if you look at their rankings by clicking on their names, you'll see that in fact the folks who did chime in are among the heavyweights) is interested in growing the number of skilled Access professionals.  Before Google Panda, doing a search for 'MS Access VBA...' would throw EE results in the top 5 of the search.  Many of us learned a lot from this site, and return the favor by answering questions.

When you post a question, being able to post an mdb sample is extremely helpful.  Since the February 2012 site redesign, I rarely, if ever, post on anything but Neglected Questions.  For those, I get emails and if they look like a challenge but within my expertise I'll post on them.  So that's why we're chatting :)

It isn't that the other posters bailed on you, either.  It's all volunteer work--and there's only so many minutes in the day, with many questions to answer.  When one heavyweight sees that another is running with a question--and it is progressing--we are as likely as not to simply read the postings but not add to them.

As for the points, 3000 points per month for questions answered means not paying for membership for that month.  At various point levels, EE sends a thank-you T-shirt.  I just received one for hitting a half-million points.  Other than that, they don't mean much.
Glad you got it all working ok.

Cheers
Nick67

Please see both selected answers.  Nick67's answer helped me get on the right track which is why I marked that as an answer...and then I figured out the last portion on my own.  I put a detailed explanation of the problem and answer, summing everything up in my final comment.  So it makes sense to make this final comment the answer since it is thoroughly explained from start to finish.  The combination of these two answers should hopefully assist others who may come across this same problem in the future.