DoCmd.ApplyFilter. Main form and subform

[peladito]

I have a main form (search) that has an unbound combo box (name: OpenedDateFilter) on my main form that has the following list values. "Today";"This Week";"Last Week";"This Month";"Last Month".
My subform (Browse All Issues) has a date field (supplier_issuer_date) that I want to filter. I know it’s possible through the use of DoCmd.Applyfilter but I’m getting an error and I’m not that used to vb.

Here is what I wrote:



If Me.OpenedDateFilter = "Today" Then
DoCmd.ApplyFilter "", "(Year([supplier_issuer_date])=Year(Date()) And DatePart(""ww"",[Opened Date],0)=DatePart(""ww"",Date(),0)) = "
Me!Browse_All_Issues.SetFocus
End If

Any help would be appreciated!

 

[SiJP]

What was the error you received?

------------------------
Hit any User to continue

 

[peladito]

The error that I’m getting is “the action or method is invalid because the form or report isn’t bound to a table or a query”

 

[SiJP]

The sub form has no way of knowing you are asking it to filter the data. Your code is asking to filter data on the main form.

You should be able to do...:

Dim sCriteria as String
sCriteria = "(Year([supplier_issuer_date])=Year(Date()) And DatePart(""ww"",[Opened Date],0)=DatePart(""ww"",Date(),0)) = "
If Me.OpenedDateFilter = "Today" Then
     Me!Browse_All_Issues.Filter = sCriteria 
     Me!Browse_All_Issues.FilterOn = True
End If

HTH's

------------------------
Hit any User to continue

 

[peladito]

Hi, Thanks for the help.
After I put

Dim sCriteria As String
sCriteria = "(Year([supplier_issuer_date])=Year(Date()) And DatePart(""ww"",[supplier_issuer_date],0)=DatePart(""ww"",Date(),0)) = "

If Me.OpenedDateFilter = "Today" Then
Me!Browse_All_Issues.Filter = sCriteria
Me!Browse_All_Issues.FilterOn = True
End If



The error that I’m getting now is “Object doesn’t support this property or method”

 

[SiJP]

Sorry.. think I missed some syntax..

Dim sCriteria As String
sCriteria = "(Year([supplier_issuer_date])=Year(Date()) And DatePart(""ww"",[supplier_issuer_date],0)=DatePart(""ww"",Date(),0)) = "

If Me.OpenedDateFilter = "Today" Then
Me!Browse_All_Issues.Form.Filter = sCriteria
Me!Browse_All_Issues.Form.FilterOn = True
End If

------------------------
Hit any User to continue

 

[peladito]

Hi! You are going to kill me, after I put that code it gives me a new error "you cant assign a value to this object

 

[PHV]

and what about this ?
If Me.OpenedDateFilter = "Today" Then
Me!Browse_All_Issues.SetFocus
DoCmd.ApplyFilter "", "(Year([supplier_issuer_date])=Year(Date()) And DatePart(""ww"",[Opened Date],0)=DatePart(""ww"",Date(),0)) = "
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[peladito]

Sorry guys, the same error as before.
The error that I’m getting is “the action or method is invalid because the form or report isn’t bound to a table or a query”