Applying filter on subform not working

[Fonzie]

I have a subform who's record source is a separate table from the main form. When I attempt to apply a filter , selecting from the subform, it returns all records, no filter applied at all. If I apply a filter to the main form, it returns the proper results, including proper subform results. I've haven't used MS Access for a while, so it's probably something simple I'm missing. Any ideas?

 

[rubbernilly]

Heyyyyyyyyyyyyyyy.... Fonzie

(Sorry, couldn't resist)

If you are setting this through code, did you set the FilterOn property to True? Here is an article about that:

http://support.microsoft.com/default.aspx?scid=kb;en-us;148299

If that is not the problem, then a couple of questions:
What version of Access?
What is the Subform object (the table, a query, or a form)?
How are you applying a filter to the subform?

You should be able to right-click in the subform and choose "Filter By Selection," "Filter By Form", "Filter For...," or "Filter Excluding Selection." These work for me.

Depending on how you answer the questions and the structure of your form, there are a couple of things that could be going wrong.

 

[Fonzie]

I am using Access XP. I am trying to use the 'Filter by Form' button on the toolbar. No coding. I tried to right-click in the subform, but none of the selections (filter by form, filter for, filter by selection...) work either. The subform is a form based on a different table (Main form - Inventory table, subform - Software table). There is a relationship between the two tables based on computer name field in both tables.

 

[rubbernilly]

Is the SourceObject of the Subform the table, or a form based on the table?

 

[Fonzie]

A form based on the table.

 

[Fonzie]

I tried both, where the subform source object is the table, and where it is a form based on the table...same result.

 

[Fonzie]

I played around with this a little more and found the filter may be working, just not the way I want it to. The main form is an inventory of all computers in the office. The subform is a list of 20 or so software packages. For each computer, I can check off which software package is installed.

When I filter the subform now, it will limit the subform to show only what I filtered for, but I still lists all 600 or so items on the main form and I have to go through each one to find what I am looking for, which makes the filter pointless. I would like it to filter the main form so only computers with the selected software packages in the subform are shown. Tell me this is possible.

 

[rubbernilly]

And if you go to Datasheet view on the subform, do you not get the option to FilterByForm from a right-click?

Sorry, I have access to Access 97, Access 2000, and Access 2003... but I don't have Access XP installed anywhere to more properly troubleshoot this.

 

[Fonzie]

Yes, I have that option. If I filter for SoftwareA in the subform, it will filter the subform, but the main form still shows all 600 or so records, so I still need to go through each one. If there are only 5 computers with SoftwareA installed, I would like the main form to only show those 5 computers. Hope I am making sense.

 

[rubbernilly]

I typically have a single form view main form with either a continuous form or datasheet view subform... are you saying that you have this setup, too, but when you move from Computer1 to computer2 in the main form, your subform will go blank because you are filtering for SoftwareA... where you would rather have Computer2 dropped out of the recordset?

If so, I think I'm understanding your problem now.

It sounds like you have to Filter the Main form for where the record exists with conditions in the subform...

for instance if ComputerName exists in both recordsets as a field, filter on the main form for:

ComputerName = nz(DLookup("ComputerName","SubDataSetQuery","Software = 'SoftwareA'"),"")

Or something like that.

HTH

 

[Fonzie]

Yes, that is how I my DB is setup and that is just what is happening.

Does that mean it is not possible using the 'Filter by Form' option? (pardon my stupidity) I am picturing a situation where I may want to filter by all computers at a certain location (main form field) that have SoftwareA installed (subform field). Maybe I'm in over my head here.

 

[rubbernilly]

You are basically telling Access that you want to limit the sort of records in the subform... so the main form never gets that filter.

Did the DLookup option work?

 

[Fonzie]

I am currently trying the DLookup option, having some trouble getting it to work.

In the sample code you gave above, would this not be a static lookup? What if I changed the software package I was wanting to filter for? What if I want to search for several software packages? Will this DLookup option still do what I am trying to do?

 

[rubbernilly]

Hmm, I thought you might be able to use a Dlookup in the filter, but I tested it and you can't. But we're on the right track. To be the most dynamic, you're probably going to have to build your own "FilterBuilder" function.

For instance, you might have an unbound listbox on the form that lists your software types and allows MultiSelect. You would select the softwares you want to filter for, and then click your custom "Apply Filter" button. The code would grab a GROUPING recordset of Computers that had that software installed (1 return per computer), and use that recordset to build a Filter string that looks liked:

"ComputerName In ('Computer1','Computer3','Computer14')"

Alternately, if you don't want the users to have this functionality, you could hide the filter-building-function on a key-combination for the form.

Here is some sample code that works for me (with different fieldnames:

Dim rs As DAO.Recordset
Dim i As Integer, sFilter As String

With Me.lstSoftware
  For i = 0 To .ListCount - 1
    If .Selected(i) = True Then
      sFilter = sFilter & "'" & .ItemData(i) & "',"
      [green]'or for a numeric field
      'sFilter = sFilter & .ItemData(i) & ","[/green]
    End If
  Next i
End With
Set rs = CurrentDb.OpenRecordset("SELECT * FROM SubFormRecordSource WHERE Software In (" & sFilter & ")")

sFilter = ""
While Not rs.EOF
  sFilter = sFilter & "'" & rs.Fields("ComputerName") & "',"
  rs.MoveNext
Wend

sFilter = "ComputerName In (" & sFilter & ")"
Me.Form.Filter = sFilter
Me.Form.FilterOn = True
  
Set rs = Nothing

HTH

 

[Grounded]

This thread is almost exactly what i have been looking for but i don't seem to be able to get it to work.

i have the list box and can multi select it. I can get all the computers that have software A or i can get all that have A or B - using dynamic SQL statements. when i try to use an AND statement to select values from the parent form i get into trouble.

The example above did not fuction properly when i put it in and substituted my field values.

Is there anyway to build a select statement that will select all the computers that have software A and Software B on them?

If not can you help me understand what the example above is doing and how to get it to work.

this is the line i am currently hung up on.
sFilter = sFilter & "'" & rs.Fields("IDPeople") & "',"

Thanks!

iz