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]

Can you post your code? Then we can have us a little look-see.

 

[ToddK]

Thanks, Russell - I'll give an overview that is more complete.

Table 1, called Source Listing by Author - consists of 5 fields:
Source
Author
Title
Year
Reference

Table 2, called Details, joined in a 1 to many on the shared "Source" field, consists of 4 fields:
ID
Source
Code
Description

Form shows all fields from Table 1, with a subform that shows Source, Code and Description for Table 2. Query is correct, since right things are showing.

My goal - to be able to search in the field description for a word or series of words. It should not be a whole field match, but match anywhere in the field. What I would like to happen is that the filter is applied in the Description field of the Details table, and this "flows through" the link back to the "1" part of the 1 to many relationship, so that only Records in Source Listing by Author that have (a) match(es) in the many table, Details, are now displayed, and only the mathces in the Details table are shown in the subform.

I have inserted a button on the form, as was suggested, which I have called FilterWord1.

I attached the following code:
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 = "Source IN (SELECT [Source Listing by Author].Source FROM [Source Listing by Author]" & _
" INNER JOIN [Details]ON [Source Listing by Author].Source = [Details].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

Where am I going wrong - at present, the filter applies, only 1 record in Table 1 (Source Listing by Author) is selected, not all of the fields are populated (only the Source field is), and nothing shows up in the subform. I can assure you that the word I filtered on is throughout the Details database exactly as I spelled it - there should have been multiple multiple hits!

Thanks for consideration.

 

[dmckinney]

Try the following SQL....it should give the same result as yours but is a little easier to read.

strSQL="Source IN (SELECT Source FROM Details WHERE Description Like '*" & strFilterOn & "*')"

also try...to see if it gives the same results

strSQL="Source IN (SELECT Source FROM Details WHERE instr(Description," & strFilterOn & ")<>0)"

Where have you put your button? Should be on the main form. May be worth requerying the main form and the subform.

dave

 

[Russell Hauf]

I agree completely with Dave. The first thing I wondered was where you had your button/code. What he wrote should work for you. Let us know if not.

 

[ToddK]

We are getting there...with the first version proposed, it will now do the filtering to relevant records in the 1 side of the relation, but does not pull along the many side to fill in the fields in the subform...so I get a subset of filtered records, but not the details from the "Details" table...interesting. Is it possible that something is lost during the querying, so that it cannot find the data to fill up the tabular subform with data?

 

[Russell Hauf]

On 2003-01-10 13:22, ToddK wrote:
We are getting there...with the first version proposed, it will now do the filtering to relevant records in the 1 side of the relation, but does not pull along the many side to fill in the fields in the subform...so I get a subset of filtered records, but not the details from the "Details" table...interesting. Is it possible that something is lost during the querying, so that it cannot find the data to fill up the tabular subform with data?

Do you have your subform linked to your main form? Is your button on the main form?
Try the last version that Dave posted.

 

[ToddK]

Same issue as other approach - it seems to select the records in the 1-side of the relation, but will not list the many in the subform. Additionally, though, it pops up a second dialog box asking me for parameter...instead of one, i get 2 dialog boxes.

The button is on the main form.

Thanks

 

[ToddK]

Aha! I recreated the query and mapped it to the form/subform and voila! I am getting almost correct behaviour - that is, it shows me filtered records from Source (the 1-side of the relation), but it does not filter the records in the many side. Is this a limitation, or is there some way that I can show only the matches in the subform (with the many database, called Details) from my query in only the records in the main form (the 1-database, called Source), so that I don't have to scroll through the entries in the subform to find my matches?

 

[ToddK]

Oops, now I am confused...

Let me go back...

when I laid out my main form, i mapped fields from Source table to the form. Then, somehow, I mapped fields from Details in my subform. With this, I had functionality to enter data into both tables; I did not use a query at that point.

Now, when I just completed my changes, I used a linking query to place fields from the many side of the relationship into the subform. It worked well enough for the filtering that you swell folks have been helping me with (see above for concern), but now, when I try to enter a new record, I have a problem with the subform (an error message appears talking about how the Object doesn't contain an automation Object Details, and that I need to do something in VBA).

I am admitting, here, that I am an amateur, and am very confused...in mapping out my fields on the main form, it won't let me link to the query, but the subform will...however, the subform doesn't want to let me enter data. Obviously, I am missing some fundamentals of Access here...I hope you will bear with my ignorance and let me know what my fields in form and subform should be mapped to, and how I can keep the functionality of entering new records?

ToddK

 

[Russell Hauf]

Todd, you're out of control!

Ok, first of all, I now understand what you want as far as filtering. It will take a little more code (I'll get back to you...or maybe Dave or someone else will step in first).

Secondly, about adding a new record, I suggest that you take the filter off before adding a new record. That should take care of your problem (similar to the following thread for details on how to do that via a button http://mrexcel.com/board/viewtopic.php?topic=34278&forum=14) - but the code would be Something like this:<pre><font color='#000000'>

Private<font color='#000080'>Sub</font> cmdNew_Click()
On Error<font color='#000080'>GoTo</font> Err_cmdNew_Click

Me.Filter = ""
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
Me.AllowAdditions = False

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

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End<font color='#000080'>Sub</font><hr align=left width=500><font color='#000080'></font></pre>

-rh
This message was edited by Russell Hauf on 2003-01-10 15:31