VBA Filtering a chart in a form

[Neilster]

Hi Guys

I have a button that opens a small form where the user can choose the dates between, then they click a button on that from which opens a form with a chart/graph relating to TblCustomerDetails.

Everything works fine but it doesn't filter or change the chart. This is the code I'm using can anyone tell me where I'm going wrong.

Many thanks.

Dim strForm As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strForm = "Sales Form"
strDateField = "[DateSold]"
lngView = acNormal


If IsDate(Me.txtFROM) Then

strWhere = "(" & strDateField & " >= " & Format(Me.txtFROM, strcJetDate) & ")"

End If

If IsDate(Me.txtTO) Then

If strWhere <> vbNullString Then
strWhere = strWhere & " AND "

End If

strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtTO + 1, strcJetDate) & ")"

End If

Debug.Print strForm
DoCmd.OpenForm strForm, lngView, , strWhere
DoCmd.Close acForm, "FrmDateFilter", acSaveNo

:banghead::banghead::banghead:

 

[arnelgp]

Dim strForm As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "mm\/dd\/yyyy"

strForm = "Sales Form"
strDateField = "[DateSold]"
lngView = acNormal


If IsDate(Me.txtFROM) Then

strWhere = "(" & strDateField & " >= #" & Format(Me.txtFROM, strcJetDate) & "#)"

End If

If IsDate(Me.txtTO) Then

If strWhere <> vbNullString Then
strWhere = strWhere & " AND "

End If

strWhere = strWhere & "(" & strDateField & " < #" & Format(Me.txtTO + 1, strcJetDate) & "#)"

End If

Debug.Print strForm
DoCmd.OpenForm strForm, lngView, , strWhere
DoCmd.Close acForm, "FrmDateFilter", acSaveNo

 

[Neilster]

Thanks for the reply, however nothing is happening to the chart.

 

[arnelgp]

do you have Link Master Fields, Link Child Fields on your graph?
what is the row source of the graph?

these things you must consider.

 

[Neilster]

The FrmDateFilter is set to unbound and the the SalesChart Form Record Source is set to TblCustomerDetails

 

[Neilster]

Row Source is

SELECT [EmployeeName],Sum([ProductCost]) AS [SumOfProductCost] FROM [QrySales] GROUP BY [EmployeeName];

 

[arnelgp]

on your code that opens the chart form, place the where as an OpenArg parameter, ie:

Debug.Print strForm
DoCmd.OpenForm strForm, lngView, , , , strWhere
DoCmd.Close acForm, "FrmDateFilter", acSaveNo

now on Open event of your "chart form":

Private Sub Form_Open(Cancel As Integer)
Dim strCriteria As String
strCriteria = Me.OpenArgs
If strCriteria <> "" Then
Me.RowSource = Replace(Me.RowSource,";","") & " Where " & strCriteria
End If

 

[Neilster]

The first part I changed to this as it only works with 5 ,,,,,

Debug.Print strForm
DoCmd.OpenForm strForm, lngView, , , , , strWhere
DoCmd.Close acForm, "FrmDateFilter", acSaveNo

Then I get a compile error - Method or data member not found in the 2nd part of the code on the from open event and highlights the Me.Rowsource

Me.RowSource = Replace(Me.RowSource, ";", "") & " Where " & strCriteria

 

[arnelgp]

sorry for that its: your graph object rowsource, ie :

With Me.theNameOfYourGraphControl
.RowSource = Replace(.RowSource, ";", "") & " Where " & strCriteria
End With

dont forget to change "theNameOfYourGraphControl" to the correct name of your graph control.

 

[Neilster]

run time error '13:'

Type mismatch

and highlights the code below.

DoCmd.OpenForm strForm, lngView, , , , strWhere

 

[arnelgp]

ok, let's make it simple:

docmd.OpenForm formname:=strForm, view:=lngView, openargs:=strWhere

 

[Neilster]

Its saying there is a syntax error (missing operator) in query expresion?


SELECT [EmployeeName],Sum([CRMCost]) AS [SumOfCRMCost] FROM [QrySales] GROUP BY [EmployeeName];

 

[arnelgp]

what is the sql of QrySales.

 

[Neilster]

SELECT TblCustomerDetails.ProductCost, TblCustomerDetails.EmployeeName, TblCustomerDetails.DateSold
FROM TblCustomerDetails;

 

[arnelgp]

go back again to your first post and change:

Dim strForm As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "mm\/dd\/yyyy"

strForm = "Sales Form"
strDateField = "[DateSold]"
lngView = acNormal


If IsDate(Me.txtFROM) Then

strWhere = "([QrySales]." & strDateField & " >= #" & Format(Me.txtFROM, strcJetDate) & "#)"

End If

If IsDate(Me.txtTO) Then

If strWhere <> vbNullString Then
strWhere = strWhere & " AND "

End If

strWhere = strWhere & "([QrySales]." & strDateField & " < #" & Format(Me.txtTO + 1, strcJetDate) & "#)"

End If

Debug.Print strForm
DoCmd.OpenForm strForm, lngView, , strWhere
DoCmd.Close acForm, "FrmDateFilter", acSaveNo

'------------------------

i noticed that your have SUM(CRMCost), on your rowsource. CRMCost is not included in your qrySales!

 

[Neilster]

No different.

Is there another way say have 2 textboxes DateFrom & DateTo on the actual form with with a click button that can filter the graph?

 

[arnelgp]

on your graph rowsource, you have SUM(CRMCost) on your query, while on original (qrySales) qry CRMCost is not in query.

 

[Neilster]

I have no idea what to do. If I have a textbox (DateSold) in my FrmCustomerDetails surely the code should filter between 2 dates but it just show resualts for the entire DB in the graph.

 

[arnelgp]

is it possible for you do upload your db?

 

[Neilster]

Work won't allow it unfortunatley. Thanks for all your help though.