Updating subforms based on a field on the main form

I have a form that is showing a weekly calendar. It has a few different options to move through calendar dates:

1. Simple forward and backward arrows which move the calendar forward and backward one day.

2. Forward and backward arrows with a terminator bar which move the calendar forward and backward one week.

3. A Today button that moves the calendar to today

4. A text box where the date can be entered to jump to a specific date

Also, the appointments that show in the calendar are colour-coded via conditional formatting depending on some flags in the appointments.

I've attached a sample database. It is almost completely working. If you use any of the arrows or the Today button, it works perfectly. But when you click in the text box for the date and use Access's built-in date picker, I have two small problems:

1. Although it jumps to the right date and displays the right appointments, the colouring (via conditional formatting) doesn't take effect until you click on the form somewhere.

2. Related to that. Access seems to be in a bit of limbo until you click on the form. Even if you click the X to close the form, the first click on the X fires the conditional formatting but it takes a second click on the X to close the form.

If you open the attached example and then open frmDispatchCalendarWeekly and use the "back one week" button, you'll see that it works fine. But if you try to use the date-picker to jump back one week (select 2014/10/24), you'll see what I mean.

Thanks in advance.

James
calendar.accdb
jrmcanada2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not sure...
It seems I could not replicate either of your issues,...

I opened the form and used the date navigation buttons to move to Oct 27-28.
As far as I can tell, the conditional formatting displaying fine. (without ever having to "click on the form")

Note: I opened the db in Access 2013

JeffCoachman
0
hnasrCommented:
Try this:
Open frmDispatchCalendarWeekly_Day subform in design view, set Current event o [Event Procedure].
Modify to this code..
Private Sub Form_Current()
    Me.Painting = False
    Me.Requery
    Me.Painting = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hnasrCommented:
Another approach.
StartDate after update event, opens and closes a Modal form.

Private Sub StartDate_AfterUpdate()
    UpdateColumnDates
    DoCmd.OpenForm "Form1", acNormal
    DoCmd.Close acForm, "Form1"
End Sub

Open in new window

calendar-2.accdb
0
jrmcanada2Author Commented:
This works but is very slow.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.