Filter a subform back to original filter.

[Rob_Jones101]

Hi.

I have a subform that is filtered on load. It shows purchase orders related to a certain quote. I have two text boxes to filter the subform based on a date range.

How do I filter the subform back to the original filter?

I have tried

me.subform.filter = ""
me.suform.form.filteron = false

All this does is remove all filters. I just want it to go back to the original filter.

This just returns

Thanks

 

[arnelgp]

instead of removing the filter, just call the Load event of the form, if the Load event has the original filter.

 

[CJ_London]

assign the filter to a variable before making any changes

static originalFilter as string
if originalFilter="" then originalFilter=me.filter

me.filter=however you are constructing the filter
me.filteron=true

 

[Rob_Jones101]

It is a Master and Child link on the Quotenumber. When you open the form it is all ready filtered from the previous form. Then I filter it based on the dates in the text boxes and use the search button to search. This filters the subform. I want to turn this filter off.

 

[CJ_London]

master child links will remain - unless you have removed them. So the question is how are you filtering at the moment? The assumption is you are using a button (your search button?) to create the filter string. If that button is m=on the may form then modify my code to reference the subform filter

 

[Rob_Jones101]

This is my code for the search button.

Private Sub SearchBtn_Click()

Dim searchfor As String
Dim searchdate As String
Dim startDate As String
Dim endDate As String

If IsNull(SDTxt) = False Then
startDate = Me.SDTxt.Value
endDate = Me.EDTxt.Value
searchdate = "SELECT * FROM Forms!Frmeditquote.SFrmeditquote WHERE Forms!Frmeditquote.SFrmeditquote!OrderDate BETWEEN #" & Format$(startDate, "dd/mm/yyyy") & "# AND #" & Format$(endDate, "dd/mm/yyyy") & "#;"
End If

Me.[SFrmeditquote].Form.Filter = searchfor
Me.[SFrmeditquote].Form.FilterOn = True
Me.SFrmeditquote.Form.Requery

End Sub

 

[CJ_London]

how does that work? or doesn't it?

you create a sql string for searchdate, but apply searchfor to your filter which as far as I can see has not had a value assigned

Also you sql needs to reference a table or query, not a form. Plus in sql you need to use the US format of mm/dd/yyyy (2nd Jan in your format will be interpreted as 1st Feb)

 

[Rob_Jones101]

This code works fine. It filters the subform. What I am trying to do is turn this filter off.

 

[CJ_London]

so have you tried the code in post #3? If so, what was the outcome?

you need to put before your If IsNull(SDTxt) line

 

[Rob_Jones101]

The code in post 3 works. It filters the subform to show the correct information based on the dates. What i need to do is turn that filter off and show all the records that are associated with the quotenumber.

 

[CJ_London]

is the quote number the child/master link between the main form and the subform? If so just setting the subform filter to "" will do the job - or turn off the filter (filteron=false)

 

[arnelgp]

With Me.[SFrmeditquote]
    .Form.Filter = ""
    .Form.Filteron = false
    .LinkMasterFields = "QuoteNumber"           ' the quote number fieldname of parent form
    .LinkChildFields = "QuoteNumber"                ' the quote number fieldname of the subform
End With

 

[Rob_Jones101]

Thanks arnelgp. Does this go on the click event or another event.

 

[arnelgp]

add another button (say, "Clear Filter", btnClearFilter) on the main form.
and on the Click Event of that button add the code.

 

[Rob_Jones101]

Hi Arnel. This is my code to filter the subform.

Dim searchfor As String
Dim searchdate As String
Dim startDate As String
Dim endDate As String

If IsNull(SDTxt) = False Then
startDate = Me.SDTxt.Value
endDate = Me.EDTxt.Value
searchdate = "SELECT * FROM Forms!Frmeditquote.SFrmeditquote WHERE Forms!Frmeditquote.SFrmeditquote!OrderDate BETWEEN #" & Format$(startDate, "dd/mm/yyyy") & "# AND #" & Format$(endDate, "dd/mm/yyyy") & "#;"
End If

Me.[SFrmeditquote].Form.Filter = searchfor
Me.[SFrmeditquote].Form.FilterOn = True
Me.SFrmeditquote.Form.Requery

I have tried your code but it dosent filter it.

Private Sub Command162_Click()

With Me.[SFrmeditquote]
.Form.Filter = ""
.Form.FilterOn = False
.LinkMasterFields = "QuoteNumber"
.LinkChildFields = "QuoteNumber"
End With

End Sub

I have put it in like you said but it dosent do anything.

Do you have any other ideas.

 

[arnelgp]

what is the Textbox name of quotation number on main form?
what is the Textbox name of quotation number on subform?
put them on .LinkMasterFields and .LinkChildFields on the code respectively.

 

[Rob_Jones101]

I have tried that. The master is quotenumber and the child is fkquotenumber. This also does nothing.