Filtering records based on entries in subform

[ToddK]

I have a form which displays data from 2 different tables, which are arranged in a 1 to many relationship.

I would like to be able to apply a filter to find specific words in the subform...and have it cascade the "hits" to the main table, so that I see only the entries in the main table that have corresponding hits in the many table (on the subform).

I would like to do this neatly - it would be very nice to be able to add a button to my form which, when clicked, asks for the find/search parameter, conducts the filtering, and gives me the answer, all in the context of the original form.

Can someone share tips with me on how to proceed? I suspect filter by form might be useful, and I even get a view of the subform, but when I type in a word and try to filter, I get nowhere...

Likewise, when I use the filter button from the button bar, it cannot do anything with the subform, and I get a null set at the end of the filter...

Todd K

 

[Russell Hauf]

Not sure what you mean by "Words in the subform", but I'm going to assume that you mean "words in one field of the subform". For this example, I have 2 tables, the "ONE" table: tbl010603a; and the "MANY" table, tbl010603b. The ONE table contains the primary key "ID". The MANY table contains ID and another field called "Letter" (that is text).

Ok, create a button on the form (mine is called cmdFilter).

Put code behind the form - something like this (I used the Wizard for the crude error handling):<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Compare</font> Database<font color='#000080'>Option</font><font color='#000080'>Explicit</font><hr align=left width=500><font color='#000080'>Private</font><font color='#000080'>Sub</font> cmdFilter_Click()<font color='#000080'>On</font><font color='#000080'>Error</font><font color='#000080'>GoTo</font> Err_cmd_Click<font color='#000080'>Dim</font> strSQL<font color='#000080'>As</font><font color='#000080'>String</font><font color='#000080'>Dim</font> strFilterOn<font color='#000080'>As</font><font color='#000080'>String</font>

strFilterOn = InputBox("Please enter the value that you would like to filter on")

strSQL = "ID in (SELECT tbl010603a.ID FROM tbl010603a " & _
"INNER JOIN tbl010603b ON tbl010603a.ID = tbl010603b.ID " & _
"WHERE tbl010603b.Letter Like ('*" & strFilterOn & "*'))"

DoCmd.ApplyFilter WhereCondition:=strSQL

Exit_cmd_Click:<font color='#000080'>Exit</font><font color='#000080'>Sub</font>

Err_cmd_Click:
MsgBox Err.Description
Resume Exit_cmd_Click<font color='#000080'>End</font><font color='#000080'>Sub</font></font></pre>

Creating the SQL is easy - just create a query in your database using the 2 tables (make sure to join them). Use only the primary key field from the ONE table, and only the field you want to filter on in the MANY table. Uncheck the SHOW box for the field from the MANY table, and for the criteria for that field, put

Like "*xxx*"

Then go to SQL view, and copy the SQL into your code. Take out the xxx part and substitute the value (actually the variable) the user enters into the inputbox in your code.

That's a quick and easy way to do what (I think it is that) you want. I hope it helps.

-Russell
This message was edited by Russell Hauf on 2003-01-06 18:24

 

[ToddK]

Hi, Russell:

Thanks for the detailed response...I have tried, with my limited knowledge, to implement it, but am not sure I understand some of the nuances of VBA programming...so if you don't mind, trouble-shoot for me???

My 2 tables are called Source Listing by Author (this is the 1 part of 1 to many) and Details (this is the many table). I have the fields of Source Listing by Author arrayed in a form, and an embedded subform displays the matches (the many) from the Details table. All of these are on the same screen, and what I was hoping to do was filter on a single word (the field could be a multiple word entry) in a field called Description in the Details table (the many side of the relationship) in situ, so that nothing appears to change on the form in front of the user, but records are matched to the filter. This would have to cascade from the many form (where the matches are made) so that only the records in the "1" side of the match now show. For example, in the 1 side, there are records with ID numbers from 1 to 118 - once the filter is applied, only the hits should show in the form, so I would expect ragtag sequences, on ID, of 1, 7, 15, 24, or the like.

Not being very knowledgeable about VBA, but trying to follow your instructions, I went into Modules window, and in Module 1, created the following code (note that the Command button I laid on the form was called FilterWord):

Option Compare Database
Option Explicit

Private Sub FilterWord_Click()
On Error GoTo Err_cmd_Click
Dim strSQL As String
Dim strFilterOn As String

strFilterOn = InputBox("Please enter the value that you would like to filter on")

strSQL = "SELECT [Source Listing by Author].Source" & _
"FROM Details INNER JOIN [Source Listing by Author] ON Details.Source = [Source Listing by Author].Source " & _
"WHERE (((Details.Description) Like '*" & strFilterOn & "*'))"

DoCmd.ApplyFilter WhereCondition:=strSQL

Exit_cmd_Click:
Exit Sub

Err_cmd_Click:
MsgBox Err.Description
Resume Exit_cmd_Click

End Sub

I created the SQL code for the string as you suggested, and the above is what came with it. I put quotation marks around each line's worth of text as suggested.

When I clicked on the button, nothing happened - no Input box to query me, nothing.

Have I missed something?

Thanks for your coaching here, Russell! It is much appreciated.

Todd K

 

[dmckinney]

Your code should be attached to the form, and not in module1.

Go into the design view of the form and look at the properties of the button you created. In the onclick property click the ellipsis (...) and chose event procedure and add your code here.

hope this is clear.

 

[Russell Hauf]

Also, your SQL will not work as you have it. You need to include the spaces in-between words. So this:

strSQL = "SELECT [Source Listing by Author].Source" & _
"FROM Details INNER JOIN [Source Listing by Author] ON Details.Source = [Source Listing by Author].Source " & _
"WHERE (((Details.Description) Like '*" & strFilterOn & "*'))"


Should change to:

strSQL = "SELECT [Source Listing by Author].Source " & _
"FROM Details INNER JOIN [Source Listing by Author] ON Details.Source = [Source Listing by Author].Source " & _
"WHERE (((Details.Description) Like '*" & strFilterOn & "*'))"

 

[dmckinney]

...and another thing...

the wherecondition shouldnt be a full sql statement - it should just be the where condition. (Like Russell had in his original response.)

 

[ToddK]

Thanks to both of you gents - I still get a bit of a problem, I suspect it relates to the message posted last by dmckinney....

When I do as suggested, I still get a SQL statement error. It was not immediately obvious what the last two suggestions on the board meant - I could distinguish no difference between my SQL statement (in my last post) and the one Russell posted (I need my glasses checked, tho...) and I was not sure how I deviated from Russell's wherecondition in my version versus his...so if you can spell it out for me, I will be most, most grateful.

ToddK

 

[Russell Hauf]

Russell's:

<pre>strSQL = "ID in (SELECT tbl010603a.ID FROM tbl010603a " & _
"INNER JOIN tbl010603b ON tbl010603a.ID = tbl010603b.ID " & _
"WHERE tbl010603b.Letter Like ('*" & strFilterOn & "*'))"</pre>

The key thing is that I have ID in (Select...

Try that (substituting your field name, of course).

Hope this time's the charm!

 

[ToddK]

OK - now I am not generating syntax error, but the filtering behaviour is unusual - it selects only one record from the one database (Source Listing by Author - the one part of the 1-to-many), and displays nothing in the subform which is connected to the many database (Details - field name, Description), on which the search was to be conducted...it's getting weird in here! Is there more I need to include in the SQL to pull both databases together in the query?

Any thoughts?

 

[ToddK]

Anyone have any further thoughts about why I am experiencing the odd filtering behaviour when I apply the filter described above?