Chart/Gaph question

[Guest]

ACCESS 2003 with WINXP.

Hello there,
I had thought I could get a chart/graph to change based on the
output from the open record set RS2(DAO). But I had problems getting it to
work.

Set rs2 = qdf2.OpenRecordset

with rs2

Me.Graph43.RowSource ="TRANSFORM Sum([Count]) AS [SumOfCount]" _
' tried to cut and paste this section from another
& "SELECT (Format([Date1],"MMM 'YY")) FROM [Hist]" _
' Chart's rowsource under it's properties....
& "GROUP BY (Year([Date1])*12 + Month([Date1])-1),(Format([Date1],"MMM
'YY"))" _ ' but it is not working here.
& "PIVOT [A-B-C];"

end with



Thank you,
Paul

 

[Rob Parker]

Well, it's late at night (for me), but since no-one's responded for almost
12 hours, here's a couple of comments.

Your code sets (or you're attempting to set) the rowSource of a Chart object
in a With construct based on a recordset which you've not described. But
regardless, the line which set the RowSource uses an SQL string, which does
not (and indeed cannot) refer to that recordset - it's a crosstab query
based on a table(or possible query) named "Hist". Regardless of what rs2
contains, the Rowsource will be that SQL statement.

The next point is that, after setting the RowSource for an object on a form,
you may need to use a Requery or Refresh command to ensure that the
displayed form changes to reflect the changed data source.

HTH,

Rob

 

[Guest]

Thanks for the reply Rob.....
This is what I came up with and it works.

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim dbs2 As DAO.Database
Dim qdf2 As DAO.QueryDef
Dim rs2 As Recordset
Dim strsql2 As String

DoCmd.DeleteObject acQuery, "test1" ' had to let one pass of test1
to allow to start
Set dbs2 = CurrentDb
strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 & """"
Set qdf2 = dbs2.CreateQueryDef("test1", strsql2)
Set rs2 = qdf2.OpenRecordset
Me.Graph48.Requery ' set the graph up
with first test1 query
rs2.Close
qdf2.Close
End Sub

I had thought there would be an easier way but as of now it runs
well....that is unless someone deletes the test1 query before opening the
form.

-Paul

Well, it's late at night (for me), but since no-one's responded for almost
12 hours, here's a couple of comments.

Your code sets (or you're attempting to set) the rowSource of a Chart
object in a With construct based on a recordset which you've not
described. But regardless, the line which set the RowSource uses an SQL
string, which does not (and indeed cannot) refer to that recordset - it's
a crosstab query based on a table(or possible query) named "Hist".
Regardless of what rs2 contains, the Rowsource will be that SQL statement.

The next point is that, after setting the RowSource for an object on a
form, you may need to use a Requery or Refresh command to ensure that the
displayed form changes to reflect the changed data source.

HTH,

Rob

ACCESS 2003 with WINXP.

Hello there,
I had thought I could get a chart/graph to change based on
the output from the open record set RS2(DAO). But I had problems
getting it to work.

Set rs2 = qdf2.OpenRecordset

with rs2

Me.Graph43.RowSource ="TRANSFORM Sum([Count]) AS [SumOfCount]" _
' tried to cut and paste this section from another
& "SELECT (Format([Date1],"MMM 'YY")) FROM [Hist]" _
' Chart's rowsource under it's properties....
& "GROUP BY (Year([Date1])*12 +
Month([Date1])-1),(Format([Date1],"MMM 'YY"))" _ ' but
it is not working here. & "PIVOT [A-B-C];"

end with



Thank you,
Paul

 

[Rob Parker]

Hi Paul,

What you're now doing is a reasonable way of getting your chart to change -
you're simply re-defining the query on which it's based. But your lines
which refer to your recordset are not actually doing anything.

An alternative way to do this is to simply assign your SQL string to the
RowSource of the chart object. This should work:

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim strsql2 As String

strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 & """"
Me.Graph48.RowSource = strsql2
Me.Graph48.Requery
End Sub

I find (testing in Access 2003) that it's not necessary to requery the chart
object, so you could remove that line also.

Is there any particular reason why you've got this code in the BeforeUpdate
event of the combo-box, rather than in its AfterUpdate event? The
BeforeUpdate event is normally used to perform validation on input, prior to
accepting the data; it has a Cancel parameter which can be used to cancel
the input if it's not correct. Here you seem to just want to base the chart
on a query using the new valaue in the combo-box - for that, the AfterUpdate
event would normally be used.

And a second thought: A common approach to doing this is to base the chart
on a query which includes the criteria from the form control; ie. your query
has a criteria set for the Company field, of "[Forms![test form]![Combo36]".
If you do it this way, you might find that the Requery line is all you need
in the Combo-box AfterUpdate event.

Again, HTH,

Rob

Thanks for the reply Rob.....
This is what I came up with and it works.

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim dbs2 As DAO.Database
Dim qdf2 As DAO.QueryDef
Dim rs2 As Recordset
Dim strsql2 As String

DoCmd.DeleteObject acQuery, "test1" ' had to let one pass of
test1 to allow to start
Set dbs2 = CurrentDb
strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 &
"""" Set qdf2 = dbs2.CreateQueryDef("test1", strsql2)
Set rs2 = qdf2.OpenRecordset
Me.Graph48.Requery ' set the graph
up with first test1 query
rs2.Close
qdf2.Close
End Sub

I had thought there would be an easier way but as of now it runs
well....that is unless someone deletes the test1 query before opening
the form.

-Paul

Well, it's late at night (for me), but since no-one's responded for
almost 12 hours, here's a couple of comments.

Your code sets (or you're attempting to set) the rowSource of a Chart
object in a With construct based on a recordset which you've not
described. But regardless, the line which set the RowSource uses an
SQL string, which does not (and indeed cannot) refer to that
recordset - it's a crosstab query based on a table(or possible
query) named "Hist". Regardless of what rs2 contains, the Rowsource
will be that SQL statement. The next point is that, after setting the
RowSource for an object on
a form, you may need to use a Requery or Refresh command to ensure
that the displayed form changes to reflect the changed data source.

HTH,

Rob

ACCESS 2003 with WINXP.

Hello there,
I had thought I could get a chart/graph to change based on
the output from the open record set RS2(DAO). But I had problems
getting it to work.

Set rs2 = qdf2.OpenRecordset

with rs2

Me.Graph43.RowSource ="TRANSFORM Sum([Count]) AS [SumOfCount]" _
' tried to cut and paste this section from another
& "SELECT (Format([Date1],"MMM 'YY")) FROM [Hist]" _
' Chart's rowsource under it's properties....
& "GROUP BY (Year([Date1])*12 +
Month([Date1])-1),(Format([Date1],"MMM 'YY"))" _ ' but
it is not working here. & "PIVOT [A-B-C];"

end with



Thank you,
Paul

 

[Guest]

Thanks again Rob,
I took your advice and moved it to the afterupdate event, but I think
more of what I am trying to do needs explanation.
My table "History" has [date] , [company] , [count] ,[A-B-C], plus several
others....so I tried to get this string close to what the graph had as it's
row
source.
Funny, after 2 errors(listed below) are clicked "ok", it will run but the
graph is blank.

Private Sub Combo36_AfterUpdate()
Dim strsql3 As String

strsql3 = " SELECT (Format([Date],MMM 'YY)),[Company],Sum([Count]) AS
[SumOfCount]" _
& " FROM History" _
& " WHERE [Company] = """ & Forms![test form]!Combo36 & """ " _
& " GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],MMM 'YY))" _
& " PIVOT [A-B-C] ;"
Me.Graph43.RowSource = strsql3 'row source type
Me.Graph43.Requery ' is Table/Query

End Sub
************************************************************************************************
Created error:
Syntax error(missing operator) in query expression 'strsql3

Then another error box:
A error occurred while sending data to the OLE server(the application used
to create the object)
************************************************************************************************

I am not sure but I believe this should work like you said Rob...but I am
not sure if the string can have all of the calculations I placed in it.

-Paul

Hi Paul,

What you're now doing is a reasonable way of getting your chart to
change - you're simply re-defining the query on which it's based. But
your lines which refer to your recordset are not actually doing anything.

An alternative way to do this is to simply assign your SQL string to the
RowSource of the chart object. This should work:

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim strsql2 As String

strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 & """"
Me.Graph48.RowSource = strsql2
Me.Graph48.Requery
End Sub

I find (testing in Access 2003) that it's not necessary to requery the
chart object, so you could remove that line also.

Is there any particular reason why you've got this code in the
BeforeUpdate event of the combo-box, rather than in its AfterUpdate event?
The BeforeUpdate event is normally used to perform validation on input,
prior to accepting the data; it has a Cancel parameter which can be used
to cancel the input if it's not correct. Here you seem to just want to
base the chart on a query using the new valaue in the combo-box - for
that, the AfterUpdate event would normally be used.

And a second thought: A common approach to doing this is to base the
chart on a query which includes the criteria from the form control; ie.
your query has a criteria set for the Company field, of "[Forms![test
form]![Combo36]". If you do it this way, you might find that the Requery
line is all you need in the Combo-box AfterUpdate event.

Again, HTH,

Rob

Thanks for the reply Rob.....
This is what I came up with and it works.

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim dbs2 As DAO.Database
Dim qdf2 As DAO.QueryDef
Dim rs2 As Recordset
Dim strsql2 As String

DoCmd.DeleteObject acQuery, "test1" ' had to let one pass of
test1 to allow to start
Set dbs2 = CurrentDb
strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 &
"""" Set qdf2 = dbs2.CreateQueryDef("test1", strsql2)
Set rs2 = qdf2.OpenRecordset
Me.Graph48.Requery ' set the graph
up with first test1 query
rs2.Close
qdf2.Close
End Sub

I had thought there would be an easier way but as of now it runs
well....that is unless someone deletes the test1 query before opening
the form.

-Paul

Well, it's late at night (for me), but since no-one's responded for
almost 12 hours, here's a couple of comments.

Your code sets (or you're attempting to set) the rowSource of a Chart
object in a With construct based on a recordset which you've not
described. But regardless, the line which set the RowSource uses an
SQL string, which does not (and indeed cannot) refer to that
recordset - it's a crosstab query based on a table(or possible
query) named "Hist". Regardless of what rs2 contains, the Rowsource
will be that SQL statement. The next point is that, after setting the
RowSource for an object on
a form, you may need to use a Requery or Refresh command to ensure
that the displayed form changes to reflect the changed data source.

HTH,

Rob


[email protected] wrote:
ACCESS 2003 with WINXP.

Hello there,
I had thought I could get a chart/graph to change based on
the output from the open record set RS2(DAO). But I had problems
getting it to work.

Set rs2 = qdf2.OpenRecordset

with rs2

Me.Graph43.RowSource ="TRANSFORM Sum([Count]) AS [SumOfCount]" _
' tried to cut and paste this section from another
& "SELECT (Format([Date1],"MMM 'YY")) FROM [Hist]" _
' Chart's rowsource under it's properties....
& "GROUP BY (Year([Date1])*12 +
Month([Date1])-1),(Format([Date1],"MMM 'YY"))" _ ' but
it is not working here. & "PIVOT [A-B-C];"

end with



Thank you,
Paul

 

[Rob Parker]

Hi Paul,

The first error is arising because your constructed SQL string is not valid;
I'm not sure exactly why, but I suspect that it's most likely a problem with
no quotes around the formatting string in the Format statements (and you'll
need to be carefull here because you'll need to insert quotes within a
quoted string, and you're also using the single-quote character as a chacter
within your formatting string, so you can't just use that as the delimiter
for the formatting string). My initial thought was that you'd not included
the space characters required within the string at the end of each line to
separate the words within the string, but I see that they're at the start of
each following line. There is no problem with the SQL string having
calculations in it - all it must be is a valid SQL string. To confirm that
it's valid and returning data as expected, set a break-point in your code
after the line which sets strsql3, then when the code stops print the value
of the string in the Immediate window (in this case, in the Immediate
window, type "? strsql3" (without the quote marks)), then cut/paste the
string to a new query (in SQL view) and run it to confirm that it gives the
expected result. If it doesn't, fix the problem in the query window, then
apply the fix to the code which is generating the SQL string.

The second error, and the reason the graph is blank, is because the QSL
string is invalid.

Again, HTH,

Rob

Thanks again Rob,
I took your advice and moved it to the afterupdate event, but I
think more of what I am trying to do needs explanation.
My table "History" has [date] , [company] , [count] ,[A-B-C], plus
several others....so I tried to get this string close to what the
graph had as it's row
source.
Funny, after 2 errors(listed below) are clicked "ok", it will run but
the graph is blank.

Private Sub Combo36_AfterUpdate()
Dim strsql3 As String

strsql3 = " SELECT (Format([Date],MMM 'YY)),[Company],Sum([Count]) AS
[SumOfCount]" _
& " FROM History" _
& " WHERE [Company] = """ & Forms![test form]!Combo36 & """ " _
& " GROUP BY (Year([Date])*12 + Month([Date])-1),(Format([Date],MMM
'YY))" _ & " PIVOT [A-B-C] ;"
Me.Graph43.RowSource = strsql3 'row source type
Me.Graph43.Requery ' is Table/Query

End Sub
************************************************************************************************
Created error:
Syntax error(missing operator) in query expression 'strsql3

Then another error box:
A error occurred while sending data to the OLE server(the application
used to create the object)
************************************************************************************************

I am not sure but I believe this should work like you said Rob...but
I am not sure if the string can have all of the calculations I placed
in it.
-Paul

Hi Paul,

What you're now doing is a reasonable way of getting your chart to
change - you're simply re-defining the query on which it's based. But
your lines which refer to your recordset are not actually doing
anything. An alternative way to do this is to simply assign your SQL
string to
the RowSource of the chart object. This should work:

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim strsql2 As String

strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 & """"
Me.Graph48.RowSource = strsql2
Me.Graph48.Requery
End Sub

I find (testing in Access 2003) that it's not necessary to requery
the chart object, so you could remove that line also.

Is there any particular reason why you've got this code in the
BeforeUpdate event of the combo-box, rather than in its AfterUpdate
event? The BeforeUpdate event is normally used to perform validation
on input, prior to accepting the data; it has a Cancel parameter
which can be used to cancel the input if it's not correct. Here you
seem to just want to base the chart on a query using the new valaue
in the combo-box - for that, the AfterUpdate event would normally be
used. And a second thought: A common approach to doing this is to base
the
chart on a query which includes the criteria from the form control;
ie. your query has a criteria set for the Company field, of
"[Forms![test form]![Combo36]". If you do it this way, you might
find that the Requery line is all you need in the Combo-box
AfterUpdate event. Again, HTH,

Rob

Thanks for the reply Rob.....
This is what I came up with and it works.

Private Sub Combo36_BeforeUpdate(Cancel As Integer)
Dim dbs2 As DAO.Database
Dim qdf2 As DAO.QueryDef
Dim rs2 As Recordset
Dim strsql2 As String

DoCmd.DeleteObject acQuery, "test1" ' had to let one pass of
test1 to allow to start
Set dbs2 = CurrentDb
strsql2 = "SELECT * FROM History " _
& "WHERE Company = """ & Forms![test form]!Combo36 &
"""" Set qdf2 = dbs2.CreateQueryDef("test1", strsql2)
Set rs2 = qdf2.OpenRecordset
Me.Graph48.Requery ' set the graph
up with first test1 query
rs2.Close
qdf2.Close
End Sub

I had thought there would be an easier way but as of now it runs
well....that is unless someone deletes the test1 query before
opening the form.

-Paul


message Well, it's late at night (for me), but since no-one's responded for
almost 12 hours, here's a couple of comments.

Your code sets (or you're attempting to set) the rowSource of a
Chart object in a With construct based on a recordset which you've
not described. But regardless, the line which set the RowSource
uses an SQL string, which does not (and indeed cannot) refer to
that recordset - it's a crosstab query based on a table(or possible
query) named "Hist". Regardless of what rs2 contains, the Rowsource
will be that SQL statement. The next point is that, after setting
the RowSource for an object on
a form, you may need to use a Requery or Refresh command to ensure
that the displayed form changes to reflect the changed data source.

HTH,

Rob


[email protected] wrote:
ACCESS 2003 with WINXP.

Hello there,
I had thought I could get a chart/graph to change based
on the output from the open record set RS2(DAO). But I had
problems getting it to work.

Set rs2 = qdf2.OpenRecordset

with rs2

Me.Graph43.RowSource ="TRANSFORM Sum([Count]) AS [SumOfCount]" _
' tried to cut and paste this section from another
& "SELECT (Format([Date1],"MMM 'YY")) FROM [Hist]" _
' Chart's rowsource under it's properties....
& "GROUP BY (Year([Date1])*12 +
Month([Date1])-1),(Format([Date1],"MMM 'YY"))" _ '
but it is not working here. & "PIVOT [A-B-C];"

end with



Thank you,
Paul