Filter a form by value of dynamic query (vba)

Question

First, sorry for my english, I'm french... I do my best I promise!

I have a subform containing details about Kits to mount (for production purposes). Several of these kits share parts with other and I made a query to identify that.

So we have a subform with my kits and their details:

Kit Number
----------------------------------------
Kit A
Kit B
Kit C

I have a query which identify kits sharing parts, like Kit A with Kit A and Kit B etc...

That query is filtered by the subform as criteria for Main_Kit : [Forms]![frm_Production01].[Form]![q10_KitsToProduced_GroupBy_subform].[Form]![Kit]

By cliking on a kit on my subform (datasheet view) I want to filter that subform with the related sharing parts kits. And be able to unfiltered and apply the filter on another kit.

For now i created an unbound textbox on my subform with a click event to filter my kit based on the query:

Private Sub Text30_Click()
    Dim strFilter As String
    strFilter = "[Kit] In('" & "[q27_KitsWithSharedParts_2]![Kit_Number]" & "')"
    Me.Filter = strFilter
    Me.FilterOn = True
End Sub

(The field Kit or Kit Number is not a number but text.)

So, my problem is thats not working, resulting on a empty subform.

Do you have any idea on how to do a such thing?

Thank you very much in advance.

Put a breakpoint on your code line " Me.Filter = strFilter" and examine the value in 'strFilter'. I don't believe that will produce the results you expect as you are not referencing the query results, but instead will reference the query name -- not the values you expect. — Wayne G. Dunn

June7 June7 · Accepted Answer · 2017-05-18T05:13:40

Don't put quotes around the field reference. Quotes indicate a literal string, whereas you want the value in the field or control. Is [Kit] a number field? If so, do not use the apostrophe delimiters either. Probably don't need the query name prefix.

strFilter = "[Kit] In('" & [q27_KitsWithSharedParts_2]![Kit_Number] & "')"

Filter a form by value of dynamic query (vba)

1 Answers