Link to home
Start Free TrialLog in
Avatar of Shawn
ShawnFlag for Canada

asked on

cannot clear filter in subform

I can set a filter from a main form to the subform fine with this:

Me.sbfrmClientSearchCompany.Form.Filter = "[CompanyName] LIKE '*" & Me.search0 & "*' "
Me.sbfrmClientSearchCompany.Form.FilterOn = True

Now on another button I am trying to clear the filter and this isn't clearing it:
Me.sbfrmClientSearchCompany.Form.FilterOn = False

how can I clear the subform?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try this first:
Me.sbfrmClientSearchCompany.Form.Filter = ""
Me.sbfrmClientSearchCompany.Form.FilterOn = False
If that doesn't work, you can resort to this, although I really don't like it that well:

Dim sRs As String
sRs = Me.sbfrmClientSearchCompany.Form.Recordsource
Me.sbfrmClientSearchCompany.Form.Recordsource = ""
Me.sbfrmClientSearchCompany.Form.Recordsource = sRs

mx
Avatar of Shawn

ASKER

your first one worked in a standalone button but it doesn't seem to work with my surrounding code...sorry, I should have posted all the code.

I get the error "can't go to the specified record"

See below:
Private Sub search0_Change()

Me.sbfrmClientSearchContact.Form.Filter = ""
Me.sbfrmClientSearchContact.Form.FilterOn = False

Me.sbfrmClientSearchCompany.Form.Filter = ""
Me.sbfrmClientSearchCompany.Form.FilterOn = False

DoCmd.GoToControl "sbfrmClientSearchCompany"
DoCmd.GoToControl "CompanyName"

If Nz([Forms]![frmClientSearch].search0, "") <> "" Then

Me.sbfrmClientSearchCompany.Form.Filter = "[CompanyName] LIKE '*" & Me.search0 & "*' OR [ClientID] LIKE '*" & Me.search0 & "*' OR [CompanyName] LIKE '*" & Me.search0 & "*'"
Me.sbfrmClientSearchCompany.Form.FilterOn = True

DoCmd.GoToControl "sbfrmClientSearchContact"
DoCmd.GoToControl "ClientID"

Me.sbfrmClientSearchContact.Form.Filter = "[LastName] LIKE '*" & Me.search0 & "*' OR [FirstName] LIKE '*" & Me.search0 & "*'OR [ClientID] LIKE '*" & Me.search0 & "*' "
Me.sbfrmClientSearchContact.Form.FilterOn = True

End If

    
DoCmd.GoToControl "search0"
SendKeys "{F2}"
End Sub

Open in new window

What line doe the error occur on?

Also, don't see how this would be related to the filter setting ...

mx
Avatar of Shawn

ASKER

it happens on this line:
DoCmd.GoToControl "CompanyName"

In the subform window when there are no records, it tries to go to this control and it is not present when the the filter is on. I was trying to shut the filter off before this so records would be present.
"In the subform window when there are no records, it tries to go to this control and it is not present "
Well, that would cause this error. You can't really go to a control if no records are present on a subform.

Can you rethink the logic ?

mx
Actually, let try this:

Private Sub search0_Change()

Me.sbfrmClientSearchContact.Form.Filter = ""
Me.sbfrmClientSearchContact.Form.FilterOn = False

Me.sbfrmClientSearchCompany.Form.Filter = ""
Me.sbfrmClientSearchCompany.Form.FilterOn = False

Me.sbfrmClientSearchCompany.SetFocus  ' focus to subform control first
Me.sbfrmClientSearchCompany.Form.CompanyName.SetFocus ' then actual control.



If Nz([Forms]![frmClientSearch].search0, "") <> "" Then

Me.sbfrmClientSearchCompany.Form.Filter = "[CompanyName] LIKE '*" & Me.search0 & "*' OR [ClientID] LIKE '*" & Me.search0 & "*' OR [CompanyName] LIKE '*" & Me.search0 & "*'"
Me.sbfrmClientSearchCompany.Form.FilterOn = True

DoCmd.GoToControl "sbfrmClientSearchContact"
DoCmd.GoToControl "ClientID"

Me.sbfrmClientSearchContact.Form.Filter = "[LastName] LIKE '*" & Me.search0 & "*' OR [FirstName] LIKE '*" & Me.search0 & "*'OR [ClientID] LIKE '*" & Me.search0 & "*' "
Me.sbfrmClientSearchContact.Form.FilterOn = True

End If

   
DoCmd.GoToControl "search0"
SendKeys "{F2}"
End Sub
Avatar of Shawn

ASKER

still getting the same error here:
Me.sbfrmClientSearchCompany.Form.CompanyName.SetFocus ' then actual control.

maybe a refresh or requery?
Avatar of Shawn

ASKER

here it is in action
Shawnaraxi-487767.flv
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Shawn

ASKER

that did the trick. thanks MX
Humm .... kind of odd.  That really should not be necessary, but I've had trouble doing it the other way also.

mx
Avatar of Shawn

ASKER

yeah, I 've used something similar before without any hitches. strange.