Filter main form based on subform data

[Varda]

Re: https://www.accessforums.net/forms/f...orm-49326.html



I have a similar question to the original poster.

The response given on this thread is not very clear to me.

[June7]

What about the suggested options do you feel need clarification? I will try to do so.

[Varda]

I have a Main Form and a Subform, and I need to filter by two criteria.

One criterion is on the Subform, and the other criterion is on the Main Form.

The code I'm using to filter the subform isn't working for me, and the suggestions online end up like the original poster said - it shows ALL records on the Main Form regardless of whether the subform has the filtered value or not.

The examples I found online deal with a Text type field, and my example is a Number type field. I believe I adjusted it properly, but it's still not working right.

My Main Form has a combo box to search for the value I want to filter the subform by, and here's the code:

strTypeID = Me.cboTypeID.Value
strFilter = "Forms![MainForm]![SubForm].Form![TypeID]=" & strTypeID

With Forms!MainForm
        .Filter = strFilter
        .FilterOn = True
    End With

[June7]

Filtering the subform has nothing to do with filtering the main form (children don't dictate to parents).

Looks like you need to use both techniques suggested.

[Varda]

FilteringSubform.zip

I'm uploading my database to show the issue I have.

I've placed both filtering options suggested: Combo box and List Box. Neither works for me.

I know the issue is with the code I placed in the "After Update" of the "Apply Filter" command button. I'm just not sure how to change it to make it work.

I am not sure I understand "children don't dictate to parents." I'm trying to find all the parents that have male children - isn't this a valid question to ask in a main form?

[June7]

I mean the subform (the child) does not dictate filter of main form (the parent). The reverse is true. The subform will show only those records that are associated with the record on the main form.

These tables have many-to-many relationship. Including tblReportItems in the main form RecordSource will cause repetition of the tblItems data when there are multiple related records in tblReportItems. This is not appropriate for a form/subform data entry arrangement. ItemID should not be a combobox on the subform. ItemID is the Master/Child link between the forms. It should not be editable on the subform. Shouldn't even be visible there.

Maybe you should consider the subform as an independent form. Set up as Continuous form and put the filtering controls into its header section.

[Varda]

The database I uploaded is a shortened version of the database I have. I did it a bit rushed and didn't check to make sure it reflects everything the way I designed it. Though I'm a bit baffled by some of the issues you raise because I don't see them on my version of the database. (I'm using MS Access 2010).

ItemID is not editable on the subform and does not show on it in my version.

The Form-Subform relationship is ideal for this particular many-to-many relationship.

Every Item can be identified with possibly just a Weekly Status Report, or Weekly Status Report and Monthly Report and Leadership Report.
Each one of these type of reports can be associated with multiple items.

There should be no repetition of tblItems data.


I'm confused about your suggestion to make the subform an independent form. I need to create the filter on a Data Entry form, not in a continuous form.

[June7]

Data entry can be done in a Continuous form. A Continuous form can be made to look like Datasheet. However, up and down cursor keys don't seem to work.

You should add multiple records associated with an Item then requery the form and see what happens. I added a Leadership Report for ItemID 1 along with the Weekly Status Report record and requeried the form. Now there are 2 ItemID 1 records on the main form.

You're right, ItemID combobox is not showing even though it is in Design View and it is set to be Visible. Same for Reports_ItemsID textbox. They have been set as hidden in form view. A user could unhide them. There is no reason to include these controls on the form.

[Varda]

I found out the issue and why you're seeing double (and it's not the New Year alcohol that's doing it). When I re-created the database, I based it on the wrong query. I fixed it and am re-uploading the database to properly reflect what I have in the larger version.

the issue with resolving this with a continuous form is that my original database has a LOT of data entry fields, and a couple of them are of the Memo type, which necessitates as much screen real estate as possible per item. A continous form would annoy the end-user tremendously.

I made the combo box on the subform invisible. Hopefully this would resolve the issue. The reason I included them on the subform is because I wasn't sure why my filtering code wasn't working and figured it's best to place them on the subform so the filter works.


I'm trying to do a work-around and filter the main form only by Status, and then filter by three check marked fields. But I'm having trouble even with that feature. I will likely post a new post entirely, as it is no longer relevant to the original post here.

[June7]

This subform doesn't have memo fields, so that is irrelevant. Why would a continuous form that looks like datasheet be annoying - aside from the lack of up/down cursor movement?

You have lookups with alias in table. Advise against this unless you really understand what is happening. Review: http://access.mvps.org/access/lookupfields.htm

Why are you having trouble with the filtering, what happens - error message, wrong results, nothing?

Appear to be missing = sign for: strStatus = "='Closed'"