INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Chart Headings from Field

Chart Headings from Field

(OP)
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

CODE -->

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

Anyone know how I can do this.

Using Access 2007

'Clever boy...'

RE: Chart Headings from Field

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

RE: Chart Headings from Field

(OP)
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...'

RE: Chart Headings from Field

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

RE: Chart Headings from Field

(OP)
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...'

RE: Chart Headings from Field

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

RE: Chart Headings from Field

(OP)
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

CODE

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

CODE

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...'

RE: Chart Headings from Field

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

RE: Chart Headings from Field

(OP)
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...'

RE: Chart Headings from Field

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

RE: Chart Headings from Field

(OP)
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...'

RE: Chart Headings from Field

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

CODE --> RecordSource

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"):

CODE --> Rowsource

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

RE: Chart Headings from Field

(OP)
Sorry Duane

I must be really dumb here.

CODE

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

CODE

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...'

RE: Chart Headings from Field

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close