Filter a form by dynamic query values (vba) (1 Viewer)

[l. Paskal]

Hi,

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 A, etc..., etc...
Kit B
Kit C
etc...

I have a query which identify kits sharing parts:
Main_Kit: Kit A ---- Sharing Kit: Kit A obviously!
Kit A ---- Kit B
Kit A ---- Kit D
Kit C ---- Kit C
Kit C ---- Kit E

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.

 

[Cronk]

It will not work because you are setting the filter to be that literal string
ie
Kit in ('[q27_KitsWithSharedParts_2]![Kit_Number]'

not the contents of the query. To do this, you would have to open a recordset based on the query, loop through the recordset and concatenate the values separated by commas.

It would be easier to change the recordsource of the subform, something along the lines of
Me.recordsource ="select * from SubformQuery inner join q27_KitsWithSharedParts_2 on SubformQuery.kitID = q27_KitsWithSharedParts_2.KitID where q27_KitsWithSharedParts_2.Kit_Number='" & me.Kit_Number & "'"

 

[Orthodox Dave]

Yes, your code has a couple of small errors (c'est la vie!)

In your code:

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 line

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

has quotation marks around the variables.

Removing the quotation marks we have:

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

(The single quotes take care of the fact it is a string not a number)

But there is still a problem. The word "In" is not VBA! Assuming you mean that [Kit] CONTAINS [Kit_Number], the VBA should read:

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

Finally, after Me.FilterOn = True, please add the line

Me.Requery

I would try re-running with just those changes before committing to major restructuring.

 

[Orthodox Dave]

Quick postscript - you can release the filter by clicking on the "Filtered" button at the bottom of the subform - very useful as you can toggle it on and off without changing the filter.

 

[l. Paskal]

Thank you everybody.

I just tested the first solution by Cronk and it works perfectly well with small modifications:

Private Sub FilterCheckBox_Click()
If FilterCheckBox.Value = True Then
Me.RecordSource = "select * from q29_General_Overview inner join q27_KitsWithSharedParts_2 on q29_General_Overview.Kit = q27_KitsWithSharedParts_2.Kit_Number"
Else
Me.RecordSource = "select * from q29_General_Overview"
End If
End Sub

One again, thank you and have a nice day

 

[jdraw]

I. Paskal,

Votre anglais est plus que suffisant pour la communication. Si seulement tous les messages étaient très clairs.