Charts in reports

[Mauricio Silva]

I hope somebody can help me.

I notice there is a time delay between the opening the report and the
creation of the chart inside it. Is that right?

What I am trying to do is to apply to this chart a query I've created using
a form but, every time I try it, I get the error:

Run time error: 2455
You entered an expression that has an invalid reference to the property
RowSource

Is there a way to work around it?

Using Access 2K SP2 & Win 2K Pro

Thank you very much

Mauricio Silva

 

[Duane Hookom]

Do you mind sharing your code?
Have you considered changing the SQL of a saved query (your chart's row
source) prior to opening the report?

 

[Mauricio Silva]

What I have is a form, where I can select what to appear in the graphic and
chart object to preview it. I know it is working fine, because I can preview
the chart correctly.

== Begin of code in form
Public Function GenerateSQL() As String
Dim strData As String
Dim strSele As String
Dim strFrom As String
Dim strWher As String

strData = "SELECT MonthName, SumOfTime_LCBillable AS [Actual # of
Billable Days]," & _
[TextTargetLine] & " as [Target Line]" & _
" FROM [Billable Days - Monthly Attainment]" & _
" ORDER BY SortingMonth"

strData = "SELECT MS.MonthName, V1.Value " & _
"FROM [Graph - Month Sequence] AS MS LEFT JOIN [Graph -
Billable Days] AS V1 ON MS.SortingMonth = V1.SortingMonth " & _
"WHERE V1.Billable_FiscalYear = " & TextFiscalYear & _
" ORDER BY MS.SortingMonth"

strSele = "MS.MonthName, V1.Value as [Actual # of Billable days (" &
TextFiscalYear & ")]"
If ShowPreviousYear Then strSele = strSele & ", V2.Value as [Actual # of
Billable days (" & TextFiscalYear - 1 & ")]"
If ShowTargetLine Then strSele = strSele & ", " & Nz(TextTargetLine, 0)
& " AS [Target Line]"

strFrom = "[Graph - Month Sequence] AS MS LEFT JOIN [Graph - Billable
Days] AS V1 ON MS.SortingMonth = V1.SortingMonth"
If ShowPreviousYear Then strFrom = "(" & strFrom & ") LEFT JOIN [Graph -
Billable Days] AS V2 ON MS.SortingMonth = V2.SortingMonth"

strWher = "(V1.Billable_FiscalYear = " & TextFiscalYear & ")"
If ShowPreviousYear Then strWher = strWher & " AND
(V2.Billable_FiscalYear = " & TextFiscalYear - 1 & ")"

GenerateSQL = "SELECT " & strSele & " FROM " & strFrom & " WHERE " &
strWher & " ORDER BY MS.SortingMonth"
End Function

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click

Dim stDocName As String
stDocName = "Graph - Billable Days - Monthly Attainment"
Me.Visible = False
DoCmd.OpenReport stDocName, acPreview

Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox Err.Description
Resume Exit_btnPreview_Click

End Sub

================ End of code in form

My report contains only a chart object an the code:

==== Begin code in report
Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

Const FormName = "Graph - Billable Days - Monthly Attainment"

If Not IsLoaded(FormName) Then
DoCmd.OpenForm FormName, , , , , acDialog
End If

strSQL = Forms(FormName).GenerateSQL
DoCmd.Close acForm, FormName

On Error GoTo ErrorHandler
Graph1.RowSource = strSQL
Graph1.Requery

Exit Sub
ErrorHandler:
If Err.Number <> 2455 Then MsgBox "Error: " & Err.Number & vbCrLf &
Err.Description, vbOKOnly + vbExclamation
Resume Next

End Sub

=============== End code in reprot

Even when I tried to apply the rowsource directly (Chart1.rowsource =
MySelectCommand) I've got the same error.

 

[Duane Hookom]

I would make sure the chart has a row source property of a saved query. Then
use your code that generates your sql to apply this new sql to your saved
query.

CurrentDb.QueryDefs("qselRowSourceForChart").SQL = GenerateSQL()

Then open the report.

--
Duane Hookom
MS Access MVP
--

What I have is a form, where I can select what to appear in the graphic
and
chart object to preview it. I know it is working fine, because I can
preview
the chart correctly.

== Begin of code in form
Public Function GenerateSQL() As String
Dim strData As String
Dim strSele As String
Dim strFrom As String
Dim strWher As String

strData = "SELECT MonthName, SumOfTime_LCBillable AS [Actual # of
Billable Days]," & _
[TextTargetLine] & " as [Target Line]" & _
" FROM [Billable Days - Monthly Attainment]" & _
" ORDER BY SortingMonth"

strData = "SELECT MS.MonthName, V1.Value " & _
"FROM [Graph - Month Sequence] AS MS LEFT JOIN [Graph -
Billable Days] AS V1 ON MS.SortingMonth = V1.SortingMonth " & _
"WHERE V1.Billable_FiscalYear = " & TextFiscalYear & _
" ORDER BY MS.SortingMonth"

strSele = "MS.MonthName, V1.Value as [Actual # of Billable days (" &
TextFiscalYear & ")]"
If ShowPreviousYear Then strSele = strSele & ", V2.Value as [Actual #
of
Billable days (" & TextFiscalYear - 1 & ")]"
If ShowTargetLine Then strSele = strSele & ", " & Nz(TextTargetLine, 0)
& " AS [Target Line]"

strFrom = "[Graph - Month Sequence] AS MS LEFT JOIN [Graph - Billable
Days] AS V1 ON MS.SortingMonth = V1.SortingMonth"
If ShowPreviousYear Then strFrom = "(" & strFrom & ") LEFT JOIN
[Graph -
Billable Days] AS V2 ON MS.SortingMonth = V2.SortingMonth"

strWher = "(V1.Billable_FiscalYear = " & TextFiscalYear & ")"
If ShowPreviousYear Then strWher = strWher & " AND
(V2.Billable_FiscalYear = " & TextFiscalYear - 1 & ")"

GenerateSQL = "SELECT " & strSele & " FROM " & strFrom & " WHERE " &
strWher & " ORDER BY MS.SortingMonth"
End Function

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click

Dim stDocName As String
stDocName = "Graph - Billable Days - Monthly Attainment"
Me.Visible = False
DoCmd.OpenReport stDocName, acPreview

Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox Err.Description
Resume Exit_btnPreview_Click

End Sub

================ End of code in form

My report contains only a chart object an the code:

==== Begin code in report
Private Sub Report_Open(Cancel As Integer)
Dim strSQL As String

Const FormName = "Graph - Billable Days - Monthly Attainment"

If Not IsLoaded(FormName) Then
DoCmd.OpenForm FormName, , , , , acDialog
End If

strSQL = Forms(FormName).GenerateSQL
DoCmd.Close acForm, FormName

On Error GoTo ErrorHandler
Graph1.RowSource = strSQL
Graph1.Requery

Exit Sub
ErrorHandler:
If Err.Number <> 2455 Then MsgBox "Error: " & Err.Number & vbCrLf &
Err.Description, vbOKOnly + vbExclamation
Resume Next

End Sub

=============== End code in reprot

Even when I tried to apply the rowsource directly (Chart1.rowsource =
MySelectCommand) I've got the same error.

---
Mauricio Silva

Do you mind sharing your code?
Have you considered changing the SQL of a saved query (your chart's row
source) prior to opening the report?

--
Duane Hookom
MS Access MVP


message

 

[Mauricio Silva]

Duane,

I must say it: I have tears of happiness in my eyes !!! :~) It worked
smoooothly.

I had thought of doing it but, thanks to some wrong concepts, I didn't try.

Thank you very much

Mauricio Silva

 

[Mauricio Silva]

One more question...

What happens if I have more than one user using the form and report... won't
it have a conflict?

Duane,

I must say it: I have tears of happiness in my eyes !!! :~) It worked
smoooothly.

I had thought of doing it but, thanks to some wrong concepts, I didn't try.

Thank you very much

Mauricio Silva

 

[Duane Hookom]

Every user should have their own copy of the front-end mdb so this should
never be an issue.

--
Duane Hookom
MS Access MVP

One more question...

What happens if I have more than one user using the form and report... won't
it have a conflict?