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

 

[dmckinney]

I agree with Russell - we need to slow things down. Breathe deeply

I tried the theory on Northwind database. (nwind.mdb).

I created a form based on categories and a sub form based on products (linked by categoryId)

I created a button on the parent form with the following code.

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click
Dim strFilter As String
strFilter = InputBox("Enter Filter")

    Me.Filter = "CategoryId in (SELECT CategoryId from Products where ProductName like '*" & strFilter & "*')"
    Me.FilterOn = True
    
    Me.Products.Form.Filter = "ProductName like '*" & strFilter & "*'"
    Me.Products.Form.FilterOn = True
    


Exit_cmdFilter_Click:
    Exit Sub

Err_cmdFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdFilter_Click
    
End Sub

(Products is the name of the subform.)

It works as per your request, and I can add records.

What I suggest you do is reproduce what I have done, and study carefully the code until you understand exactly what is going on.

Then (and only then) apply exactly the same principles to your database, and see what you come up with.

hth,

dave.

 

[ToddK]

It made me weep, Dave - it works!

Here is the code:
Option Compare Database
Option Explicit

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

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

Me.Filter = "Source IN (SELECT Source FROM Details WHERE Description Like '*" & strFilter & "*')"
Me.FilterOn = True

Me.Source_Detail_Query_subform.Form.Filter = "Description like '*" & strFilter & "*'"
Me.Source_Detail_Query_subform.Form.FilterOn = True


Exit_FilterWord_Click:
Exit Sub

Err_cmd_Click:
MsgBox Err.Description
Resume Exit_FilterWord_Click

End Sub

Now - just one little problem - I still get the odd message about adding new records to my "many" table (in the subform), even when the filter has been cleared.

It reads:
"The object doesn't contain the automation object Details.

You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

Check the component's documentation for information on the properties and methods it makes available for automation operations."

Anyone figure out what this gobbledegook means, and how can I fix it?

TK

 

[dmckinney]

Sorry Todd - haven't really any ideas on that one. Except that I don't think it's linked to what we're doing directly. I'm going to leave that one for the next guy! I'd recommend you open another thread.

(To see if it's connected you could take a copy of your form - remove the button and the code and see if you're still getting the error when adding.)