chart does not reflect parameter querry

A

Andre C

OI am not solving this problem easily.

I wish to generate a report based on a querry and various parameters
set in a form. The querry "basicreportquerry" returns allk the
records in the dataset. I use a form with various listboxes and
comboboxes to enter criteria for several of the fields. Then I use the
following VBA to generate the sql statement.

Dim varItem As Variant
' the six list boxes which correspond to the report filters.
Dim strCCN As String
Dim strdisease As String
Dim strtopleveldisease As String
Dim strconsultant As String
Dim strPCT As String
Dim strReport As String


Dim strcaseload As String 'this variable stors the result of the
active caseload option group.

Dim strFilter As String 'this stores the sql phrase made fromthe above
variables




' for the CCN listbox
For Each varItem In Me.CCN.ItemsSelected
strCCN = strCCN & "," & Me.CCN.ItemData(varItem) _
& ""
Next varItem

If Len(strCCN) = 0 Then
strCCN = "Like '*'"
Else
strCCN = Right(strCCN, Len(strCCN) - 1)
strCCN = "IN(" & strCCN & ")"
End If

' for the Consultant list box
For Each varItem In Me.Consultant.ItemsSelected
strconsultant = strconsultant & "," &
Me.Consultant.ItemData(varItem) _
& ""
Next varItem

If Len(strconsultant) = 0 Then
strconsultant = "Like '*'"
Else
strconsultant = Right(strconsultant, Len(strconsultant) - 1)
strconsultant = "IN(" & strconsultant & ")"
End If

'for PCT list box
For Each varItem In Me.PCT.ItemsSelected
strPCT = strPCT & "," & Me.PCT.ItemData(varItem) _
& ""
Next varItem

If Len(strPCT) = 0 Then
strPCT = "Like '*'"
Else
strPCT = Right(strPCT, Len(strPCT) - 1)
strPCT = "IN(" & strPCT & ")"
End If

'for disease box.

For Each varItem In Me.disease.ItemsSelected
strdisease = strdisease & "," & Me.disease.ItemData(varItem) _
& ""
Next varItem

If Len(strdisease) = 0 Then
strdisease = "Like '*'"
Else
strdisease = Right(strdisease, Len(strdisease) - 1)
strdisease = "IN(" & strdisease & ")"
End If

'for top level disease box.

For Each varItem In Me.ToplevelDisease.ItemsSelected
strtopleveldisease = strtopleveldisease & "," &
Me.ToplevelDisease.ItemData(varItem) _
& ""
Next varItem

If Len(strtopleveldisease) = 0 Then
strtopleveldisease = "Like '*'"
Else
strtopleveldisease = Right(strtopleveldisease,
Len(strtopleveldisease) - 1)
strtopleveldisease = "IN(" & strtopleveldisease & ")"
End If

' for ioption group activecaseload

Select Case Me.activecaseload.Value
Case 1
strcaseload = "='Y'"
Case 2
strcaseload = "='N'"
Case 3
strcaseload = "Like '*'"
End Select

' now select the correct report style.

Select Case Me.reportname.Value
Case 1
strReport = "reportsummary"
Case 2
strReport = "reportsummary2"
Case 3
strReport = "reportsummary3"
Case 4
strReport = "reportsummary4"
Case 5
strReport = "reportsummary5"
Case 6
strReport = "reportsummary6_graphsex"
End Select

If SysCmd(acSysCmdGetObjectState, acReport, strReport) <>
acObjStateOpen Then
DoCmd.OpenReport strReport, acViewPreview
End If

' ************************
' now make the SQL statement
' ****************************

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [PCT-ID] " & strPCT & _
" AND [DiseaseID] " & strdisease & _
" AND [toplevelDiseaseID] " & strtopleveldisease & _
" AND [Active] " & strcaseload

' Apply the filter and switch it on
With Reports(strReport)
.Filter = strFilter
.FilterOn = True
' .txtReportTitle.Value = "Caseload Report - "
End With

As you can see the form is quite complex and allows the useer to
manipulate the querry in some depth. The problem is with the report
reportsummary6_graphsex which simply shows how many males and females
are in the querry. Note I don't allow the person to filter on male
female. What I want the graph to show is the ratio of male V female in
the sub querry. However the graph only shows all the males and
females. i.e. it does not apply the sql to the basic querry.

How do I make the graph change with each request?
 
D

Duane Hookom

The report's record source and filter don't apply to the chart's row source
other than by using the Link Master/Child properties.
You can use your code to build and replace the SQL of a saved query that
serves as the Row Source of the chart.

--
Duane Hookom
MS Access MVP

Andre C said:
OI am not solving this problem easily.

I wish to generate a report based on a querry and various parameters
set in a form. The querry "basicreportquerry" returns allk the
records in the dataset. I use a form with various listboxes and
comboboxes to enter criteria for several of the fields. Then I use the
following VBA to generate the sql statement.

Dim varItem As Variant
' the six list boxes which correspond to the report filters.
Dim strCCN As String
Dim strdisease As String
Dim strtopleveldisease As String
Dim strconsultant As String
Dim strPCT As String
Dim strReport As String


Dim strcaseload As String 'this variable stors the result of the
active caseload option group.

Dim strFilter As String 'this stores the sql phrase made fromthe above
variables




' for the CCN listbox
For Each varItem In Me.CCN.ItemsSelected
strCCN = strCCN & "," & Me.CCN.ItemData(varItem) _
& ""
Next varItem

If Len(strCCN) = 0 Then
strCCN = "Like '*'"
Else
strCCN = Right(strCCN, Len(strCCN) - 1)
strCCN = "IN(" & strCCN & ")"
End If

' for the Consultant list box
For Each varItem In Me.Consultant.ItemsSelected
strconsultant = strconsultant & "," &
Me.Consultant.ItemData(varItem) _
& ""
Next varItem

If Len(strconsultant) = 0 Then
strconsultant = "Like '*'"
Else
strconsultant = Right(strconsultant, Len(strconsultant) - 1)
strconsultant = "IN(" & strconsultant & ")"
End If

'for PCT list box
For Each varItem In Me.PCT.ItemsSelected
strPCT = strPCT & "," & Me.PCT.ItemData(varItem) _
& ""
Next varItem

If Len(strPCT) = 0 Then
strPCT = "Like '*'"
Else
strPCT = Right(strPCT, Len(strPCT) - 1)
strPCT = "IN(" & strPCT & ")"
End If

'for disease box.

For Each varItem In Me.disease.ItemsSelected
strdisease = strdisease & "," & Me.disease.ItemData(varItem) _
& ""
Next varItem

If Len(strdisease) = 0 Then
strdisease = "Like '*'"
Else
strdisease = Right(strdisease, Len(strdisease) - 1)
strdisease = "IN(" & strdisease & ")"
End If

'for top level disease box.

For Each varItem In Me.ToplevelDisease.ItemsSelected
strtopleveldisease = strtopleveldisease & "," &
Me.ToplevelDisease.ItemData(varItem) _
& ""
Next varItem

If Len(strtopleveldisease) = 0 Then
strtopleveldisease = "Like '*'"
Else
strtopleveldisease = Right(strtopleveldisease,
Len(strtopleveldisease) - 1)
strtopleveldisease = "IN(" & strtopleveldisease & ")"
End If

' for ioption group activecaseload

Select Case Me.activecaseload.Value
Case 1
strcaseload = "='Y'"
Case 2
strcaseload = "='N'"
Case 3
strcaseload = "Like '*'"
End Select

' now select the correct report style.

Select Case Me.reportname.Value
Case 1
strReport = "reportsummary"
Case 2
strReport = "reportsummary2"
Case 3
strReport = "reportsummary3"
Case 4
strReport = "reportsummary4"
Case 5
strReport = "reportsummary5"
Case 6
strReport = "reportsummary6_graphsex"
End Select

If SysCmd(acSysCmdGetObjectState, acReport, strReport) <>
acObjStateOpen Then
DoCmd.OpenReport strReport, acViewPreview
End If

' ************************
' now make the SQL statement
' ****************************

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [PCT-ID] " & strPCT & _
" AND [DiseaseID] " & strdisease & _
" AND [toplevelDiseaseID] " & strtopleveldisease & _
" AND [Active] " & strcaseload

' Apply the filter and switch it on
With Reports(strReport)
.Filter = strFilter
.FilterOn = True
' .txtReportTitle.Value = "Caseload Report - "
End With

As you can see the form is quite complex and allows the useer to
manipulate the querry in some depth. The problem is with the report
reportsummary6_graphsex which simply shows how many males and females
are in the querry. Note I don't allow the person to filter on male
female. What I want the graph to show is the ratio of male V female in
the sub querry. However the graph only shows all the males and
females. i.e. it does not apply the sql to the basic querry.

How do I make the graph change with each request?
Click to expand...
 
K

Kevin K. Sullivan

I haven't read through your whole post, but whenever I see "Graph" and
"Report" in a post, I remember an intermittent timing problem I had with
my report's graph until I put some magic code (suggested by SteveT in
this group) in the Detail's Print event:


'You will need to add the following code to the OnPrint section
'of the report where you placed the graph to make sure Access keeps the
'data in sync.
' - Steve T

Dim oGraph As Object
Me.Graph1.Requery
Set oGraph = Me!Graph1
oGraph.Refresh
oGraph.Object.Application.Update
Set oGraph = Nothing


Before I used this code, my graph often (but not always) printed the
default data that was saved in the Graph's design.

HTH,

Kevin

Andre said:
OI am not solving this problem easily.

I wish to generate a report based on a querry and various parameters
set in a form. The querry "basicreportquerry" returns allk the
records in the dataset. I use a form with various listboxes and
comboboxes to enter criteria for several of the fields. Then I use the
following VBA to generate the sql statement.

Dim varItem As Variant
' the six list boxes which correspond to the report filters.
Dim strCCN As String
Dim strdisease As String
Dim strtopleveldisease As String
Dim strconsultant As String
Dim strPCT As String
Dim strReport As String


Dim strcaseload As String 'this variable stors the result of the
active caseload option group.

Dim strFilter As String 'this stores the sql phrase made fromthe above
variables




' for the CCN listbox
For Each varItem In Me.CCN.ItemsSelected
strCCN = strCCN & "," & Me.CCN.ItemData(varItem) _
& ""
Next varItem

If Len(strCCN) = 0 Then
strCCN = "Like '*'"
Else
strCCN = Right(strCCN, Len(strCCN) - 1)
strCCN = "IN(" & strCCN & ")"
End If

' for the Consultant list box
For Each varItem In Me.Consultant.ItemsSelected
strconsultant = strconsultant & "," &
Me.Consultant.ItemData(varItem) _
& ""
Next varItem

If Len(strconsultant) = 0 Then
strconsultant = "Like '*'"
Else
strconsultant = Right(strconsultant, Len(strconsultant) - 1)
strconsultant = "IN(" & strconsultant & ")"
End If

'for PCT list box
For Each varItem In Me.PCT.ItemsSelected
strPCT = strPCT & "," & Me.PCT.ItemData(varItem) _
& ""
Next varItem

If Len(strPCT) = 0 Then
strPCT = "Like '*'"
Else
strPCT = Right(strPCT, Len(strPCT) - 1)
strPCT = "IN(" & strPCT & ")"
End If

'for disease box.

For Each varItem In Me.disease.ItemsSelected
strdisease = strdisease & "," & Me.disease.ItemData(varItem) _
& ""
Next varItem

If Len(strdisease) = 0 Then
strdisease = "Like '*'"
Else
strdisease = Right(strdisease, Len(strdisease) - 1)
strdisease = "IN(" & strdisease & ")"
End If

'for top level disease box.

For Each varItem In Me.ToplevelDisease.ItemsSelected
strtopleveldisease = strtopleveldisease & "," &
Me.ToplevelDisease.ItemData(varItem) _
& ""
Next varItem

If Len(strtopleveldisease) = 0 Then
strtopleveldisease = "Like '*'"
Else
strtopleveldisease = Right(strtopleveldisease,
Len(strtopleveldisease) - 1)
strtopleveldisease = "IN(" & strtopleveldisease & ")"
End If

' for ioption group activecaseload

Select Case Me.activecaseload.Value
Case 1
strcaseload = "='Y'"
Case 2
strcaseload = "='N'"
Case 3
strcaseload = "Like '*'"
End Select

' now select the correct report style.

Select Case Me.reportname.Value
Case 1
strReport = "reportsummary"
Case 2
strReport = "reportsummary2"
Case 3
strReport = "reportsummary3"
Case 4
strReport = "reportsummary4"
Case 5
strReport = "reportsummary5"
Case 6
strReport = "reportsummary6_graphsex"
End Select

If SysCmd(acSysCmdGetObjectState, acReport, strReport) <>
acObjStateOpen Then
DoCmd.OpenReport strReport, acViewPreview
End If

' ************************
' now make the SQL statement
' ****************************

strFilter = "[CCN-ID] " & strCCN & _
" AND [consultantID] " & strconsultant & _
" AND [PCT-ID] " & strPCT & _
" AND [DiseaseID] " & strdisease & _
" AND [toplevelDiseaseID] " & strtopleveldisease & _
" AND [Active] " & strcaseload

' Apply the filter and switch it on
With Reports(strReport)
.Filter = strFilter
.FilterOn = True
' .txtReportTitle.Value = "Caseload Report - "
End With

As you can see the form is quite complex and allows the useer to
manipulate the querry in some depth. The problem is with the report
reportsummary6_graphsex which simply shows how many males and females
are in the querry. Note I don't allow the person to filter on male
female. What I want the graph to show is the ratio of male V female in
the sub querry. However the graph only shows all the males and
females. i.e. it does not apply the sql to the basic querry.

How do I make the graph change with each request?
Click to expand...
 
A

Andre C

I haven't read through your whole post, but whenever I see "Graph" and
"Report" in a post, I remember an intermittent timing problem I had with
my report's graph until I put some magic code (suggested by SteveT in
this group) in the Detail's Print event:
Click to expand...
Tried that one but it does not work.
 
A

Andre C

The report's record source and filter don't apply to the chart's row source
other than by using the Link Master/Child properties.
You can use your code to build and replace the SQL of a saved query that
serves as the Row Source of the chart.
Click to expand...

Sorry can you explain that a different way.
 
D

Duane Hookom

You can save the chart's Row Source as a query "qryMyRptChart". Then use DAO
code to change the SQL of qryMyRptChart based on strFilter.
CurrentDb.QueryDefs("qryMyRptChart").SQL = "SELECT ... FROM ... WHERE " &
strFilter
 
A

Andre C

You can save the chart's Row Source as a query "qryMyRptChart". Then use DAO
code to change the SQL of qryMyRptChart based on strFilter.
CurrentDb.QueryDefs("qryMyRptChart").SQL = "SELECT ... FROM ... WHERE " &
strFilter
Click to expand...

I'll give that a go and report back.
 

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

Error 3085: Undefined function 'N' in expression. - Access 2010 1
Open report to current year 1
Crosstab Query Building Report 2
report based on selected items in list box(multiselect) 0
print previewing rpt based on selected items in list box(mulitsele 0
VBA Coding Help for Beginner 0
Wrong Report from Query 7
VBA Export to PDF 0

Top