Here is the breakdown. I have a number of tables within a database, I have a main screen as to pick the required table I want to display in either tabular or graph format, the table works. I have created a graph and am trying to get the code or row source in the properties to look at the VBA I have written.
Any ideas please?
It’s something to do with the row source type. If it were a function, I would just put the function name there. It’s a private sub.
How to change the Title of a graph with code. By title I mean the box on the graph that automatically displays the name of the table used to create the graph in the chart wizard. I've tried numerous bits of code found in different forums but they all failed.
Access 2010 OLE Class: Microsoft Graph Chart Class: MSGraph.Chart.8
Suppose you have 10 fields on a certain spot in a form. And you want to place these ten controls, say command buttons) to line up under each other with an even spacing of about 0.2...Up to now, for a whole lot of controls, I've been selecting them one by one (or "line" by "line"), and manually entering the correct numbers in the left and top property setting fields so they line up the way I want them to. In the beginning I've tried selecting multiple controls at once and then using align but this actually never does what I want it to do.Do you guys and gals know something I don't concerning this which could make life easier?
Is it possible to direct the placement/order new of fields when modifying an Ms Access database in code?
I need to modify the schema of an MS Access database via code - but I want to be able to direct the order or placement of the fields within the tables.
For example - if TableOne has 3 fields - Field10, Field20 and Field30.
I would like to be able to add say Field15 between Field10 and Field20 - not just append it to the end of existing fields.
I believe it is possible as you can do it within Ms Access itself. I can use ADO, DAO, ADOX or SQL for that matter - but it seems all of these offer no placement of the field within the table.
We have a daily task that generates a dozen graphs. Each day, we want to display the graphs on a large screen monitor for public viewing, preferably as a slideshow. Currently the graphs come from Access 2003. How can we automate getting the graphs in a format that will work with PowerPoint or some other slideshow software?
I am trying to change the fill colors on a bar graph based on the value the bar graph has.
the bar graph gives the percentages of tasks and I would like to have it so that the fill color of a bar is based on the a set of values for the percentage... so if the percentage of task A is between 0 and 25% it should be red, if percentage of task A is between 25% and 50% it should be yellow, and if the percentage of task A is between 50% and 100% it should be green.
this is the query that the graph is based off of Code:SELECT TEST_RawData.DeliverableDesc, FormatPercent(Avg(PercentComplete)) AS CurrentProgressFROM TEST_RawDataGROUP BY TEST_RawData.DeliverableDesc;
This is the data table of the graph DeliverableDesc | CurrentProgress test 1 | 82.64% test 2 | 55.75% test 3 | 30.09% test 4 | 13.00%
now I have looked at this http://support.microsoft.com/?kbid=200527 and tried to remodel it to my situation but have some concerns. This is what I have so far vba Code: Original - vba Code Private Sub Report_Open(Cancel As Integer) Dim chtObj As Object, strRowSource As String Dim rsRowSourceFiltered As Recordset Dim i As Integer, j As Integer Dim strArrTaskPercents() As String Dim intArrTaskColors() As Integer ' The color integers are those that are used by the QBColor function to assign point colors. Const cBadProgress_Red = 4 Const cOkayProgress_Yellow = 6 Const cGoodProgress_Green = 2 ' Place all the task percents values into an array. ReDim strArrTaskPercents(3) strArrTaskPercents(1) = "10.00%" strArrTaskPercents(2) = "25.00%" strArrTaskPercents(3) = "50.00%" ' Place the task color values into an array. ReDim intArrShipperColors(3) intArrTaskColors(1) = cBadProgress_Red intArrTaskColors(2) = cOkayProgress_Yellow intArrTaskColors(3) = cGoodProgress_Green Set chtObj = Me!Graph10.Object strRowSource = "SELECT TEST_RawData.DeliverableDesc, FormatPercent(Avg(PercentComplete)) AS CurrentProgress FROM TEST_RawData GROUP BY TEST_RawData.DeliverableDesc;" Set rsRowSourceFiltered = CurrentDb.OpenRecordset(strRowSource, dbOpenSnapshot) ' Check to see if the filtered recordset has any records. If rsRowSourceFiltered.BOF And _ rsRowSourceFiltered.EOF Then MsgBox "There are no records to chart." Exit Sub End If ' Loop through the recordset containing the chart's filtered RowSource. rsRowSourceFiltered.MoveFirst i = 0 While Not rsRowSourceFiltered.EOF ' Index i synchronizes the Points collection index with the current recordset row. i = i + 1 ' Loop through the task percents array and look for a match with the field names of the chart's filtered RowSource. For j = 1 To UBound(strArrTaskPercents) ' 1-based ' The first field in the recordset contains the task percent. Some tasks may not be in the filtered recordset. If rsRowSourceFiltered.Fields(0).Value = strArrTaskPercents(j) Then ' Because every task has a corresponding color, the arrays strArrTaskPercents and intArrTaskColors always contain the same number of elements. Assign the color of the chart column, bar, slice etc. Graph10.SeriesCollection(1).Points(i). _ Interior.Color = QBColor(intArrTaskColors(j)) End If Next rsRowSourceFiltered.MoveNext WendEnd Sub Private Sub Report_Open(Cancel As Integer) Dim chtObj As Object, strRowSource As String Dim rsRowSourceFiltered As Recordset Dim i As Integer, j As Integer Dim strArrTaskPercents() As String Dim intArrTaskColors() As Integer ' The color integers are those that are used by the QBColor function to assign point colors. Const cBadProgress_Red = 4 Const cOkayProgress_Yellow = 6 Const cGoodProgress_Green = 2 ' Place all the task percents values into an array. ReDim strArrTaskPercents(3) strArrTaskPercents(1) = "10.00%" strArrTaskPercents(2) = "25.00%" strArrTaskPercents(3) = "50.00%" ' Place the task color values into an array. ReDim intArrShipperColors(3) intArrTaskColors(1) = cBadProgress_Red intArrTaskColors(2) = cOkayProgress_Yellow intArrTaskColors(3) = cGoodProgress_Green Set chtObj = Me!Graph10.Object strRowSource = "SELECT TEST_RawData.DeliverableDesc, FormatPercent(Avg(PercentComplete)) AS CurrentProgress FROM TEST_RawData GROUP BY TEST_RawData.DeliverableDesc;" Set rsRowSourceFiltered = CurrentDb.OpenRecordset(strRowSource, dbOpenSnapshot) ' Check to see if the filtered recordset has any records. If rsRowSourceFiltered.BOF And _ rsRowSourceFiltered.EOF Then MsgBox "There are no records to chart." Exit Sub End If ' Loop through the recordset containing the chart's filtered RowSource. rsRowSourceFiltered.MoveFirst i = 0 While Not rsRowSourceFiltered.EOF ' Index i synchronizes the Points collection index with the current recordset row. i = i + 1 ' Loop through the task percents array and look for a match with the field names of the chart's filtered RowSource. For j = 1 To UBound(strArrTaskPercents) ' 1-based ' The first field in the recordset contains the task percent. Some tasks may not be in the filtered recordset. If rsRowSourceFiltered.Fields(0).Value = strArrTaskPercents(j) Then ' Because every task has a corresponding color, the arrays strArrTaskPercents and intArrTaskColors always contain the same number of elements. Assign the color of the chart column, bar, slice etc. Graph10.SeriesCollection(1).Points(i). _ Interior.Color = QBColor(intArrTaskColors(j)) End If Next rsRowSourceFiltered.MoveNext WendEnd Sub
these are my concerns - first is the strucutre and syntax for the above Sub Report_Open good? -second how do I match percents the query is outputting to the table to the percents I write in the strArrTaskPercents array -third how would I change how it matches percents so that it matches a range of percentages, like the first example if something is between 50% and 100% it should be green.
if anyone can directly help with the code, or indirectly point me to some examples, articles, etc. that might help me it would be greatly appreciated.
A while back a colleague at work (who has since left my company) showed me an example of a graph within a Data Access Page in the Microsoft Northwind database. The Northwind database that I've downloaded doesn't contain such an example. Does anyone know of a Northwind DB with an exmaple of a graph embedded into a Data Access Page?
I am trying to export the result of a query to a specific Excel sheet where I have a dynamic graph. The results are exported on a monthly basis, so I am just adding the data, and the graph reflects that. Now, I can export to excel easily enough by using the Transfer Spreadsheet method, but this creates a new worksheet, thus destroying my lovely graph. I have even tried the Output Query method, but to no avail. Is there any way I can export to excel and keep the graph intact?
So as you can see, my graph is actually confusing my TOP record with the title and that's why it's not showing. So how can I fix this?
On a sidenote: As you can see, I didn't work on 11/11 & 12/11 but my graph is still showing those dates but of course with value 0. How can I leave out dates where I created no orders?
Hello :D I have an issue with Access I was hoping someone could help me on. So I have this pie chart graph with 3 sections, colored red for East, Green for West, and Blue for North. Now the problem is that sometimes, the data that I pull in doesn't have a "West" and therefor doesn't add it to the chart. This, however, throws the coloring scheme off as it assigns colors so that 1 is red, 2 is green, 3 is blue rather than East is red, West if Green, and North is Blue. I'll provide some images so it'll be easier to understand. (Clickable thumbnails)
I would like to display a graph in a form along with the fields from a table. The user will be allow to update the table and the graph will be updated. So far I am able to display the graph on the report but I cannot find a way to display the graph on the form. Can somebody help? Thanks in advance.
I have an x-y graph on a form that gets its data from a query. The query pulls five records of data from a table. So there are five data points I want to have displayd on the graph. I got the graph to work properly except it only displays four points! The first data point is not displaying. I tried to set the datasheet in the graph to "ignore" the first row, as I noticed immediately that it does not have a number reference....I am assuming that is the problem. Is there a method to resolve this easily? I assume that it must be done with vba. My data is used by the graph in two columns...first column is the X value, second column is the Y value.
I currently have a form from which users can select their name and it will open a report listing events they have attended. It does this through a macro running a filter. What I would like to happen is the same criteria that is being used to filter the report be used as criteria for a different (unfortunately) query that can then generate a graph in the report as well.how to get the criteria into the second query for the graph.
:confused: I wish to construct a form that displays a graph showing the monthly sales totals year on year. In this way - I will be able to see quite easily how the present monthly income compares to previous years.
Therefore, each year will have its own data series on the graph... the problem I have is that I wish the graph to automatically recognise the roll-over to the next year thereby creating the next series for that year. This would mean having the months Jan-Dec on the X axis and the income scale as the Y axis. Each data series representing the year. Otherwise, the alternative is to manually change the underlying datasource every year to include .... has anyone done anything similar to this that they would be willing to share please?
I currently have a Pareto graph created by a query, that counts occurence and then uses the Dsum function to create the cumalative %. The function is as follows:
This is as per MS article: http://support.microsoft.com/kb/208373
Now this is OK and is as per the MS article but if you have counts that are the same, it just give the same cumalative % for each one that is the same. Please see attached image for an example of what this query produces.
I recently started using the Chart feature in access. Everytime the form with the graph loads up windows installer pops up and asks me to install. Since i let it install like 3 times, i click cancel and it still works fine. Anyone else have a problem like this? How can i prevent it from installing everything, since its already installed?
When I use the Chart Wizard to create a chart it looks fine in the preview but when i change it to form design - to alter the fonts etc- it gives me a graph which looks as if it comes from the Northwest database. What on earth is going on???
PS Am i best using graphs in reports or Pivot charts
I Have this graph that needs to be run from a combo box selection. it doesnt seem to want to display any information at all. i have attached my database.
I have a bar graph on a report in ms access that represents 4 different risks ie count on the y axis and risk type on the x axis (low, moderate high and extreme)
Question: How do i get the each bar to automatically represent the colour based on the risk colours below?
Low: Green Moderate : Blue High : Yellow Extreme: Red
TRANSFORM Count([Copy of qryDailyCountOfValidations].Staff_Number) AS CountOfStaff_Number SELECT [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time] FROM [Copy of qryDailyCountOfValidations] GROUP BY [Copy of qryDailyCountOfValidations].PatternStartDate, [Copy of qryDailyCountOfValidations].[End Time] PIVOT Format([Start Time],"Short Time");
it shows the number of employees that start a shift at a particular time and the time the shift ends, on any day.What i need to show on a graph is the count number of employees starting at x time and plotting the number of hours the employees are on shift until they leave or another employee starts....
-the y axis is the count of employees -the x axis is the start time of their shift
10 9 8 7 xxxxxxxx 6 x x 5 x x 4 xxxxxxxx x 3 xxxxxxx x 2 xxxxxxxxxx 1 0 00:00 03:15 05:30.........................23:59
So the above mock up says 3 employees start at 00:00 and continue until 03:15 then another employee starts, then 4 employees continue 06:00, then 3 more employees start, 7 all together, then 5 employees leave later etc...
-the level of detail needed is to plot for every 15 minutes. -The [end time] is not strictly needed, just included to show what columns i have available
achieving this on a graph on an access 2010 report..i have updated a test database so you can see what the data looks like.
