Chart Headings from Field

[jeffwest21]

I have a bar chart, where what will be on it will differ each time the report is run, what I want to do is name the elements dynamically from a field on the report, I have tried adding an alias to the query, noting the form filed I want to use, but this just errors with [Reports]![Report_Name]![Task_1_Description] is not a valid name

[Reports]![Report_Name]![Task_1_Description]:Task_1_Level

Anyone know how I can do this.

Using Access 2007

'Clever boy...'

 

[dhookom]

Apparently you have a chart that you want to dynamically change the title based on a field in the report's record source. If this is the case, why not remove the chart title and place a text box in the report on top of the chart.

Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

Sorry, I will try and explain better.

I have a form that is filled in by a person, the details of that form are passed to a report which is emailed to that person.

On the form are four drop downs, containing 30 different items from which they can choose from.

The query pulls the bulk of the data from a SQL table where it is placed after submission.

At the moment the chart is showing Task 1, Task 2, Task 3, Task 4

What I have been asked to do is replace those values with

Sending Email, White post etc, or what ever the actual name of the task is, hence I have a field on the report that has this information ([Reports]![Report_Name]![Task_1_Description]: ), I am trying to work out how to add that dynamically to the chart each time, as they will never be the same twice running.

'Clever boy...'

 

[dhookom]

How are "the details of that form are passed to a report"? Is there a table involved or just unbound controls?

Where are the charts displaying "Task 1, Task 2, Task 3, Task 4"? Is this in chart or axis titles?

You don't have "field on the report". You probably have a text box on the report that you want to display a value. Where exactly should that value come from?

Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

The details for the report are passed by the id being pushed to the report when it is opened via

strReport = "Agent_Report_FT_Admin"
strWhereCondition = "TempID = " & Me.Number
DoCmd.OpenReport strReport, acViewReport, , strWhereCondition

The Task 1 etc.. is the x axis headings for the elements

The query pulls the rest of the data through from the SQL table based on the id that is sent from the form, this includes the majority of the data that the report is populated by..

The descriptions are added to a text box on the report at the moment as I cannot add these into the axis headings, what I what to do is hide these and instead use the field to name the axis.

'Clever boy...'

 

[dhookom]

I'm still ultra confused. Maybe it would help if you provide the record source of the report, the Row Source of the chart(s), and if they are linked master/child.

How do "form are four drop downs" get passed to the report if there is only one value in strWhereCondition?

Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

Duane

I know you are trying to help here, I have said here how the report is populated.

The query pulls the rest of the data through from the SQL table based on the id that is sent from the form, this includes the Task 1 description which is a field in the table from where the data is got.

The report row source is

SELECT dbo_New_FTA_Score.ID, dbo_New_FTA_Score.Agent_Name, dbo_New_FTA_Score.Admin_Name, dbo_New_FTA_Score.Campaign_Name, dbo_New_FTA_Score.Monitor_Date, dbo_New_FTA_Score.Monitor_Time, dbo_New_FTA_Score.Monitor_Comments, dbo_New_FTA_Score.TempID FROM dbo_New_FTA_Score;

The chart's row source is this

SELECT dbo_New_FTA_Score.Agent_Name, dbo_New_FTA_Score.Task_1, dbo_New_FTA_Score.Task_2, dbo_New_FTA_Score.Task_3, dbo_New_FTA_Score.Task_4 FROM dbo_New_FTA_Score. WHERE (((dbo_New_FTA_Score.TempID)=[Reports]![Agent_Report_FT_Admin]![TempID]));

'Clever boy...'

 

[dhookom]

What is [Reports]![Report_Name]![Task_1_Description] and where does its value come from? There is no report named Report_Name and there is no column in your report's record source named Task_1_Description.

I think one of the roots of your problem is an un-normalized table with field names like "Task_X".

Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

Ok, maybe I haven't tried to tell you what is happening correctly, while I do not have a pull to the Task_1_Description, this is because the code snippet I gave you doesn't include it, however, please assume the following.

I have a text box on the report that has a field called Text_Data
The text box will be populated by the main query for the report based on ID of the record being sent by the form when it is submitted.
The query uses the ID to get the data and populate the chart and the report (This has various other text boxes on it which are populated by this query)
The value of that field I would like to show as a X axis header on my chart is the value in the text box Text_Data.

Is this possible?


'Clever boy...'

 

[dhookom]

I'm not seeing where Text_Data gets its value.

My thought is the value you want to see in the chart X axis title needs to come from data in the chart's row source. You haven't connected the dots for me.

An alternative might be to use code to change the value. You can create a similar chart in Excel, turn on the macro recorder, change the value for the title, and turn off the recorder. The code that is created can be altered and added to the On Format event of the section of your report that contains the chart.



Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

I know you are trying to help, and I do appreciate it, but I can't really give you any more info.

Below is the part of the above post where I say where the text box Text_Data get's it's value

I have a text box on the report that has a field called Text_Data
The text box will be populated by the main query for the report based on ID of the record being sent by the form when it is submitted.

Can you help me understand why this isn't clear so that I can post better in the future?

'Clever boy...'

 

[dhookom]

I don't see any "field" in the "main query for the report" named "Text_Data". This is the record source you provided.

SELECT ID, Agent_Name, Admin_Name, Campaign_Name, 
Monitor_Date, Monitor_Time, Monitor_Comments, TempID 
FROM dbo_New_FTA_Score;

If Text_Data is the name of a control it probably has a control source tied to a field, function, or calculation. If the control source is blank then it is probably filled in with code at run-time.

My first suggestion was to get the source of Text_Data into the row source of the chart so it could be used to provide the axis title. For the sake of readability, this is what you provided as the Row Source of the chart (I removed the stray period after "FROM dbo_New_FTA_Score"):

SELECT Agent_Name, Task_1, Task_2, Task_3, Task_4 
FROM dbo_New_FTA_Score 
WHERE TempID=[Reports]![Agent_Report_FT_Admin]![TempID];

Since I wasn't making any progress trying to find out the source of Text_Data, I thought you could use vba code to dynamically change the axis title. I provided the method I would use but you haven't responded whether or not you tried or understood the method.

Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

Sorry Duane

I must be really dumb here.

SELECT ID, Agent_Name, Admin_Name, Campaign_Name, 
Monitor_Date, Monitor_Time, Monitor_Comments, task_1_description,TempID 
FROM dbo_New_FTA_Score;

If I make the adjustment as above, so bringing in the Task_1_Description into the main body of my data, add this to a text box called Text_Data by making the value of the text box to be Text_1_Description, then try and reference this in my chart code as below

SELECT Agent_Name, Task_1 As [Report]![Report_Name]![Text_Data], Task_2, Task_3, Task_4 
FROM dbo_New_FTA_Score 
WHERE TempID=[Reports]![Agent_Report_FT_Admin]![TempID];

Is when I get the initial error that I started with '[Reports]![Report_Name]![Task_1_Description] is not a valid name'

This is what I am trying to do.

I have not had a chance to ay to try the vba method that you suggested, although I am not sure that this will work the way I want.

'Clever boy...'

 

[dhookom]

I haven't suggested anything you just attempted. I still don't understand where Task_1_Description or Text_Data come from. Maybe your fields and tables are structured such that I can't picture what you are attempting to do.

Just remove the X axis title and place your text box on top of the chart.

Duane
Hook'D on Access
MS Access MVP