Solved

Updating subforms based on a field on the main form

Posted on 2014-10-31
4
127 Views
Last Modified: 2014-12-04
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
0
Comment
Question by:jrmcanada2
  • 2
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40416335
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
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 40416959
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40417000
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
 

Author Closing Comment

by:jrmcanada2
ID: 40480739
This works but is very slow.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now