Conditional Formatting: DateDiff date based on subform date values: Access 2010

[kyfe]

Hi,

I am tried for a while to get this working, using the skills I have but have not had luck.

Access 2010.

I am trying to conditionally format a control based on DateDiff if a person has called me back. The control to be formatted is in my main subform, but need to reference a value in another subform on the main form.

Currently I can do a plain DateDiff of 10 days on the field, but want to do an expression to only color the control if a person hasn't called us back in 10 days - so taking a value from a subform and applying it to the expression if the checkbox is ticked.

I have attached a stripped down version for you to view.

when the database opens, it autoloads the form IntakeLog which is where I am trying to do the conditional formatting. The entry to view is First Name: Some and LastName: Person. When you select them you will see below the call notes where it explains a bit of what I am looking for.

(Any VBA errors are probably related to the other tables/forms/queries I removed to make the file small to upload - i think I resolved them all though)

 

[JHB]

Should it only be where one of the Callbacks is set to true in the subform?
You have some contacts where the subform is empty, should they also be turned red or ???
Could you show the rules here in the thread, (not in some records in your database)!

 

[kyfe]

Sorry, I should have been more clear in my original post and posted all of the rules in the first posting, that is my bad. I will clarify here:

On the main form we have two subforms. The top form ('subContactsForm') is a continuous form that tracks the 'people' calling into our service. The bottom subform ('Calls') is linked to the to top form through another table which tracks all our calls, the bottom form filters based on the selected 'person' in the top form to show the calls associated to that 'ContactID'. What happens is when a person calls us, we add them to the top form which creates a 'Call' entry into the bottom subform. When we attempt to call a person back we make a note on the bottom subform to capture our 'Call' activity.

We only keep people on top form for 10 days - after that we close them. The catch is we start counting the 10 days from the day they called us OR the last time they called us back.

JHB, you are right that I am only looking at the records that have 'Callbacks' set to true. Where I am stuck is that I can do the DateDiff to the orginal date they called (listed on the top form), but am unable to make the expression also look at the bottom subform and only mark greater than 10 days (which colors the top form date field red) from the last time the person calls me, if they have.

Does this add more clarity? If not I am happy to take another stab at it.

kyfe

 

[JHB]

Try the attached database, be aware of I've change some dates for test.

 

[kyfe]

JHB, Thank you for this, it works quite wonderfully on Load to change the color.

I am wondering though, if there is a way to limit the records that it looks at upon opening. The reason being is that our production DB is a split DB with 5 users concurrently using. Our network isn't the best in the world and we have close to 20000 records. Just trying to find ways to speed up the process a bit, as we really only need to worry about the last 3 months to update.

As well, wondering if there is a way to trigger the code when I add a new 'Call' note only for the associated 'ContactID' so that the Red updates automatically so all users will see it on refresh (right not we need to close open to see conditional formating updates). I almost had it working but it was updating whenever the tickbox was marked true it would flip the 'Contacts' RedNotRed field however sometimes we backdate call notes that were missed that would still put the date beyond the 10 day cutoff so we wouldn't want it to clear the Red coloring in that case.

Thank you for the help you have given so far, kilometers ahead of where I was able to get myself. If there is nothing else to be done, this is better than what we had going. So Thank you.

 

[JHB]

..
I am wondering though, if there is a way to limit the records that it looks at upon opening. The reason being is that our production DB is a split DB with 5 users concurrently using. Our network isn't the best in the world and we have close to 20000 records. Just trying to find ways to speed up the process a bit, as we really only need to worry about the last 3 months to update.

You could use a query to only take a part, (Using the Select Top statement), you know best how many records you would like to get!

..
As well, wondering if there is a way to trigger the code when I add a new 'Call' note only for the associated 'ContactID' so that the Red updates automatically so all users will see it on refresh (right not we need to close open to see conditional formating updates).

You could use the AfterUpdate event for the subform or for some field in it and then test if the criterias are meet, you don't need to run a query for that.

 

[JHB]

The code could be like below, (placed in the subform).

Private Sub Form_AfterUpdate()
  If Me.CallBack = -1 And Me.CallDate > Date - 10 Then
    Me.Parent!subContactsQuery!RedNotRed = 0
  End If
End Sub