Chart on report ocassionally displaying information

[nells1]

Hi All

I have a simple system.

Tables: (Fields)
Accounts (AccountNumber, AccountName)
SalesFigures (ID, AccountNumber, Year, Month, Value)
Months (MonthID, ShortName, LongName)

Reports:
SalesFiguresForAccounts

The SalesFiguresForAccounts report is based on Accounts with a chart that has the following crosstab query:

TRANSFORM Sum(SalesFigures.Value) AS SumOfValue 
SELECT Months.ShortName 
FROM Months INNER JOIN SalesFigures ON Months.MonthID = SalesFigures.Month 
WHERE (((Months.MonthID)=[SalesFigures].[Month])) 
GROUP BY SalesFigures.Month, Months.ShortName 
PIVOT SalesFigures.Year;

The LinkMasterFields and LinkChildFields are set to [AccountNumber].

It all appears to work well. But on closer inspection, some of the graphs appear to show another account's figures, (none of the accounts have the same figures). Its like that particular report page is not getting formatted correctly.

They print fine, but the user needs to look at the information before printing. I can't have incorrect figures showing up randomly.

The problem is more obvious if you're viewing 4 pages at once.

I have a system that I can send to whoever needs it. Any help would be appreciated..

Many thanks
Nells

 

[nells1]

Sorry the title for this thread was supposed to be: "Chart on report ocassionally displaying WRONG information

 

[SmallTime]

nells1

I had a very similar problem. As I understand it, it's to do with the chart being drawn before it's actually got all the data. The work around is to run a small loop to delay the formatting process and allow all the data to be processed.

Someone helped me out with this not to long ago although I can't recall his name now.

try this -

Use a DoEvents Loop
1. Create a new module.
2. Paste or type the following function into the new module:
3. Function TestProc()
4. Dim i As Integer
5. For i = 1 To 5
6. DoEvents
7. Next i
8. End Function

Because this behavior is related to timing, you may have to increase the number of iterations through the loop. Because the number of iterations that must be performed is variable, a maximum value is not provided. The number of iterations is dependent on such things as the complexity of the report, the complexity of the graph, or the number of records.
9. Save and then close the module.
10. Open your report in Design view.
11. Open the Properties window for the section that contains the graph object, and then click the Events tab.
12. For the On Format event, call the TestProc procedure that you created in step 2.

Hope it helps

 

[nells1]

SmallTime

Thank you so much for your post. It is exactly what I was looking for! I was really despairing over this one )

You've saved me HOURS!!

Cheers
Nells