CODEWITHSUNDEEP
ms-accessvba
David
David

Reputation: 115

trying to filter subform based on contents of field on main form

I've looked at a bunch of other posts, an nothing seems to get it. This damned Access nomenclature always throws me.

I've got a main form, with a subform. I want to be able to enter a string into a field on the main form, and have the subform filtered as follows:

[Title on Eligibility List] like ""*" & frmNewGeneralclassification_fill_in.!txtsearchstring & "*""

This is not a "link master and child" situation. It's doing a LIKE match.

I've tried

me!frmNewGeneralclassification_fill_in.form.filter = "[Title on Eligibility List] like '*" & me!txtSearchString & "*'"

and

me.frmNewGeneralclassification_fill_in.form.filter = "[Title on Eligibility List] like '*" & me!txtSearchString & "*'"

(Filter is not an Autosense option, but if I type it in anyway, it capitalizes it.)

and I get "object required" error message.

One of those SHOULD work, but they're not.

and

frmJob_Title_Lookup.Form.RecordSource = "SELECT [Job Title to New Classification].[Title on Eligibility list], [Job Title to New Classification].Employer, [Job Title to New Classification].[New Classification in EE], [Job Title to New Classification].[New General Classification] FROM [Job Title to New Classification] WHERE ((([Job Title to New Classification].[New Classification in EE]) Is Not Null) AND (([Job Title to New Classification].[New General Classification]) Is Not Null)) and ([Title on Elibibility List] like '*" & frmNewGeneralclassification_fill_in.txtSearchString & "*'"
    frmJob_Title_Lookup.Form.Requery

and I get "Object Required" error message.

Any pointers?

Upvotes: 0

Views: 1147

Answers (1)

DevRoz
DevRoz

Reputation: 42

I agree that referencing subforms is really confusing in Access. In this case, try not using 'me' and just fully reference what you need (and remember to turn on your filter):

Screenshot of my form with subform

Private Sub txtSearch_AfterUpdate()
Dim strWhere As String
strWhere = ""


If Not IsNull(Me.txtSearch) Then
    strWhere = strWhere & " ([ItemCode] like '*" & Me.txtSearch & "*' OR "
    strWhere = strWhere & " [ItemDescription] like '*" & Me.txtSearch & "*') AND "
End If


'remove final AND
If strWhere <> "" Then
    strWhere = Left(strWhere, Len(strWhere) - 5)


    Forms!frmItemList.frmItemListItemsSF.Form.Filter = strWhere
    Forms!frmItemList.frmItemListItemsSF.Form.FilterOn = True
Else
    strWhere = "1=1" 'this is always true and forces the filter to clear
    Forms!frmItemList.frmItemListItemsSF.Form.Filter = strWhere
    Forms!frmItemList.frmItemListItemsSF.Form.FilterOn = True
End If

End Sub

Upvotes: 1

Related Questions