Change Color Of Sub-Form Row (1 Viewer)

GC2010 · Jul 13, 2019

I have a form with a sub form on it. This sub form shows dates, I want to change the row color to red of any date that is >= 60 days from today and change the rows to yellow for any date that is between >=59 days and >= 30 days from today

I assume I’d use the form_load event but what would be the syntax?

theDBguy · Jul 13, 2019

Hi. If you're using a continuous view form in your subform, then I don't think you can use the Load event to change the rows with different colors. Instead, you may have to use Conditional Formatting (CF). However, CF only applies to one control. So, one popular workaround is to add an empty textbox behind all the controls in the row and apply the CF rule to it.

GC2010 · Jul 13, 2019

I don’t believe the sub form is set to co tinuous view.

Conditional formatting - how would I do that?

Could I set the on load event of the sub form (which is a form itself) and when the sub form loads in the parent form the colored rows will remain?

missinglinq · Jul 13, 2019

The same applies to a Datasheet View Form...which is likely to be the situation (assuming it's not in Continuous View) since your talking about coloring 'rows.' In that case you have to use the same advice, i.e. Conditional Formatting, but either use it to change the color of one Control, probably the Date Control, or to change the color of all Controls in the particular Record/Row.

You can do the Formatting of multiple Controls as easy a single Control.

In Design View

Hold Down <SHIFT>
Select each Control to be Formatted
Click on Conditional on the Ribbon
Under Contion1 drop down Expression Is
In the next box enter you first expression
Click on the Paint Bucket Icon
Select the color for your first expression
Click on Add
Under Contion2 drop down Expression Is
In the next box enter you second expression
Click on the Paint Bucket Icon
Select the color for your second expression
Click on Add if appropriate...or OK if you're through.

Linq :0)>

GC2010 · Jul 14, 2019

missinglinq said:
The same applies to a Datasheet View Form...which is likely to be the situation (assuming it's not in Continuous View) since your talking about coloring 'rows.' In that case you have to use the same advice, i.e. Conditional Formatting, but either use it to change the color of one Control, probably the Date Control, or to change the color of all Controls in the particular Record/Row.

You can do the Formatting of multiple Controls as easy a single Control.

In Design View

Hold Down <SHIFT>

Select each Control to be Formatted

Click on Conditional on the Ribbon

Under Contion1 drop down Expression Is

In the next box enter you first expression

Click on the Paint Bucket Icon

Select the color for your first expression

Click on Add

Under Contion2 drop down Expression Is

In the next box enter you second expression

Click on the Paint Bucket Icon

Select the color for your second expression

Click on Add if appropriate...or OK if you're through.

Linq :0)>

Hah - so it's the same steps as for Excel! HOOZAH!!!

I have a New Formatting Rule
Rule Type: Check values in the current record or use an expression
Format only cells where the:
Expression Is: [SourceDate] >= Date(Now()+60)
Change Back Color To Red

This gives me an error of:
The expression you entered has a function containing the wrong number of arguments

(and [SourceDate] is a field in the table and being displayed on the sub-form)
What am I setting up incorrectly here?

GC2010 · Jul 14, 2019

I have tested on ONLY my SourceDate field and have added in the criteria
Red: Value < Date()-60
Yellow: Value Between Date()-30 AND Date()-59

The only issue I have remaining is that it ONLY will backfill the SourceDate column, I want ALL textboxes on the form to have the back-color changed when the criteria is met.

theDBguy · Jul 14, 2019

GC2010 said:
I have tested on ONLY my SourceDate field and have added in the criteria
Red: Value < Date()-60
Yellow: Value Between Date()-30 AND Date()-59

The only issue I have remaining is that it ONLY will backfill the SourceDate column, I want ALL textboxes on the form to have the back-color changed when the criteria is met.

To apply CF on a control based on a value in another control, you'll have to use an "expression." For example:

Code:

Expression Is: [ControlName] Between Date()-59 And Date()-30

Hope it helps...

Gasman · Jul 14, 2019

GC2010 said:
I have tested on ONLY my SourceDate field and have added in the criteria
Red: Value < Date()-60
Yellow: Value Between Date()-30 AND Date()-59

The only issue I have remaining is that it ONLY will backfill the SourceDate column, I want ALL textboxes on the form to have the back-color changed when the criteria is met.

Either apply the same criteria to every control in the row, or use a textbox as back fill as in the Emulated Split Form DB attached.?

Edit: I see theDBguy already mentioned this in post #2?

Dreamweaver · Jul 14, 2019

Hopefully this will help

https://youtu.be/NDDvM5dC38g

GC2010 · Jul 14, 2019

All right! I’m very close now!!

Can someone help me with the condition for the 30 days past due?

The Source date displayed that we are checking against will always be the 1st of the month

For > 60 day accounts I am using
Expression is [SourceDate] <Date()-60

If I use
Expression is [SourceDate] Between Date()-30 and Date()-59

That skews the results bc today is not the first of the month so we are not actually altering color accurately

With the Source date Always being the 1st as the day how would my conditional formatting need to be setup to truly show accounts 30 days over due and accounts 60 days or greater over due?

theDBguy · Jul 15, 2019

GC2010 said:
All right! I’m very close now!!

Can someone help me with the condition for the 30 days past due?

The Source date displayed that we are checking against will always be the 1st of the month

For > 60 day accounts I am using
Expression is [SourceDate] <Date()-60

If I use
Expression is [SourceDate] Between Date()-30 and Date()-59

That skews the results bc today is not the first of the month so we are not actually altering color accurately

With the Source date Always being the 1st as the day how would my conditional formatting need to be setup to truly show accounts 30 days over due and accounts 60 days or greater over due?

Hi. I think maybe you’re asking how to use the DateDiff() function here. For example:

Code:

Expression Is DateDiff([SourceDate],Date())>60

GC2010 · Jul 15, 2019

theDBguy said:
Hi. I think maybe you’re asking how to use the DateDiff() function here. For example:

Code:

Expression Is DateDiff([SourceDate],Date())>60

I think I’ve done a poor job of explanation

What my issue is, is that we are using Date() which is based off the current Fate.

I need the calculation to be based off the first day of the month

For example, the current day of the current month is no matter to the calculation. I want to show 60 days from first of month, does that make sense?

Also show between 30 and 60 days from first of month

Micron · Jul 15, 2019

I want to show 60 days from first of month

Maybe

Code:

DateSerial(Year(#06/14/2019#),Month(#06/14/2019#), 1) + 60

where you are providing any valid date value for the month in question?

GC2010 · Jul 15, 2019

Micron said:
Maybe

Code:

DateSerial(Year(#06/14/2019#),Month(#06/14/2019#), 1) + 60

where you are providing any valid date value for the month in question?

That was so close! When I used your code, I kept getting the 2nd and not the first.

Example:

Code:

Debug.Print DateSerial(Year(Date), Month(Date), 1) - 60
Debug.Print DateSerial(Year(#07/14/2019#), Month(#07/14/2019#), 1) - 60

Returns

Code:

5/2/2019

Which got the wheels turning and I used

Code:

Debug.Print DateSerial(Year(Date - 60), Month(Date - 60), 1)

Which returns my desired result of:

Code:

5/1/2019

Micron · Jul 15, 2019

Looks like you got it then? Just be sure to test as many date ranges as you can, some with February and such. Looks promising though.

Hope this is not you...
https://www.accessforums.net/showthread.php?t=77590

Change Color Of Sub-Form Row (1 Viewer)

GC2010

Registered User.

theDBguy

I’m here to help

GC2010

Registered User.

missinglinq

AWF VIP

GC2010

Registered User.

GC2010

Registered User.

theDBguy

I’m here to help

Gasman

Enthusiastic Amateur

Attachments

Dreamweaver

Well-known member

GC2010

Registered User.

theDBguy

I’m here to help

GC2010

Registered User.

Micron

AWF VIP

GC2010

Registered User.

Micron

AWF VIP

Similar threads

Users who are viewing this thread