Graph based on a Query?

W

WildlyHarry

I have a large table that records new transaction data for 10 account types,
with hundreds of possible transaction types per acount type. I use a query
to aggregate the data. I have a chart based on the query that shows volume
trending from month to month. In this way I can monitor any unusal spikes in
volume. I would like to be able to create a form with combo boxes. The user
can choose account type and transaction type from the combo box. Those
choices then become the limits for the query that the chart is based on. The
chart then resides on the same form as a subform. However, when I use the
unbound combos on the form to limit the query I get an error from the chart.
Stating that it does not recognize the limits ie [forms]![form1]![tran1].
Does anyone know what is causing this?
 
M

Mark Andrews

I ran into the same problem. I ended up having to always build the sql at
runtime for the query that drives the graph, which works but is not the
ideal situation.

Sorry it's not the best answer,
I have SP1 of Access2007, perhaps in SP2 this is fixed?

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com
 
W

WildlyHarry

I came up with the same solution. Below is my code. The problem I am having
now is that when I send the SQL, the graph will not update until throw the
form into design view and back. Did you run into that as well?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("chart source")

Dim stdocname As String
Dim stdocname1 As String
Dim stdocname2 As String

stdocname = Me.Scenario.Value
stdocname1 = Me.Tran.Value
stdocname2 = Me.Desc.Value

qdf.sql = "SELECT [Step 1 - union all archive transactions].[Prcsng Dt],
IIf([Step 1 - union all archive transactions].[scnro displ nm]='Checks, MIs -
Sequential Number','Checks MIs - Sequential Number',[Step 1 - union all
archive transactions].[scnro displ nm]) AS [Scnro Displ Nm], [Step 1 - union
all archive transactions].[Trxn Type1 Cd], [Tran Codes].[Trxn Type2 Cd],
[Step 1 - union all archive transactions].[Number of Trans] " & _
"FROM [Step 1 - union all archive transactions] LEFT JOIN [Tran Codes] ON
([Step 1 - union all archive transactions].[Trxn Type2 Cd] = [Tran
Codes].[Trxn Type2 Cd]) AND ([Step 1 - union all archive transactions].[Trxn
Type1 Cd] = [Tran Codes].[Trxn Type1 Cd]) " & _
"WHERE ((([Step 1 - union all archive transactions].[Prcsng
Dt])>=#1/1/2009#) AND ((IIf([Step 1 - union all archive transactions].[scnro
displ nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step 1 - union all archive transactions].[scnro displ nm]))= '" &
[stdocname] & "' ) AND (([Step 1 - union all archive transactions].[Trxn
Type1 Cd])= '" & [stdocname1] & "' ) AND (([Tran Codes].[Trxn Type2 Cd])= '"
& [stdocname2] & "' )) " & _
"ORDER BY IIf([Step 1 - union all archive transactions].[scnro displ
nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential Number',[Step
1 - union all archive transactions].[scnro displ nm]), [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Step 1 - union all archive
transactions].[Prcsng Dt]; "

Me.Refresh
Me.Requery
Me.Repaint

Mark Andrews said:
I ran into the same problem. I ended up having to always build the sql at
runtime for the query that drives the graph, which works but is not the
ideal situation.

Sorry it's not the best answer,
I have SP1 of Access2007, perhaps in SP2 this is fixed?

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com




WildlyHarry said:
I have a large table that records new transaction data for 10 account
types,
with hundreds of possible transaction types per acount type. I use a
query
to aggregate the data. I have a chart based on the query that shows
volume
trending from month to month. In this way I can monitor any unusal spikes
in
volume. I would like to be able to create a form with combo boxes. The
user
can choose account type and transaction type from the combo box. Those
choices then become the limits for the query that the chart is based on.
The
chart then resides on the same form as a subform. However, when I use the
unbound combos on the form to limit the query I get an error from the
chart.
Stating that it does not recognize the limits ie [forms]![form1]![tran1].
Does anyone know what is causing this?
Click to expand...
Click to expand...
 
M

Mark Andrews

Checkout this article, there is a refreshing problem on Vista:
http://www.vb123.com.au/toolbox/09_access/access2007charting.htm

Your code looks close, you are building a string and assigning it to the
querydef. You should have some "close" and "set to nothing" in the mix.
Here's some sample code that makes a query:

Public Sub ExportQueryToExcel(QueryName As String, sql As String, filepath
As String)
On Error GoTo Err_ExportQueryToExcel
'exports the specified query to the specified filepath
'Note: if sql <> "" then populate query with sql and save first before
exporting

Dim dbs As Database
Dim dbQueryDef As DAO.QueryDef

If (sql <> "") Then
Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs(QueryName)
dbQueryDef.sql = sql
dbQueryDef.Close
End If
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, filepath
MsgBox "Export File Created: " & filepath, , "Export"

Exit_ExportQueryToExcel:
Set dbQueryDef = Nothing
Set dbs = Nothing
Exit Sub

Err_ExportQueryToExcel:
MsgBox Err.Description
Resume Exit_ExportQueryToExcel

End Sub


WildlyHarry said:
I came up with the same solution. Below is my code. The problem I am
having
now is that when I send the SQL, the graph will not update until throw the
form into design view and back. Did you run into that as well?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("chart source")

Dim stdocname As String
Dim stdocname1 As String
Dim stdocname2 As String

stdocname = Me.Scenario.Value
stdocname1 = Me.Tran.Value
stdocname2 = Me.Desc.Value

qdf.sql = "SELECT [Step 1 - union all archive transactions].[Prcsng Dt],
IIf([Step 1 - union all archive transactions].[scnro displ nm]='Checks,
MIs -
Sequential Number','Checks MIs - Sequential Number',[Step 1 - union all
archive transactions].[scnro displ nm]) AS [Scnro Displ Nm], [Step 1 -
union
all archive transactions].[Trxn Type1 Cd], [Tran Codes].[Trxn Type2 Cd],
[Step 1 - union all archive transactions].[Number of Trans] " & _
"FROM [Step 1 - union all archive transactions] LEFT JOIN [Tran Codes] ON
([Step 1 - union all archive transactions].[Trxn Type2 Cd] = [Tran
Codes].[Trxn Type2 Cd]) AND ([Step 1 - union all archive
transactions].[Trxn
Type1 Cd] = [Tran Codes].[Trxn Type1 Cd]) " & _
"WHERE ((([Step 1 - union all archive transactions].[Prcsng
Dt])>=#1/1/2009#) AND ((IIf([Step 1 - union all archive
transactions].[scnro
displ nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step 1 - union all archive transactions].[scnro displ nm]))= '" &
[stdocname] & "' ) AND (([Step 1 - union all archive transactions].[Trxn
Type1 Cd])= '" & [stdocname1] & "' ) AND (([Tran Codes].[Trxn Type2 Cd])=
'"
& [stdocname2] & "' )) " & _
"ORDER BY IIf([Step 1 - union all archive transactions].[scnro displ
nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step
1 - union all archive transactions].[scnro displ nm]), [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Step 1 - union all archive
transactions].[Prcsng Dt]; "

Me.Refresh
Me.Requery
Me.Repaint

Mark Andrews said:
I ran into the same problem. I ended up having to always build the sql
at
runtime for the query that drives the graph, which works but is not the
ideal situation.

Sorry it's not the best answer,
I have SP1 of Access2007, perhaps in SP2 this is fixed?

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com




WildlyHarry said:
I have a large table that records new transaction data for 10 account
types,
with hundreds of possible transaction types per acount type. I use a
query
to aggregate the data. I have a chart based on the query that shows
volume
trending from month to month. In this way I can monitor any unusal
spikes
in
volume. I would like to be able to create a form with combo boxes.
The
user
can choose account type and transaction type from the combo box. Those
choices then become the limits for the query that the chart is based
on.
The
chart then resides on the same form as a subform. However, when I use
the
unbound combos on the form to limit the query I get an error from the
chart.
Stating that it does not recognize the limits ie
[forms]![form1]![tran1].
Does anyone know what is causing this?
Click to expand...
Click to expand...
Click to expand...
 
W

WildlyHarry

That got it, thanks

Mark Andrews said:
Checkout this article, there is a refreshing problem on Vista:
http://www.vb123.com.au/toolbox/09_access/access2007charting.htm

Your code looks close, you are building a string and assigning it to the
querydef. You should have some "close" and "set to nothing" in the mix.
Here's some sample code that makes a query:

Public Sub ExportQueryToExcel(QueryName As String, sql As String, filepath
As String)
On Error GoTo Err_ExportQueryToExcel
'exports the specified query to the specified filepath
'Note: if sql <> "" then populate query with sql and save first before
exporting

Dim dbs As Database
Dim dbQueryDef As DAO.QueryDef

If (sql <> "") Then
Set dbs = CurrentDb()
Set dbQueryDef = dbs.QueryDefs(QueryName)
dbQueryDef.sql = sql
dbQueryDef.Close
End If
DoCmd.OutputTo acOutputQuery, QueryName, acFormatXLS, filepath
MsgBox "Export File Created: " & filepath, , "Export"

Exit_ExportQueryToExcel:
Set dbQueryDef = Nothing
Set dbs = Nothing
Exit Sub

Err_ExportQueryToExcel:
MsgBox Err.Description
Resume Exit_ExportQueryToExcel

End Sub


WildlyHarry said:
I came up with the same solution. Below is my code. The problem I am
having
now is that when I send the SQL, the graph will not update until throw the
form into design view and back. Did you run into that as well?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("chart source")

Dim stdocname As String
Dim stdocname1 As String
Dim stdocname2 As String

stdocname = Me.Scenario.Value
stdocname1 = Me.Tran.Value
stdocname2 = Me.Desc.Value

qdf.sql = "SELECT [Step 1 - union all archive transactions].[Prcsng Dt],
IIf([Step 1 - union all archive transactions].[scnro displ nm]='Checks,
MIs -
Sequential Number','Checks MIs - Sequential Number',[Step 1 - union all
archive transactions].[scnro displ nm]) AS [Scnro Displ Nm], [Step 1 -
union
all archive transactions].[Trxn Type1 Cd], [Tran Codes].[Trxn Type2 Cd],
[Step 1 - union all archive transactions].[Number of Trans] " & _
"FROM [Step 1 - union all archive transactions] LEFT JOIN [Tran Codes] ON
([Step 1 - union all archive transactions].[Trxn Type2 Cd] = [Tran
Codes].[Trxn Type2 Cd]) AND ([Step 1 - union all archive
transactions].[Trxn
Type1 Cd] = [Tran Codes].[Trxn Type1 Cd]) " & _
"WHERE ((([Step 1 - union all archive transactions].[Prcsng
Dt])>=#1/1/2009#) AND ((IIf([Step 1 - union all archive
transactions].[scnro
displ nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step 1 - union all archive transactions].[scnro displ nm]))= '" &
[stdocname] & "' ) AND (([Step 1 - union all archive transactions].[Trxn
Type1 Cd])= '" & [stdocname1] & "' ) AND (([Tran Codes].[Trxn Type2 Cd])=
'"
& [stdocname2] & "' )) " & _
"ORDER BY IIf([Step 1 - union all archive transactions].[scnro displ
nm]='Checks, MIs - Sequential Number','Checks MIs - Sequential
Number',[Step
1 - union all archive transactions].[scnro displ nm]), [Step 1 - union all
archive transactions].[Trxn Type1 Cd], [Step 1 - union all archive
transactions].[Prcsng Dt]; "

Me.Refresh
Me.Requery
Me.Repaint

Mark Andrews said:
I ran into the same problem. I ended up having to always build the sql
at
runtime for the query that drives the graph, which works but is not the
ideal situation.

Sorry it's not the best answer,
I have SP1 of Access2007, perhaps in SP2 this is fixed?

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com




I have a large table that records new transaction data for 10 account
types,
with hundreds of possible transaction types per acount type. I use a
query
to aggregate the data. I have a chart based on the query that shows
volume
trending from month to month. In this way I can monitor any unusal
spikes
in
volume. I would like to be able to create a form with combo boxes.
The
user
can choose account type and transaction type from the combo box. Those
choices then become the limits for the query that the chart is based
on.
The
chart then resides on the same form as a subform. However, when I use
the
unbound combos on the form to limit the query I get an error from the
chart.
Stating that it does not recognize the limits ie
[forms]![form1]![tran1].
Does anyone know what is causing this?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Creating a Chart on form from selected data in query. 0
Automation of scanned file storage based on content 0
Apply Month End date based on transaction date 5
Display message if specific query returns a value 4
Link a text box based on drop down box with four options MS Word 365 0
Open a form from a combobox on the switchboard based on a field inthe subform 1
Can I save a sql statement generated by a search form? 1
best way to graph stacked bar with line on two axes 0

Top