I have a table which controls categories that are used throughout my database. They are grouped on graphs and values are displayed based on those categories in a pie chart. I added a column that I entered RGB values in for the colors that I want those categories to use in the pie chart, so when a category loads in the chart it isnt automatically assigned a color and the rest of the data sets I set the color for doesnt lose theirs. The values in the field are like "255,255,0". Im assuming through vb, how do I get the pie chart to use the corect rgb value for the category it's displaying?

toons,

You may want to check out this Microsoft Knowledgebase article on How to Change a Chart's Colors Dynamically.

Hope this helps,

Dennis

I looked at that, but it seems a little much for what Im trying to accomplish. Is there a way to simply do a loop until EOF for my tbl_expense_types that utilizes something like this (which I found online but doesnt work with my chart)



Id be replacing the RGB values with a dlookup that would pull the correct value based on the corresponding SeriesCollection, so it would probably just be 1 line for the dataset and color in the loop instead of a line for each color like the above code.

toons,

The knowledgebase article's sample code included this:



This seems to be different from your posted code. Did you try altering your syntax to match the knowledgebase article's syntax?



I was under the impression that you needed help with the syntax for changing the color of chart objects, which is why I linked you to the knowledgebase article. Now you're asking how to do a loop... Can you show what you have so far? That might help me to figure out how you should be looping your code.

Hope this helps,

Dennis

this is what I started playing with, but commented it out since it creates errors when I try to use it



I guess, from the link you provided, I would try and somehow make the following code do what I am trying to accomplish



my main issue is that I havent seen the SeriesCollection method appear when in the vb editor. at least that is one of the main issues. I just need each data series to use the RGB value I specified in the table, so each category has the same slice color in the pie graph, regardless if some categories have data and load or not

toons,

Okay, so it looks like you haven't worked with recordsets before. Here's the basic structure I use:


This loops through each record in the query. So, if the query has 7 records, you can do something to the chart 7 times.

Now, I haven't seen your pie chart, or your query, so I don't know if this is what you need or not. So, let's try to clarify things a bit: You want to loop through a set of records - one record for each category. So, is this true? Does the query "qry_expense_history_pie" contain only one record for each unique category? If so, you should be almost home.

Then, it's just a matter of figuring out the exact syntax for changing the color of which part of the chart. I'd recommend just hard-coding the change to red for each iteration of the loop. Use that until your code turns the whole chart red. After that works, move on to extracting the color info from the recordset and assigning it to the chart.

Hope this helps,

Dennis

ok, so I modified it to this just to test a static value for a color but Im getting the 'too few arguements' error for line "Set rst = qdf.OpenRecordset(dbOpenDynaset)"

also, this is the sql of my query 'qry_expense_history_pie'. I would prefer to not have a hard coded strsql in the vb so that if I need to make edits to the query I can do it to the physical query.

toons,

This SQL looks a lot more complex than it probably needs to be - all it needs to select are the categories and their corresponding RGB values, right? If you feel you must use this SQL, you need to make it dynamic, by inserting the [Forms]![frm_main]![sub_menu_target].[Form]![cbo_date] value at the time of execution. Something along these lines:



(Untested air-code.)

Notice how the reference to the cbo_date control is NOT in quotation marks, so the VBA can grab the value from the form and insert it into the SQL? That's what you need to do at run-time, if that value determines which colors apply.

Hope this helps,

Dennis

Here is the code now. It is getting stuck on the SeriesCollection value, saying it is 'unable to get the SeriesCollection property of the Chart class'. Ive looked it up and alot of people seem to be doing something like 'Dim PieChart As Chart' or Graph.Chart , but I dont see that anywhere on the property list. It needs to recognize that PieChart is a chart so that it can apply the SeriesCollection property. Im still digging, but there isnt any straightforward answers that I can find so far

ok, found this which helped get past that hump



on like the 20th page of google search results. No it's saying 'no current record', even though I have the cbo_date using a default value of the first value in the combo's query results. I think Ill set a delay and see if its just running too fast

toons,

WHEN is it saying "no current record"? Are you certain that your query contains data?

Place a Breakpoint on this line:



Then, when the code pauses there, go into your Immediate Window, and enter:

? strSQL

It should return the SQL code for your query. Copy and paste the SQL from the Immediate Window into a new query and run the query, to see if it contains any data. If not, look at the value being passed by the combobox to see if it's valid.

Dennis

ok, I got past the 'no data' error. basically, even though the date combo had a default value on load, it wasnt populating the initial value fast enough so the sql wasnt getting data associated with the date fast enough. I added an onload string to quickly populate the first value onload, and its loading fine now. however, it is once again back to the whole 'unable to get the seriescollection...' error. Even though I set the object to chart, its not working. I just keep hitting a wall with this

Which error are you getting, and on which line of code?

Dennis

Run-time error '1004':

Unable to get the SeriesCollection property of the Chart class.


getting that on line

According to the knowledgebase article I linked you to, you should be using:

Set PieChart = Me!pie_mon.Object

I just created a test form with a pie chart on it, and this worked for me:



Hope this helps,

Dennis

not at all. using the With and trying to do a loop isnt allowing it to recognize the SeriesCollection. when I replaced the 0 with a 1 like in your code, it said that Color wasnt a property of Interior. Im getting such a headache with this. Ill try to just run 1 instance like you did and see if it works

yes, when I placed



outside of the With loop it worked fine. also inside of the loop it worked when I set it to vbRed. When I tried changing the color to

or it gave the 'unable to set color property of interior class' [censored] again.

more trial and error has given more insight, sort of. changing the code to this produced 2 things

1) I get the error 'unable to set seriescollection...'
2) if I click End when the error appears, it still loops through and all pie wedges are red. so the loop is there but still gives the stupid error msg first



when I change vbRed to the colors dont change at all. when I change to is gives the error 'unable to set the color property...' but not the seriescollection error

toons,

I think we need to calm down/slow down here. If my advice isn't helping "at all" as you say, I can move on to other tasks. I'm just a volunteer here, trying my best to help you out.


You need to refer to your recordset properly, and you appear to be trying random syntax at this point. Where did you come up with rst("exp_colo")?

rst.Fields("exp_colo").Value should give you a value, if indeed there is a value in the query field for that record. You can use a Breakpoint to pause your code and the Immediate Window to verify this. When you aren't sure about your syntax, you should check the built-in Help system in the Visual Basic Editor.

Here's the order that you should be working on:

1. Verify that you can change the color of what you want to change by using a constant value. (I've suggested this earlier.)

This will verify that you are using the correct syntax.

2. Verify that your recordset/field actually contains a valid value for a color at the time of execution. You can do this with a Breakpoint and the Immediate Window.

3. Verify that you've properly inserted your recordset/field value into your syntax.

Right now, you're all over the place, complaining about several errors at once. Solve the first problem first. Do NOT attempt to work on the next problem before solving the previous one. That just leads to confusion.

Hope this helps,

Dennis

this was what I had said wasnt really helping, not for what Im trying to do anyway. I tried adding the .Value to the end of the color string, but it didnt find the value in the field. I do know that exp_colo has a value for each record in that table. An example of one of the values is, and this is how the value appears in exp_colo, is RGB(0,153,0).

having the loop just change them all to vbRed works, but only after the 'unable to get the seriescollection property...' appears and I click End. on Debug the line it is pointing to is



and this is still after doing the

Dim PieChart As Object

Set PieChart = Me!pie_mon.Object

in debug, when that line is highlighted and I hover the cursor over it, it clearly shows the value

rst.Fields("exp_colo").Value = "RGB(204,102,255)"

if it is loading the proper value, but wrapping it in quotes, that could be the issue. I know that the quotes make it a string, and access cant translate it to RGB values. is there a function that can make it so when it pulls the value it doesnt wrap quotes around it?

ok, Im close, but its the same old error again 'unable to get the seriescollection property of the chart class'. instead of using the [exp_colo] value from rst, I added the original column from the table without the concatenated RGB() wrapped around it. The original column was [exp_color] and was just the 3 numerical values separated by commas. when the subform loads the graph, it gives the mentioned error, and when I click end all the data series are colored with the initial value: 204,102,255. so, it is pulling the color from the first record now successfully, but crapping out right after and not populating the rest of the colors. on the home stretch, but dont really know where to go from here.

The problem is in this line:

PieChart.SeriesCollection(i).Interior.Color = rst![exp_color]

exp_color is a string that contains the name of the function with arguments. VBA isn't going to evaluate that function into a color number unless you tell it to. It just considers the field value text like "Hello". So the code is trying to set the color to the literal character set "RGB(#,#,#)" which doesn't make sense. Actually, the chart color properties are pretty versatile, in that they do accept numbers and some strings, but those strings have to be sensible color names like "Red", etc. It won't know what to do with a name like "RGB(#,#,#)"

If you weren't using a recordset and tried to do this:

PieChart.SeriesCollection(i).Interior.Color = RGB(255,0,0)

That would work fine, because without quotes the RGB function would be evaluated. However, this is what your code is essentially doing by supplying the value from a recordset text field:

PieChart.SeriesCollection(i).Interior.Color = "RGB(255,0,0)"

There are a few things you can do:

1. Modify your table to include the actual color number instead of an RGB function string.
2. If you want to keep the individual red, green and blue components in the table, split them into three numeric fields and change the line I referenced above to:

PieChart.SeriesCollection(i).Interior.Color = RGB(rst![exp_color_R].Value,rst![exp_color_G].Value,rst![exp_color_B].Value)

3. Keep your table structure exactly the same and change your code to:

PieChart.SeriesCollection(i).Interior.Color = Eval(rst![exp_color].Value)

The Eval() function takes a string that indicates the name of a function and evaluates it.

EDIT
I just saw your recent post about understanding the string thing, not sure how I missed it the first time. Your last question about a function that will take the quotes off is essentially option 3 above.

thanks for the reply. this is the current code. it still gives the error about the seriescollection, but when I click End on the message it seems to be loading the correct color for the first data series. it isnt looping through and loading the correct colors for each series. it probably would if it wasnt giving an error. I just couldnt think of any other routes to take with it. I read so many threads online yesterday about it and couldnt find any other options, but the Points part seems to be a start towards what Im trying to do.

In the Access pie charts I've automated, the pie itself is a single series and the slices are points. What about just assuming you have 1 series and looping through the points? In my experience the collections are 0-based, so you may have to replace the 1's with 0's.



What is the exp_color field in the table? I see your SQL creates the RGB() expression on the fly using the value in that field but you alias it as exp_colo without the r. So this line:

PieChart.SeriesCollection(0).Points(j).Interior.Color = rst![exp_color]

Would be setting the value to what's actually in the field, not the RGB() expression. If the field still contains RGB(#,#,#) I still don't see how that line would work for you since that's just a blob of text.

Also, it seems you want each slice to be a specific color based on the type. Simply looping through wouldn't do that for you, unless the points happen to be in the same order as the records.

right, I want each slice (point) to be a specific color. in the table, exp_color is exactly that. a typical field value might be 0,255,0. in the query, I tried appending the RGB() around it to see if that would translate it, but it just created a string. when I just used the direct field name, so only the comma separated numbers loaded, it seemed to do thr trick for the first point. the sql only picks up points with an actual value, and each record has a set color, so wouldnt looping through the points still produce the same effect? the graph would show the name of the dataseries, the value associated with it, and the color attached to it. it wouldnt jumble it, would it?

right, I want each slice (point) to be a specific color. in the table, exp_color is exactly that. a typical field value might be 0,255,0. in the query, I tried appending the RGB() around it to see if that would translate it, but it just created a string. when I just used the direct field name, so only the comma separated numbers loaded, it seemed to do thr trick for the first point. the sql only picks up points with an actual value, and each record has a set color, so wouldnt looping through the points still produce the same effect? the graph would show the name of the dataseries, the value associated with it, and the color attached to it. it wouldnt jumble it, would it?

First, it seems that the color property actually does accept values like this:

#,#,#

without error, but from what I'm seeing in a quick demo (attached), they don't appear to be correct on screen, like maybe it's just taking the first number in the set or jumbling the r,g,b order. However, I do get colors I expect using:

Eval("RGB(" & rst!exp_color & ")")

Since a point loop would simply go through the points collection, that collection would have to have been created in the same order as the records are in the recordset. I can't say if that will be guaranteed to happen, but you don't have anything forcing it to happen. A better idea would be to filter the recordset to the type that matches the current point's type:

rst.Filter = "ExpenseType=" & pt.GetValue(chDimCategories)

That's the code I used in the attached example but I think you're using a different chart object than I am so the chDimCategories might not work. Mine is Access 2002 PivotChart.

my graph is a graph that physically sits on the form, not one dynamically created within the vb. how would I incorporate what you did in to my loop? I looked at your code, but Im not quite sure what to do with mine to make it work like yours without losing my formatting. my query also pulls a date from a field on the form, which is why I wound up having to put the sql in the vb itself and not use the query I had in the db

The technique that fixes your current problem is recognizing the slices are points, not series, and finding the point in the recordset to get the color. Instead of this:



I'd suggest this structure:



Replace tbl_expense_types.exp_name with the field used as your categories. Adjust for data type as well (no single quotes for numeric). If you don't have a reference to a Web Components library containing the chDimCategories constant, try the literal value, 1, instead:

rst.Filter = "YourCategoryFieldName=" & pt.GetValue(1)

With your type of graph object, it seems it's been 1-based, so you may also need to use SeriesCollection(1) instead of (0).

ok, Ill give it a go. this is the first time Ive had to automate a graph, so its all new functions for me. thanks for your help so far and Ill post the results

still no luck. with your adjustments, this is my current code using the correct field names. using SeriesCollection(0) I get the error 'unable to get the seriescollection property...' for line , and with SeriesCollection(1) I get 'object doesnt support this property' for line

Well I know a DAO.Recordset has a Filter property, so my only guess is whatever graph object you're using doesn't like the pt.GetValue. That's odd since everything else you have except the collection base seems to match.

The next step I'd suggest is finding a way to extract the category value out of the pt (point) object so you can use it to filter the recordset. The fastest way I could think of was pt.GetValue. If nothing seems to be able to give you that, try just assigning colors based on recordset position and hope they match the order of the points:

and that worked. the colors do match the value set per category in the table. this has been an overly insane endeavor, and I thank those who have helped me get to this point. Im going to have to play with it more and see if it sticks when more categories load in to the pie. for those who might try to do this later, here is the code Im using

Congrats. I'm always excited about what I can do with an automated graph, but never excited that every time I have to program a new one it feels like I'm doing it for the first time again. There's very little good documentation for doing it in Access/VBA beyond the very basic. Enjoy!

I will point out that using the same code, though, doesnt seem to be working in a report using the same graph setup. Ill have to play with it and see what I can come up with.

ok, to get the exact same code to work on the graph in a report, I just used it in the Details section OnFormat event. copied and pasted. no changes. and the results work as perfectly as the form. cheers

another note of advise for future dynamic graph makers. in my example I had a combo box that grouped and displayed the month and year, which the query in the graph pulled from. having the script for the graph in the OnOpen/Load event didnt load the colors properly after I changed the date in the combo to show a different month's data. they were all jumbled because it wasnt reloading the color loop. To remedy this, I created a Public Function called

ResetPieChart()

I pasted the graph code in to that. In my forms OnOpen event I called up ResetPieChart. In the combo boxes OnChange event I also called up ResetPieChart so that when I select a different date, the colors reload and match the points correctly.