Using VBA to filter a chart

A

Alex

I really need help with this. I've posted a few questions, but not having
any luck. I have a report that is filtered by an input form that contains a
multi-select list box. Normally I would use a query to filter the data then
use that query as the report data source. Since I need users to be able to
select more than one choice in a list box, I can't use the query. My user
input form contains a button with the following code that works great to
filter the report.

Here's my problem: I have a chart that I need to put in the same report.
When I run the report, the chart is not filtered the same as the report. How
can I use vba to filter my chart? Is it as simple as changing ' Apply the
filter and switch it on With Reports![LocationsReportbyStorage].Filter =
strFilter.FilterOn = True End With End Sub? I'm totally stuck on this and
will appreciate any help you can give. I'm very new to vba. Thank you.

Private Sub cmdApplyFilter_Click()
Dim VarItem As Variant
Dim strStore As String
Dim strLocation As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strFilter As String
' Check for Location
If Len(Me.cmdLocation.Value & "") = 0 Then
MsgBox "You must pick a location"
Exit Sub
End If

'Check for Beginning and Ending date
If Len(Me.cmdBeginDate.Value & "") = 0 Then
MsgBox "You must type a Beginning date"
Exit Sub
End If

If Len(Me.cmdEndDate.Value & "") = 0 Then
MsgBox "You must type an Ending date"
Exit Sub
End If


' Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "LocationsReportbyStorage")
<> acObjStateOpen Then
DoCmd.OpenReport "LocationsReportbyStorage", acViewPreview
End If

' Build criteria string from StoreRoom listbox
For Each VarItem In Me.cmdStoreRoom.ItemsSelected
strStore = strStore & ",'" & Me.cmdStoreRoom.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strStore) = 0 Then
strStore = "Like '*'"
Else
strStore = Right(strStore, Len(strStore) - 1)
strStore = "IN(" & strStore & ")"
End If

' Build criteria string from Location option group
Select Case Me.cmdLocation.Value
Case 1
strLocation = "='1'"
Case 2
strLocation = "='2'"

End Select

'Build Beginning & Ending date parameter
If Not IsNull(Me.cmdBeginDate) Then
datBeginDate = Me.cmdBeginDate
End If
If Not IsNull(Me.cmdEndDate) Then
datEndDate = Me.cmdEndDate
End If

' Build filter string
strFilter = "[StorageRoom] " & strStore & " AND [Location] " &
strLocation & " AND [Date] Between #" & datBeginDate & "# and #" & datEndDate
& "#"


' Apply the filter and switch it on
With Reports![LocationsReportbyStorage]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 
D

David Lloyd

Alex:

Some further elaboration on the response I gave you previously.

Say the query behind the chart is called qryChart and you have a second
query called qryChart_UnFiltered, which is the query with all of your data
unfiltered (probably what you are using currently). You could use DAO to
reference the qryChart_UnFiltered querydef, then assign its SQL property to
a string. You could then append a WHERE statement to the string, and then
assign this filtered query string to the SQL string property of your
qryChart, again through a DAO querydef reference.

It would look something like the following although I am not guaranteeing
this to be a complete solution for you, as I do not know all the specifics.

Dim db as Database
Dim qdf_Chart as QueryDef
Dim qdf_Unfiltered as QueryDef
Dim sql as String

Set db = Currentdb
Set qdf_Unfiltered = db.QueryDefs("qryChart_Unfiltered")
Set qdf_Chart = db.QueryDefs("qryChart")

sql = qdf_Unfiltered.SQL

'This is strFilter from your routine
sql = sql & " WHERE " & strFilter

qdf_Chart.SQL = sql

If you already have a WHERE clause in your unfiltered data, you will have to
adjust accordingly.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I really need help with this. I've posted a few questions, but not having
any luck. I have a report that is filtered by an input form that contains a
multi-select list box. Normally I would use a query to filter the data then
use that query as the report data source. Since I need users to be able to
select more than one choice in a list box, I can't use the query. My user
input form contains a button with the following code that works great to
filter the report.

Here's my problem: I have a chart that I need to put in the same report.
When I run the report, the chart is not filtered the same as the report.
How
can I use vba to filter my chart? Is it as simple as changing ' Apply the
filter and switch it on With Reports![LocationsReportbyStorage].Filter =
strFilter.FilterOn = True End With End Sub? I'm totally stuck on this and
will appreciate any help you can give. I'm very new to vba. Thank you.

Private Sub cmdApplyFilter_Click()
Dim VarItem As Variant
Dim strStore As String
Dim strLocation As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strFilter As String
' Check for Location
If Len(Me.cmdLocation.Value & "") = 0 Then
MsgBox "You must pick a location"
Exit Sub
End If

'Check for Beginning and Ending date
If Len(Me.cmdBeginDate.Value & "") = 0 Then
MsgBox "You must type a Beginning date"
Exit Sub
End If

If Len(Me.cmdEndDate.Value & "") = 0 Then
MsgBox "You must type an Ending date"
Exit Sub
End If


' Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "LocationsReportbyStorage")
<> acObjStateOpen Then
DoCmd.OpenReport "LocationsReportbyStorage", acViewPreview
End If

' Build criteria string from StoreRoom listbox
For Each VarItem In Me.cmdStoreRoom.ItemsSelected
strStore = strStore & ",'" & Me.cmdStoreRoom.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strStore) = 0 Then
strStore = "Like '*'"
Else
strStore = Right(strStore, Len(strStore) - 1)
strStore = "IN(" & strStore & ")"
End If

' Build criteria string from Location option group
Select Case Me.cmdLocation.Value
Case 1
strLocation = "='1'"
Case 2
strLocation = "='2'"

End Select

'Build Beginning & Ending date parameter
If Not IsNull(Me.cmdBeginDate) Then
datBeginDate = Me.cmdBeginDate
End If
If Not IsNull(Me.cmdEndDate) Then
datEndDate = Me.cmdEndDate
End If

' Build filter string
strFilter = "[StorageRoom] " & strStore & " AND [Location] " &
strLocation & " AND [Date] Between #" & datBeginDate & "# and #" &
datEndDate
& "#"


' Apply the filter and switch it on
With Reports![LocationsReportbyStorage]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 
A

Alex

Thank you David, that got me started in the right direction.

David Lloyd said:
Alex:

Some further elaboration on the response I gave you previously.

Say the query behind the chart is called qryChart and you have a second
query called qryChart_UnFiltered, which is the query with all of your data
unfiltered (probably what you are using currently). You could use DAO to
reference the qryChart_UnFiltered querydef, then assign its SQL property to
a string. You could then append a WHERE statement to the string, and then
assign this filtered query string to the SQL string property of your
qryChart, again through a DAO querydef reference.

It would look something like the following although I am not guaranteeing
this to be a complete solution for you, as I do not know all the specifics.

Dim db as Database
Dim qdf_Chart as QueryDef
Dim qdf_Unfiltered as QueryDef
Dim sql as String

Set db = Currentdb
Set qdf_Unfiltered = db.QueryDefs("qryChart_Unfiltered")
Set qdf_Chart = db.QueryDefs("qryChart")

sql = qdf_Unfiltered.SQL

'This is strFilter from your routine
sql = sql & " WHERE " & strFilter

qdf_Chart.SQL = sql

If you already have a WHERE clause in your unfiltered data, you will have to
adjust accordingly.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I really need help with this. I've posted a few questions, but not having
any luck. I have a report that is filtered by an input form that contains a
multi-select list box. Normally I would use a query to filter the data then
use that query as the report data source. Since I need users to be able to
select more than one choice in a list box, I can't use the query. My user
input form contains a button with the following code that works great to
filter the report.

Here's my problem: I have a chart that I need to put in the same report.
When I run the report, the chart is not filtered the same as the report.
How
can I use vba to filter my chart? Is it as simple as changing ' Apply the
filter and switch it on With Reports![LocationsReportbyStorage].Filter =
strFilter.FilterOn = True End With End Sub? I'm totally stuck on this and
will appreciate any help you can give. I'm very new to vba. Thank you.

Private Sub cmdApplyFilter_Click()
Dim VarItem As Variant
Dim strStore As String
Dim strLocation As String
Dim datBeginDate As Date
Dim datEndDate As Date
Dim strFilter As String
' Check for Location
If Len(Me.cmdLocation.Value & "") = 0 Then
MsgBox "You must pick a location"
Exit Sub
End If

'Check for Beginning and Ending date
If Len(Me.cmdBeginDate.Value & "") = 0 Then
MsgBox "You must type a Beginning date"
Exit Sub
End If

If Len(Me.cmdEndDate.Value & "") = 0 Then
MsgBox "You must type an Ending date"
Exit Sub
End If


' Open Report
If SysCmd(acSysCmdGetObjectState, acReport, "LocationsReportbyStorage")
<> acObjStateOpen Then
DoCmd.OpenReport "LocationsReportbyStorage", acViewPreview
End If

' Build criteria string from StoreRoom listbox
For Each VarItem In Me.cmdStoreRoom.ItemsSelected
strStore = strStore & ",'" & Me.cmdStoreRoom.ItemData(VarItem) _
& "'"
Next VarItem
If Len(strStore) = 0 Then
strStore = "Like '*'"
Else
strStore = Right(strStore, Len(strStore) - 1)
strStore = "IN(" & strStore & ")"
End If

' Build criteria string from Location option group
Select Case Me.cmdLocation.Value
Case 1
strLocation = "='1'"
Case 2
strLocation = "='2'"

End Select

'Build Beginning & Ending date parameter
If Not IsNull(Me.cmdBeginDate) Then
datBeginDate = Me.cmdBeginDate
End If
If Not IsNull(Me.cmdEndDate) Then
datEndDate = Me.cmdEndDate
End If

' Build filter string
strFilter = "[StorageRoom] " & strStore & " AND [Location] " &
strLocation & " AND [Date] Between #" & datBeginDate & "# and #" &
datEndDate
& "#"


' Apply the filter and switch it on
With Reports![LocationsReportbyStorage]
.Filter = strFilter
.FilterOn = True
End With
End Sub
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

Hide Fields usedin Report Filter 0
Filter report by beginning and ending dates 1
Chart Row Source 0
VBA Export to PDF 0
VBA Coding Help for Beginner 0
Working outlook via vba code with xlsx - error 0
VBA - exporting serial letters to single documents 0
Error 3085: Undefined function 'N' in expression. - Access 2010 1

Top