Subform Search Function in Access

[QuetzalVB]

I have a form in Access which has a subform nested within it. My users have requested that they be able to search for specific values within the subform, which I know is not a built-in function in Access.

I've created a short VB code attached to a button that is supposed to this by:
1. Prompting the user for the subform value they want to search
2. Running a query with that value
3. Returning the ID number (key) that is in the main form
4. Applying a filter with this ID number

The steps work, except that as Access tries to apply the filter, it throws an error: "The Apply Filter Action was cancelled." The code is below. Anyone have success with this, or have a simpler approach?

Private Sub cmdGp15Query_Click()
On Error GoTo Err_cmdGp15Query_Click

    Dim stDocName As String
    Dim FilterCrit As Integer
    
    DoCmd.ShowAllRecords
    stDocName = "qryFindGp15Unit"
    DoCmd.SetWarnings no
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.SetWarnings yes
    DoCmd.ApplyFilter , "[ORDER #]=""[tblTempSearchString].[ORDER #]"""

Exit_cmdGp15Query_Click:
    Exit Sub

Err_cmdGp15Query_Click:
    MsgBox Err.Description
    Resume Exit_cmdGp15Query_Click
    
End Sub

I've tried removing the double quotes (Access doesn't like that) and passing the table value through a variable (also with no success).

[wes4dbt]

Never used that command but the filter string doesn't look right.
try this

DoCmd.ApplyFilter , "[ORDER #]='" & [tblTempSearchString].[ORDER #] & "'"

If [ORDER #] is a numeric field then try this

DoCmd.ApplyFilter , "[ORDER #]=" & str([tblTempSearchString].[ORDER #])

[QuetzalVB]

Thanks, wes4dbt. Unfortunately, with both of those strings, I get the error "Microsoft Access can't find the field '|' referred to in your expression."

I know that the query works and generates the correct value for [ORDER #] (which is an integer). But it does not want to filter on the result in the table. I've tried replacing the [tblTempSearchString].[ORDER #] with a value and that works.

If there's another easy way to search for a subform value, I'm willing to try it--but what I've seen online seems more complicated than just filtering.