MSGraph some times work and not - automation error
Hi,
MS Graph seems to be a never ending story. I use it for an Access application since many years ago. Now its behave differently even if it is the same code. Shortly, What I try to do is to present a graph for values (actually counts of something) and parts of that value of the totals. The later works fine, the first crash with the error
Errormsg.docx
The code is the following
Set dta = Grph.DataSheet
With dta
.Cells(iRow, iCol).Value = CStr([Common rec].Get_SubObjectTypeList
Select Case maxTotal
Case -1 ‘ value
.Cells(iRow, iCol + 1).Value = r.Fields(1)
Case -2
.Cells(iRow, iCol + 1).value = r.Fields(1)
Case Else
.Cells(iRow, iCol + 1).Value = r.Fields(1) / maxTotol
End Select
End with
Notice r.fields(1) is not NULL or text, it is number not texttype.
Sorry that it isn't so well formatted.
For the partsof case the id to text and case else works fine. For parts I crash at id to text or case -1 crash.
I have clearcontens etc but this the first data into the datasheet.
I have noticed that VBA can have problems to keep references. I have also tried to replace .cells with dta.cells and grph.datasheet.cells. The code line works if they are executed from the immitate window.
Thanks in advance.
B Anders
PS I am quite feed up with MS Graph, any suggestion of replacement?
MS Graph seems to be a never ending story. I use it for an Access application since many years ago. Now its behave differently even if it is the same code. Shortly, What I try to do is to present a graph for values (actually counts of something) and parts of that value of the totals. The later works fine, the first crash with the error
Errormsg.docx
The code is the following
Set dta = Grph.DataSheet
With dta
.Cells(iRow, iCol).Value = CStr([Common rec].Get_SubObjectTypeList
Select Case maxTotal
Case -1 ‘ value
.Cells(iRow, iCol + 1).Value = r.Fields(1)
Case -2
.Cells(iRow, iCol + 1).value = r.Fields(1)
Case Else
.Cells(iRow, iCol + 1).Value = r.Fields(1) / maxTotol
End Select
End with
Notice r.fields(1) is not NULL or text, it is number not texttype.
Sorry that it isn't so well formatted.
For the partsof case the id to text and case else works fine. For parts I crash at id to text or case -1 crash.
I have clearcontens etc but this the first data into the datasheet.
I have noticed that VBA can have problems to keep references. I have also tried to replace .cells with dta.cells and grph.datasheet.cells. The code line works if they are executed from the immitate window.
Thanks in advance.
B Anders
PS I am quite feed up with MS Graph, any suggestion of replacement?
ASKER
Hi,
first line: .Cells(iRow, iCol).Value = CStr([Common rec].Get_SubObjectTypeList
below case -1: .Cells(iRow, iCol + 1).Value = r.Fields(1)
During the first iteration it works on second not. The data it is trying to set is text string (always) respectively a number (likely a long and always at least zero. The funny thing is that if stop before line and do the line from the immitate window it works and directly if step into the line. Is it a doevents that might cure the problem?
Through the years similarly errors have occurred. Believe it or not - moved the code to another place. At least in my view the code is lengthy, approx. 500.000 lines.
B Anders
first line: .Cells(iRow, iCol).Value = CStr([Common rec].Get_SubObjectTypeList
below case -1: .Cells(iRow, iCol + 1).Value = r.Fields(1)
During the first iteration it works on second not. The data it is trying to set is text string (always) respectively a number (likely a long and always at least zero. The funny thing is that if stop before line and do the line from the immitate window it works and directly if step into the line. Is it a doevents that might cure the problem?
Through the years similarly errors have occurred. Believe it or not - moved the code to another place. At least in my view the code is lengthy, approx. 500.000 lines.
B Anders
<< Is it a doevents that might cure the problem?>>
Good chance of that. What you describe certainly sounds like a time dependent issue rather than being data driven. The fact that it errors when running vs works fine when you step through is a sure sign of that. Graph is a separate component actually and not a native Access control, so it may be doing something in the background and it is not quite ready all the time when you hit that statement.
If it were me, I would break down all the calls being made in that statement into separate ones (do getLanguage() first, then Get_SubObjectTypeListValue
I would also debug.print the values of iRow and iCol or (log them in some way), and put some checks in on the values before the statement executes.
But as you say, it executes fine when you step though it which makes it seem like it is not the data. I still wonder however if something there is occurring that is just not obvious.
Jim.
Good chance of that. What you describe certainly sounds like a time dependent issue rather than being data driven. The fact that it errors when running vs works fine when you step through is a sure sign of that. Graph is a separate component actually and not a native Access control, so it may be doing something in the background and it is not quite ready all the time when you hit that statement.
If it were me, I would break down all the calls being made in that statement into separate ones (do getLanguage() first, then Get_SubObjectTypeListValue
I would also debug.print the values of iRow and iCol or (log them in some way), and put some checks in on the values before the statement executes.
But as you say, it executes fine when you step though it which makes it seem like it is not the data. I still wonder however if something there is occurring that is just not obvious.
Jim.
Well if something was working and now its not then the most possible scenarios are
1. Your data...well everything might seem right,everything might "feel" right but they might be not alright...take your tables involved and look for "erroneous" data...probably a date that is not a date ....i have seen it many many times...a field that supposedly should be a number but it is text and of course missing primary keys...so start by right clicking your tables,check their design and move to validating the data...just order them ASC,DESC....something will popup...and of course start with a simple compact and repair or even better decompile and then compact & repair.
2. Some kind of system...change...an antivirus, a backup solution...anything that you introduced to your system and although might seems irrelevant ...well it might not
3. Network issues...the data feed is not constant...packets are lost and thus it bombs out...
1. Your data...well everything might seem right,everything might "feel" right but they might be not alright...take your tables involved and look for "erroneous" data...probably a date that is not a date ....i have seen it many many times...a field that supposedly should be a number but it is text and of course missing primary keys...so start by right clicking your tables,check their design and move to validating the data...just order them ASC,DESC....something will popup...and of course start with a simple compact and repair or even better decompile and then compact & repair.
2. Some kind of system...change...an antivirus, a backup solution...anything that you introduced to your system and although might seems irrelevant ...well it might not
3. Network issues...the data feed is not constant...packets are lost and thus it bombs out...
ASKER
Well, it can't be a network problem since everything is local. The records that used to populate the datasheet is strings and numbers and not Null or empty.
Since, Ifor many years 40 years ago, both developed and had support of OS, we already at that time had timing problem. Actually, it is possible have that in, today, very simple and small OS.
Sorry, I don't have for the moment time for this problem. But I will back in a couple of weeks since I need to sort out this problem.
BAnders
Since, Ifor many years 40 years ago, both developed and had support of OS, we already at that time had timing problem. Actually, it is possible have that in, today, very simple and small OS.
Sorry, I don't have for the moment time for this problem. But I will back in a couple of weeks since I need to sort out this problem.
BAnders
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
In general when that happens, it is a data driven error.
<<PS I am quite feed up with MS Graph, any suggestion of replacement?>>
Microsoft has added new charts in Access 2019:
https://techcommunity.microsoft.com/t5/Access-Blog/Introducing-new-and-modern-chart-types/ba-p/193479
But I think you'll be disappointed as there is very little exposed to VBA.
Your other choice would be to call Excel as an automation object and do the charting in that.
Jim.