VBA to Conditionally Format a Chart (2 Viewers)

[isladogs]

I’m not completely clear what you are doing. Can you upload a cut down version of your database to look at.

 

[DHookom]

Did you attempt the method I suggested with creating multiple columns from a single field where one of the multiple will have the value and the others will return 0?

 

[ChrisMore]

I’m not completely clear what you are doing. Can you upload a cut down version of your database to look at.

Hi Colin, I've attached a sample version of the project I'm working on, along with the data.

The database will open to the calendar report and you'll see the chart in question under Monday. Above that is the controls to change the work week. Underneath these controls are some helper text boxes, which would normally be hidden. One of these boxes [Text33] is the criteria for the underlying query for the chart. It calculates in multiples of 7 as the work week changes. If you open the chart report independently and type -35 into the parameter box it will show the data in the example screenshot I previously sent.

The two issues I have with this setup since using a modern chart:

1. The chart in the report loads quicker than the report load event so [Text33] isn't populated with 0 (as per the report load event) before the underlying query for the chart can use this as its criteria.
2. The chart isn't responding to the requery code I previously posted.

If I can get the chart to requery, it should resolve my first issue as I can requery the chart after the report load event is complete.

Thanks for your help.

 

[ChrisMore]

Did you attempt the method I suggested with creating multiple columns from a single field where one of the multiple will have the value and the others will return 0?

I didn't because I wasn't 100% sure on what you meant and you mentioned manually changing the color of the bars and this is what I want to avoid. I was also distracted by learning about modern charts so ended up going down that route.

 

[isladogs]

Just to check, the report shortcut is called Monday_Machine_Type_Usage_Total_StackedChartReport, the parameter dialog is

Finally the chart control is Chart4 ....

I think it will take me a while to work out how this all works and it may not be today. Perhaps others can look at it as well

 

[ChrisMore]

Just to check, the report shortcut is called Monday_Machine_Type_Usage_Total_StackedChartReport, the parameter dialog is

Finally the chart control is Chart4 ....

I think it will take me a while to work out how this all works and it may not be today. Perhaps others can look at it as well

That's correct, Monday_Machine_Type_Usage_Total_StackedChartReport is the chart report and the chart is named Chart4.

No problem, there's no rush to resolve this. Others are welcome to take a look. I am grateful for any help I can get.

 

[isladogs]

Two more questions to speed things up:
1. Am I right in thinking that all you are interested in is the blue progress bars on the stacked bar chart?
However, you would ideally like the progress bars to be the colour of the traffic light section where the bar ends.

2. The chart does appear to update as far as I can tell. Maybe I'm misunderstaning what you mean

For example, if I type in -35 I get the following:

but if I type in -21 I get

Similar changes occur for other parameter values

 

[ChrisMore]

Two more questions to speed things up:
1. Am I right in thinking that all you are interested in is the blue progress bars on the stacked bar chart?
However, you would ideally like the progress bars to be the colour of the traffic light section where the bar ends.

2. The chart does appear to update as far as I can tell. Maybe I'm misunderstaning what you mean

For example, if I type in -35 I get the following:

but if I type in -21 I get

Similar changes occur for other parameter values

My main objective was to have the blue progress bar coloured like the traffic light system, as you say.

However, since using a modern chart, the requery part of the calendar report code is no longer working so the chart is not updating when the calendar controls are used. The chart updates with the parameter dialog but not when using the calendar report controls.

As I said previously:

The two issues I have with this setup since using a modern chart:

1. The chart in the report loads quicker than the report load event so [Text33] isn't populated with 0 (as per the report load event) before the underlying query for the chart can use this as its criteria.
2. The chart isn't responding to the requery code I previously posted.

If I can get the chart to requery, it should resolve my first issue as I can requery the chart after the report load event is complete.

 

[isladogs]

OK thanks. Can't do anything for now as I have a problem with my Office installation which I'm trying to resolve

 

[ChrisMore]

OK thanks. Can't do anything for now as I have a problem with my Office installation which I'm trying to resolve

As I said, no rush. I do appreciate your time helping me with this

 

[isladogs]

Hi Chris
Sorry but I can't see any way of applying conditional formatting based on the values of the percentage (blue) bars
i've just asked a member of the Access team if he can suggest a method of doing so and will let you know if I get a solution

As for the requerying, your chart is based on multiple layers of queries and it should be possible to simplify this considerably.
The chart should automatically update one the values are set using the various buttons.
You also have requery events for every single component of the report and report_load, after each button click and on a timer event.
I would also suggest simplifying all of those multiple layers of requery. You should be able to remove a lot of this code

I've spent well over an hour trying to simplify this but without success as far as the chart is concerned

Other things:

1. Use Option Explicit at the top of all forms & reports and ensure all variables are declared
2. You can shorten all the code by using Me. syntax e.g. Reports![Five_Day_Production_Report]![Monday_Production_Report].Report.Requery can be written as Me.Monday_Production_Report.Requery. Similarly for all the other similar lines
3. You could also put all the requerí code lines into a separate procedure and call that as needed. For example:

Private Sub RequeryObjects()

    Me.Monday_Production_Report.Requery
    Me.Tuesday_Production_Report.Requery
    Me.Tuesday_Machine_Availability_Subreport.Requery
    Me.Wednesday_Production_Report.Requery
    Me.Wednesday_Machine_Availability_Subreport.Requery
    Me.Thursday_Production_Report.Requery
    Me.Thursday_Machine_Availability_Subreport.Requery
    Me.Friday_Production_Report.Requery
    Me.Friday_Machine_Availability_Subreport.Requery

End Sub

Now use that in your button click code which is far more complex than it needs to be

Private Sub Command43_Click()
    Me.Text33 = 0
    RequeryObjects
End Sub

Private Sub Command36_Click()
    If Me.Text33 = 0 Then      
           Me.Text33 = Me.Text39
    Else    
       Me.Text33 =Me.Text42+Me.Text39
   End If

    Me.Text42 = Me.Text33
    RequeryObjects
End Sub

4. Its very difficult to keep track of the controls as you are using default names such as Text33, Text42, Command36, Command37 etc
Use meaningful names e.g. cmdRefresh, cmdNext, cmdPrev etc.

 

[ChrisMore]

Hi Chris
Sorry but I can't see any way of applying conditional formatting based on the values of the percentage (blue) bars
i've just asked a member of the Access team if he can suggest a method of doing so and will let you know if I get a solution

As for the requerying, your chart is based on multiple layers of queries and it should be possible to simplify this considerably.
The chart should automatically update one the values are set using the various buttons.
You also have requery events for every single component of the report and report_load, after each button click and on a timer event.
I would also suggest simplifying all of those multiple layers of requery. You should be able to remove a lot of this code

I've spent well over an hour trying to simplify this but without success as far as the chart is concerned

Other things:

1. Use Option Explicit at the top of all forms & reports and ensure all variables are declared
2. You can shorten all the code by using Me. syntax e.g. Reports![Five_Day_Production_Report]![Monday_Production_Report].Report.Requery can be written as Me.Monday_Production_Report.Requery. Similarly for all the other similar lines
3. You could also put all the requerí code lines into a separate procedure and call that as needed. For example:

Private Sub RequeryObjects()

    Me.Monday_Production_Report.Requery
    Me.Tuesday_Production_Report.Requery
    Me.Tuesday_Machine_Availability_Subreport.Requery
    Me.Wednesday_Production_Report.Requery
    Me.Wednesday_Machine_Availability_Subreport.Requery
    Me.Thursday_Production_Report.Requery
    Me.Thursday_Machine_Availability_Subreport.Requery
    Me.Friday_Production_Report.Requery
    Me.Friday_Machine_Availability_Subreport.Requery

End Sub

Now use that in your button click code which is far more complex than it needs to be

Private Sub Command43_Click()
    Me.Text33 = 0
    RequeryObjects
End Sub

Private Sub Command36_Click()
    If Me.Text33 = 0 Then     
           Me.Text33 = Me.Text39
    Else   
       Me.Text33 =Me.Text42+Me.Text39
   End If

    Me.Text42 = Me.Text33
    RequeryObjects
End Sub

4. Its very difficult to keep track of the controls as you are using default names such as Text33, Text42, Command36, Command37 etc
Use meaningful names e.g. cmdRefresh, cmdNext, cmdPrev etc.

Hi Colin,
Thank you very much for the time you have spent looking at my project. Also, I really appreciate your feedback. I am self taught with VBA so a lot of bad habits and/or over complications have crept into my coding over the years. It is very helpful to have direct feedback on what I have coded. I will adjust the code/naming as you've suggested.

Thank you for contacting the Access team, I look forward to hearing their response.

Regarding the requerying and your response:

The chart should automatically update one the values are set using the various buttons.

I've spent well over an hour trying to simplify this but without success as far as the chart is concerned

As I have said previously, the classic chart didn't have a problem with requerying using this setup. Why would this be different for a modern chart?
Is the modern chart not requerying because of the complex layering to the underlying query?

 

[isladogs]

I can’t be certain due to the way you’ve coded this but assume that it’s not updating due to how the data source has been built up. All the charts I have ever created update automatically to any data changes at runtime.

 

[isladogs]

SUCCESS!
Add ONE line to the RequeryObjects sub I showed in post #31 and it updates correctly

Private Sub RequeryObjects()

    Me.Monday_Production_Report.Requery
    Me.Monday_Machine_Type_Usage_Total_StackedChartReport.Report.Chart4.RowSource = "Monday_Machine_Type_Usage_Chart_Query"
   ' Me.Monday_Machine_Type_Usage_Total_StackedChartReport.Report.Chart4.Requery  'REQUERY NOT NEEDED
    Me.Tuesday_Production_Report.Requery
    Me.Tuesday_Machine_Availability_Subreport.Requery
    Me.Wednesday_Production_Report.Requery
    Me.Wednesday_Machine_Availability_Subreport.Requery
    Me.Thursday_Production_Report.Requery
    Me.Thursday_Machine_Availability_Subreport.Requery
    Me.Friday_Production_Report.Requery
    Me.Friday_Machine_Availability_Subreport.Requery

End Sub

Example results:

Updated FE attached. Please check it works correctly
I'll leave you to do the rest of the tidying up! For info, it doesn't compile due to issues elsewhere in the FE

EDIT:
Also I suggest you scrap the red/yellow/green data fields as these get 'squeezed' when the blue bar exceeds 50%

 

[ChrisMore]

Amazing! It does work. Thank you for your persistence

Note sure what you mean by:

For info, it doesn't compile due to issues elsewhere in the FE

What doesn't compile?

Regarding your edit:
I have fixed the expression in the underlying query for the coloured data fields so that issue isn't there when the blue bar exceeds 50%.

 

[isladogs]

Glad you're happy with it!
As I said (after editing my initial reply), there was no need to requery the chart itself as that happens automatically
You can probably remove the Refresh button now as well.

What doesn't compile?

When I tested it failed on the Cutomer_Order_Form ... Goods_Out_Search_Button_Click on the line
If DLookup("[Tag]", "Customer_Orders", "[Order_Number] ='" & Me.Text67 & "'") = True Then

However that button doesn't actually exist so that code procedure could be deleted.
When I did so it compiled.
However if you add Option Explicit to each code module there may well be other compilation issues - not tested.

 

[June7]

AFAIK, Modern Charts upgrades are only available in Access 365, not desktop version.
I have not tried coding for Modern Charts.
My code for Classic is working but the data is very limited.

Unfortunately, not all graph properties show in Intellisense hints (such as SeriesCollection although it is in Object Browser) - very annoying.

I did my first dialog with CoPilot which led to this advice (which seems to be what DHookum suggested in post 2):

1. Modify your query or table to include a calculated field that assigns a color name or RGB code based on the value.
SELECT
Category,
Segment,
Value,
IIf([Value]<50,"Red", IIf([Value]<100,"Orange","Green")) AS ColorCode
FROM YourTable;

2. Instead of one stacked series, create multiple series, each representing a color category. This way, you control the color by assigning it to the series itself.
Your chart data might look like:

Category	RedValue	OrangeValue	GreenValue
Q1	30	20	50
Q2	10	40	100

3. Code

Private Sub Form_Load()
    Dim cht As Object
    Set cht = Me.YourChartControl.Object

    With cht
        .SeriesCollection(1).Interior.Color = RGB(255, 0, 0)     ' Red series
        .SeriesCollection(2).Interior.Color = RGB(255, 165, 0)   ' Orange series
        .SeriesCollection(3).Interior.Color = RGB(0, 128, 0)     ' Green series
    End With
End Sub

Here's link to the dialog https://copilot.microsoft.com/chats/wp5sZT5JEX9n3DqwEXt1E

That's as far as I've gone. Haven't figured out how to make it actually work for your data.

 

[isladogs]

AFAIK, Modern Charts upgrades are only available in Access 365, not desktop version.

All versions of Access including 365 are 'desktop versions'. There is no cloud edition of Access

 

[June7]

Perhaps I should have said "non-subscription" version?

 

[isladogs]

You could have just said full VBA support for modern charts is only available in Access 365.

Nevertheless, limited support for properties of the chart object are available using code in all versions from 2019 onwards.