Subform text search (2K)

Topic

New Reply

I want to be able to filter a main form based on a text search of subform records. I have a fairly simple database for keeping track of the contents of offsite file storage boxes for a law firm, with two linked tables: the master table contains details about the box, and the child table contains a list of box contents, much like an invoice form. Pretty simple stuff. The database might ultimately track 10,000 or so boxes in storage.

The key form to this application is a standard form/subform. What I need is to be able to filter based on text in the box contents: Which boxes contain at least one reference to “Charlotte”? I’d like to be able to use standard “right-click” filtering commands – filter on/excluding selection and avoid using a separate search form.

The rub is obvious — when you set a filter on the subform, it applies to the subform, not the main form. In pseudo-code, what I think needs to happen is to intercept the OnApplyFilter event (which I can do), and cancel it to allow all of the line item detail to display. Then pass the filter condition to the main form, using an inner join to limit the display of box records.

It seems to me this should be a frequent problem (searching invoice detail, order detail, etc.), but I didn’t see any mention of it when I searched. It’s a shared application, so that seems to rule out a Flag field. Filtering doesn’t seem appropriate, and changing the recordsource doesn’t seem to permit a join (it gets stripped out).

I’m stumped. Anyone got a solution? I suspect it’s something obvious that I just don’t see.

Reply | Quote

Replies

One problem I see right away is that the rows in the subform are already “filtered” – they represent only the contents of the current box on the main form. Not sure how you’re going to do this without using a separate form.

Reply | Quote

Thanks for taking the time to reply. Yes, it’s more complex than it seems. I’ve been able to make a little progress. So far, I’ve been able to get most of it worked out by setting the recordsource property in the Form_Load event (I guess I should mention that the linking key field, RC, is a standard integer, not an auto-number field):

Me.RecordSource = “SELECT DISTINCT AGITEMS.* FROM AGITEMS INNER JOIN AGMEMO ON AGITEMS.RC = AGMEMO.RC; ”

Later on, to find boxes that mention “Charlotte”, recordsource can be stuffed with:

Me.RecordSource = “SELECT DISTINCT AGITEMS.* FROM AGITEMS INNER JOIN AGMEMO ON AGITEMS.RC = AGMEMO.RC ” & _
“WHERE (((AGMEMO.MMO) Like ‘*Charlotte*’));”

Problem at this point is that new records can’t be added, since there are no qualifying detail records.

Reply | Quote

Your parent and child forms are linked together one way, parent to child. You can’t filter the parent form based on the contents of the subform because the subform can only contain the records the parent allows. You can set the recordsource for the parent form based on a value contained in one of the subform records, but you will get an entirely new recordset in both parent form and subform.

This has been discussed many times in this forum and the answers are always the same. Some alternatives have been proposed, and you should be able to find them using the Lounge Search feature.

Reply | Quote

Actually, I had done a search as far back as early May, but didn’t see anything on point. It’s possible I missed all those posts you mention, since you’ve made the same point before on this issue. I’ll take another look and go back further.

I do see the somewhat incestuous relationship between the recordsource in the main form and the subform and suspect that this is the underlying problem.

From where I sit, this is one of those Access issues that makes perfect sense to a user, who doesn’t really care that two tables are involved, but it doesn’t have an easy answer from an implementation standpoint that I can see. The only thing I can think of is to pull the line item detail into a memo field on the main table, “unpacking” the lines for record editing purposes, and “repacking” it again when leaving the record if it’s dirty. That would solve a number of problems, including making it possible to do more complex searches, such as multiple terms, such as “Which boxes refer to both ‘McDonald’ and ‘coffee'”?

Reply | Quote

Maybe the attached zipped Access 97 database will help you get started. You’ll have to unzip it and convert it to Access 2000.

It contains two forms.

The first form, frmBoxes, has an attached tool bar with one button that filters the main form to those boxes whose contents contain the text selected in the subform (so if you select “ring”, you’ll find boxes whose contents mention “ruby ring” but also boxes whose contents mention “earring”).

The second form, frmBoxes2, has two text boxes in which you can enter a keyword. There are command buttons for applying an “AND” filter or an “OR” filter.

HTH,
Hans

Reply | Quote

YES!! This was spot on. I have to admit I haven’t used an IN clause in a SQL statement since my Foxpro days, and I had forgotten all about it. Performance is fine, and there were no conversion issues between 97 and 2K.

I’m grateful for your help, Hans. Thanks.

FWIW, the example Hans sent along is recommended reading for other Loungers. As Charlotte pointed out, this is apparently a frequent post, and it’s one I’ve run into several times without ever finding a workable solution. Hans nailed it, and his example is clear and to the point.

Reply | Quote