MsAccess Barchart Color Change using vba

[baba]

Hi All ,


I have created a MS Access barchart by running a query. X axix has user name and y axis has counts. I would like to change every bar's color based on the x axis user name value .Is there a way to do that using vba code in MSAccess barcharts. Kindly advise.

[June7]

Google: VBA Access barchart color

Maybe this will help
http://www.ozgrid.com/forum/showthread.php?t=147742

[baba]

Hi ,
Thanks for the details. The link you provided will work for excel but it is not working for MSAccess.
I would like to know if I can read the values of x axis in a bargraph and change the color of each bar based on the value of x axis in MSACCESS.
I appreciate your help !

[June7]

I have been able to adapt in Access VBA code concerning graphs that was written behind Excel. That said, I don't know if this particular case can be accomplished with Access graphing. Do you want to provide project for analysis?

[baba]

Thanks for the response. I am writing a detailed explanation of my sceanrio below. Please see if you can help !

Query ran to create barchart :Select jobname,count(*) from table 1;
In my bargraph,
X axix shows jobnames.
Y axis shows counts.
All the bars in barchart are of same color.

Example bar 1 : jobname : Joba count 3
Example bar 2 : jobname : Jobb Count 5
Example bar 3 : jobname : Jobc count 7
Example bar 4 :jobname : Joba count 7

Currently the generated bar graph shows all jobs joba,jobb,jobc,jobd in the same color.
My requirement:
bar 1-Joba -Green -
bar 2-Jobb -Blue
bar 3-Jobc -Yellow
bar 4-Job a -Green
Is it possible to change the color of each barcode by reading the jobname of the bar that is in the x axis.



Is it possible to write some code like this to change the bar colors after the bargraph is generated by my query....
Step 1.Generate the bargraph by the query
Step 2.Change the barcolor of each bar based on the jobname value .

I have writtent the logic of what I want but I dont know the syntax and how to implement it .Kindly advise

With Forms!Form2.Graph1
For i = 1 to Forms!Form2.Graph1.SeriesCollection.Count
If Forms!Form2.Graph1.SeriesCollection(i).Xaxis.value = "joba"
then Forms!Form2.Graph1.SeriesCollection(i).interior.co lor = "Green"
end if

If Forms!Form2.Graph1.SeriesCollection(i).Xaxis.value = "jobb"
then Forms!Form2.Graph1.SeriesCollection(i).interior.co lor = "Blue"
end if

If Forms!Form2.Graph1.SeriesCollection(i).Xaxis.value = "jobc"
then Forms!Form2.Graph1.SeriesCollection(i).interior.co lor = "yellow"
end if

Next i

End with

[baba]

SELECT TrendRemedytbl.[Job Name], Count(*) AS [Count]
FROM TrendRemedytbl
GROUP BY TrendRemedytbl.[Job Name];

This query generates a barchart with jobname in xaxis and counts in yaxis .
Can i also get the color of bar populated CORRECTLY based on if conditions within the query
I want something like what I have pasted below but the syntax is incorrect. Please help ...

SELECT iif TrendRemedytbl.[Job Name] = "A" THEN TrendRemedytbl.[Job Name] AS RED,iif TrendRemedytbl.[Job Name] = "B" THEN TrendRemedytbl.[Job Name] AS BLUE,iif TrendRemedytbl.[Job Name] = "C" THEN TrendRemedytbl.[Job Name] AS GREEN, Count(*) AS [Count]
FROM TrendRemedytbl
GROUP BY TrendRemedytbl.[Job Name];

[June7]

You need a nested IIf to set value:

SELECT IIf([Job Name]="A","Red", IIf([Job Name]="B","BLUE", IIf([Job Name]="C","Green", "Black"))) AS NameColor, Count(*) AS NameCount
FROM TrendRemedytbl
GROUP BY [Job Name];

Or use Switch function:
SELECT Switch([Job Name]="A","Red", [Job Name]="B","BLUE", [Job Name]="C","Green") AS NameColor, Count(*) AS NameCount
FROM TrendRemedytbl
GROUP BY [Job Name];

I have no idea how the bar chart will apply these values to color the bars.

Found this code from a thread. I tested it in the Format event of a report and Open event of form. Both worked.

Private Sub Form_Open(Cancel As Integer) 
 
With Me.Graph1
           .SeriesCollection(1).Interior.Color = RGB(255, 128, 128) 'peach
           .SeriesCollection(2).Interior.Color = RGB(0, 255, 128) 'green
           .SeriesCollection(3).Interior.Color = RGB(0, 128, 255) 'blue
           .SeriesCollection(4).Interior.Color = RGB(128, 255, 255) 'turquoise
           .SeriesCollection(5).Interior.Color = RGB(255, 255, 128) 'yellow
           .SeriesCollection(0).Interior.Color = RGB(255, 0, 255) 'magenta
End With
 
End Sub

[baba]

Thanks .... Also this link works


http://www.bigresource.com/Tracker/Track-ms_access-s7QD24x7/