When row source sql saved as a query it stops working (1 Viewer)

[Rachael]

Hi All,

This is a bit hard to explain and I've searched for an answer to no avail. I think this might be a 2007 bug.

I have a report with a chart on it. The report is based on a query with criteria derived from a form. The chart also has it's row source (obviously) set to a query but until now hadn't been named ie in the row source line in the properties box there was the long line of sql.

The charts query is a crosstab and linked to the report via master/child settings.

My issue is the report opens fine when I leave it as is with the big long line of sql in the row source but when I simply go to the query editor and do Save As a name, close and try to run the report again it makes the chart blank.

I've tried everything I can think of, there is data in the query when you look at it in the query editor, I'm not getting any error messages, I've tried pasting the long sql back in to the row source...the report works...as soon as I save it ...it fails.

Anyone come across this before? It's driving me mad

Cheers, Rachael

 

[wiklendt]

can you post your long line of SQL?

my first feeling is that the data you've derived from the form would be referenced differently as a stand-alone query than as a rowsource (but i could be wrong, it's hard to say).

if you're deriving data from a form you must also ensure your form is open when you generate the report. (this has happened to me before - it took me a while to figure out what what going on b/c i was subconsciously ignoring the fact the form needed to be open...)

I've tried everything I can think of

like.... what?

 

[Rachael]

Hi Wiklendt,

PARAMETERS [Forms]![GetChart]![Year1] Long, [Forms]![GetChart]![Year1] Long; TRANSFORM Avg(CompGraphQry.Be) AS AvgOfBe
SELECT (Format([Date],"dd mmm")) AS Expr1
FROM CompGraphQry
WHERE (((CompGraphQry.Vintage) Between [Forms]![GetChart]![Year1] And [Forms]![GetChart]![Year2]))
GROUP BY (Format([Date],"dd mmm")), CompGraphQry.BlkID, (Int([Date])), CompGraphQry.Spare1, CompGraphQry.Vintage
PIVOT [Vintage] & " " & [SublkName];

I chopped it up so it was easier to read and thank you very much for having a look at this.

The above in the row source works perfectly when left as is but when I simply click on the little ... box at the end of the row source property and save is as a query (not the same name as anything else either) it doesn't work anymore.

I definatley do have the underlying form open. I've tried pasting the obove long line back into row source and the chart on the report will work but when I click the saved query of exactly the same thing into the row source it won't work.


Normally I wouldn't worry about it as 'Don't fix something if it ain't broke' but I really need it to be a named query so I can manipulate it later with vba.

Thank you very much for your help,

Rachael

 

[Rachael]

Hi Wiklendt,

Your post gave me some ideas and I foun this on the Microsoft website but am unsure if this is the cause of my problem. Your help is much appreciated.

"Changes to the underlying data (such as the format of the data) have precedence over other changes. However, if the RowSource property setting contains a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.), such as =Format([StartDate],"MMM"), the format is determined by the result of the calculation, not by the underlying data."

Kind regards,

Rachael

 

[wiklendt]

... oh, and after you save as a query, did you then refer to that query in your control source for the chart

 

[Rachael]

yep, this is wrong isn't it?

 

[Banana]

It sounds to me that AutoCorrupt may be at play here.

Try this and see if it fixes the problem:

Create a new blank database.
Turn off the AutoCorrupt... Erm, I mean AutoCorrect feature.
Import in all objects from your current database.

See if this works now.

 

[wiklendt]

Hi Wiklendt,

Your post gave me some ideas and I foun this on the Microsoft website but am unsure if this is the cause of my problem. Your help is much appreciated.

"Changes to the underlying data (such as the format of the data) have precedence over other changes. However, if the RowSource property setting contains a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.), such as =Format([StartDate],"MMM"), the format is determined by the result of the calculation, not by the underlying data."

Kind regards,

Rachael

i would be surprised if this is causing the problem. having said that, i've had date formatting cause issues in other ways (which i couldn't fix) - have you tried this query without formatting the dates? to see if that's the problem after-all?

 

[Rachael]

Yeah, I've tried that, sorry to take up your time I think I'm having a brain breakdown, will try again tommorrow and post of my results thank you.

 

[wiklendt]

hmmm... i think i can see something....

try changing "GROUP BY (Format ...)" to "GROUP BY Expr1" (or if you want to change the Expr1 to a more descriptive field name, change it in both places - BE AWARE that "DATE" is an Access Reserved Word - it's possible this is causing issues too)

if that doesn't help, also, when you "SELECT", you have to select all those fields (as far as i'm aware) that you are going to use in your query - so, i see that you are only 'selecting' the date field, but are wanting to filter / group / sort by other fields. i'd add the other fields reference in your query into the "SELECT" portion of it (before you reach "FROM") see if access allows you to go into design view of that saved query - sometimes when i do this i am shown an error message, which is sometimes helpful to discovering what's wrong.

 

[DCrake]

Wiklendt,

I noticed that you have been having problems in the past with passing arguments to queries which are reliant on open forms. Seems to be quite popular at the moment. Anyway I havbe taken the liberty of attaching a link to a document I posted a while ago regarding this issue and how to overcome this. At present I have not supplied a sample mdb to accompy it, but i think I may well do. Hope this gives you an insight on these issues.

David
Link

Edit:
If have scrambled together a simple demo to show you how it all goes together.

 

[gemma-the-husky]

PARAMETERS [Forms]![GetChart]![Year1] Long, [Forms]![GetChart]![Year1] Long; TRANSFORM Avg(CompGraphQry.Be) AS AvgOfBe
SELECT (Format([Date],"dd mmm")) AS Expr1
FROM CompGraphQry
WHERE (((CompGraphQry.Vintage) Between [Forms]![GetChart]![Year1] And [Forms]![GetChart]![Year2]))
GROUP BY (Format([Date],"dd mmm")), CompGraphQry.BlkID, (Int([Date])), CompGraphQry.Spare1, CompGraphQry.Vintage
PIVOT [Vintage] & " " & [SublkName];

is this a typo - year1 twice at the top, but wanting year2 in the query, or is this part of the problem

 

[wiklendt]

thanks DCrake... i'm still learning - and more quickly with help from the fabulous people on these forums

 

[Rachael]

Gemma, yep that's a typo, sorry.

DCrake have downloaded your examples, will apply these and post my result.

I still can't understand though why a Row Source on a chart can be the long sql thing in the properties sheet but if you click on the ... and save as a query so that the Row Source then has the name of the query (which is exactly the same) in it instead of the long SQL stuff, it doesn't work.

Thanks to you all for looking at my problem,

Rachael

 

[wiklendt]

can you post your database?

 

[Rachael]

Thank you very much for looking at this, I'm sure I'm doing something really stupid!!

Also I apologise for some of the names used, I've learnt alot over the years about naming conventions, I've been developing this database for almost 10 years now so unfortunately some of the early stuff still hangs around.

Basically double-click to open and follow thru to preview the maturity chart, which works fine.

Then go to the report and give the chart objects row source an actual query name (save the row source statemnt as a query) and try again.

Thank you very much

Rachael

 

[wiklendt]

Thank you very much for looking at this, I'm sure I'm doing something really stupid!!

i'm sure you're not. don't worry, we'll get to the bottom of this

 

[boblarson]

Normally I wouldn't worry about it as 'Don't fix something if it ain't broke' but I really need it to be a named query so I can manipulate it later with vba.

Actually, I don't think you're going to be successful at saving the query and using it like that. I tried and failed too. But, just because you don't have it as a saved query doesn't mean you won't be able to manipulate it later with VBA as it should be able to be accessed as the Graph27.RowSource.

 

[wiklendt]

Actually, I don't think you're going to be successful at saving the query and using it like that. I tried and failed too. But, just because you don't have it as a saved query doesn't mean you won't be able to manipulate it later with VBA as it should be able to be accessed as the Graph27.RowSource.

AH, nice tip...

can you try the VBA manipulation, rachael, using boB's tip and see if that works for you?

if boB says it can't be done, then i'd tend to believe him... unless by some freak event it happens that there's a bizzare and unheard of work-around...

 

[Rachael]

Hi Wiklendt and Bob,

Sorry I haven't replied earlier, I've got a vineyard here in Oz and we're right in the middle of harvest so have been picking grapes for the last week.

Thanks for looking at this, I guess sometimes you just need to accept that somethings can't be done.

I have tried the rowsource method suggested by Bob as what I'm ultimately trying to do with this graph is place a data label on some plot points and not others. When I open the recordset in vba using Graph27.rowsource I get too few parameters message so was wanting to be able to rewrite the query programatically for the graph rowsource to eliminate the too few paramters message but as I can't save it as a name I don't know how to get the recordset open without the parameter message.

Hope this makes sense.

Kind regards,

Rachael