Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 18th, 2007, 04:29 PM
Registered User
 
Join Date: Jan 2007
Location: Portland, Oregon, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Reporting with MSGraph.Chart.8

Ok, here is the rundown.

I have created a report. This report has 16 graphs. Each of these graphs references the same query to show the data. All I want to do, is upon opening the report, change the filter for each chart so that each chart represents 1 person from the query. (The query can have 1 to 16 people depending on the selected date range.)

Here is my code that is in the Report_Open Event


Dim rs As New ADODB.Recordset
Dim name As String
Dim sRowSource As String
Dim ctrl As Access.Control

rs.Open "Select distinct operator from qryblohmproductivitytotal
     order by operator", CurrentProject.Connection, adOpenStatic,
     adLockReadOnly

rs.MoveFirst

For Each ctrl In Me.Report.Controls
  If Left(ctrl.Properties(1).Value, 5) = "Graph" Then
    If rs.EOF = False Then
      name = rs.Fields(0)
      sRowSource = "TRANSFORM Sum
      (qryBlohmProductivityTotal.SumOfBlohmPersonnelProd uctivity)
      AS SumOfSumOfBlohmPersonnelProductivity SELECT
      (Format([Date],""Short Date"")) AS Expr1 FROM
      ryBlohmProductivityTotal INNER JOIN tblOperator ON
      qryBlohmProductivityTotal.Operator = tblOperator.Name WHERE
      (((qryBlohmProductivityTotal.Operator)=""" & name & """))
      GROUP BY (Int([Date])), (Format([Date],""Short Date"")) PIVOT
      qryBlohmProductivityTotal.Operator;"

      ctrl.Properties.Item(7).Value = sRowSource
      rs.MoveNext
    Else
      ctrl.Properties.Item(21).Value = False
    End If
  End If
Next


When the report is run by clicking the button I get the following error :
Run-time error '2113':
The value you entered isn't valid for this field.

I press Debug, then I press continue and the form loads properly with everything just as I want it.

I think that the Graph object is taking too long to load which is why this error is occuring. Can anyone stear me into the correct direction to fix this!

Thanks


John P. Greiner
jpg@keyknife.com
Reply With Quote
  #2 (permalink)  
Old January 19th, 2007, 08:22 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Is this: "ctrl.Properties.Item(21).Value = False" a boolean?


mmcdonal
Reply With Quote
  #3 (permalink)  
Old January 19th, 2007, 12:16 PM
Registered User
 
Join Date: Jan 2007
Location: Portland, Oregon, USA.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, it is a boolean. (It is the Visability Property) I should have annotated where the code stops working. It halts at:

ctrl.Properties.Item(7).Value = sRowSource

John P. Greiner
jpg@keyknife.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reporting gunjan.sh SQL Language 0 February 17th, 2008 11:37 PM
Chart keso VB How-To 2 November 30th, 2006 04:51 AM
Chart Visibility baaul Reporting Services 1 September 28th, 2006 03:53 PM
Using Customized chart for Pivot Chart kliu9 Excel VBA 3 October 13th, 2004 09:37 AM
Drill Down from Bar Chart to another chart snowbird Crystal Reports 0 July 11th, 2003 01:53 PM



All times are GMT -4. The time now is 02:57 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.