Solved Filtering form and subform (1 Viewer)

jharding08

Member
Local time
Yesterday, 22:55
Joined
Feb 16, 2021
Messages
55
I have a form with a recordsource of one table. There are subforms that link to the main form using the Link Master/Child Fieldvand everything works great with adding, etc.

I also have dropdowns to filter the data. I take the value from the filter and create a string to use for the Form.Filter property.

I also now have a dropdown to filter by data/fields in the subform. If the field isnt part of the main form, how do I filter the main form with the value of a subform, that is a foreign key to the main form recordset. Would I have to do a filter in the subform?

Main Form
PK - ID

Subform
PK - ID
FK - MainForm.ID

This is the function called on the dropdown_Afterupdate event (all fields on the main form :

Private Sub FilterForm()
Dim strwhere As String

If Nz(Me.cboCustomer, "") <> "" Then
strwhere = strwhere & "[CustomerID] = " & Me.cboCustomer & " AND "
End If

If Nz(Me.cboStatus, "") <> "" Then
strwhere = strwhere & "[StatusID] = " & Me.cboStatus & " AND "
End If

If Nz(Me.cboSubject, "") <> "" Then
strwhere = strwhere & "[SubjectID] = " & Me.cboSubject & " AND "
End If

If strwhere <> "" Then
strwhere = Left(strwhere, Len(strwhere) - 5)
Me.Filter = strwhere
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub

The dropdown for the subform is cboProduct, would I create a separate filter for the subform and would that filter the main form based on the subform because of the linked fields?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:55
Joined
Oct 29, 2018
Messages
21,358
Hi. If I understand your question correctly, you would filter the subform separately from the main form. So, after you filter the main form, you can then filter the subform.
 

jharding08

Member
Local time
Yesterday, 22:55
Joined
Feb 16, 2021
Messages
55
This is what I tried, but when I filter the subform, I need the main form to filter based on that.

So for example, the main form is orders and the subform is products for that order.
Orders table
ID
Order No
CustomerID
StatusID

Order_Product table
ID
OrderID
ProductID

Product Table
ID
PRoduct Name

My subform allows users to add products to the order:
SELECT tbl_OrderProduct.ID, tbl_OrderProduct.OrderID, tbl_OrderProduct.ProductID
FROM tbl_OrderProduct;

For the subform:
Link Master ID:ID
Link Child ID: Order ID

I have a dropdown at the top of the main Order Form for filtering by Product. This would filter the main form based on orders that have that product on them. But the product data is on the subform. and it doesnt filter the orders when I filter the subform

Would I filter the subform based on product ID, then the main form based on the subform order IDs from that result set?
Right now I just have the product dropdown calling FilterSubform independently

Private Sub FilterSubForm()
Dim strwhere As String

If Nz(Me.cboProduct, "") <> "" Then
strwhere = strwhere & "[ProductID] = " & Me.cboProduct
End If

If strwhere <> "" Then
strwhere = Left(strwhere, Len(strwhere) - 5)
Me.subfrm_OrderProduct.Form.Filter = strwhere
Me.subfrm_OrderProduct.Form.FilterOn = True
Else
Me.subfrm_OrderProduct.Form.Filter = ""
Me.subfrm_OrderProduct.Form.FilterOn = False
End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:55
Joined
Oct 29, 2018
Messages
21,358
This is what I tried, but when I filter the subform, I need the main form to filter based on that.
Can you post a screenshot with some annotations? Without seeing your setup, it's hard to follow what you're trying to describe.

A form/subform setup typically represent a one-to-many relationship. The main form is supposed to display the parent record and the subform is automatically filtered to show the related records to the current record displayed on the main form. Filtering the main form based on the data in the subform is sort of backwards to how a form/subform setup should work.
 

jharding08

Member
Local time
Yesterday, 22:55
Joined
Feb 16, 2021
Messages
55
This is the main form
1631560746588.png


A treatment has one customer, one disease and one season. It can have multiple species and multiple products.

The users can filter up top by customer, disease and season to narrow the records in the recordset. They also want to filter by product, showing the treatments in the system that use a certain product. The product is in a different table since it is many products for one treatment.

To filter by product, it would mean filtering the Product subform, then filtering the main form by the orders in the subform, is that correct?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:55
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you saying the user wants to review all customers using a particular product? If so, you would typically create a form/subform where the main form shows the product and the subform showing the customers.

In the above screenshot, if the user filters the main form to customer #1 who may have used products 2 and 3, but then the user wants to filter the subform to show only product 3, are you saying you want the main form to also show the other customers who used product 3?
 

jharding08

Member
Local time
Yesterday, 22:55
Joined
Feb 16, 2021
Messages
55
The Main screen is for Treatments/ORders and the filters are all based on filtering the Treatments (Orders). Each treatment/order has one customer and multiple products (shown using the subform on the treatment/order screen). They would use the product dropdown to show all treatments/orders that have that product associated with it.

For example:
Treatment/Order : 1
Product: 1, 2, 3

Treatment/Order: 2
Product: 2,3

Treatment/Order: 3
Product: 1

Treatment/Order: 4
Product: 2


User selects Product 2 in dropdown, Treatment screen filters treatments/orders to show Treatment/Order 1, 2 and 4
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:55
Joined
Oct 29, 2018
Messages
21,358
The filters are all based on Treatments (Orders). Each treatment/order has one customer and multiple products (shown using the subform on the treatment/order screen). They would use the product dropdown to show all treatments/orders that have that product associated with it.
Then perhaps you need a different form setup. That sounds to me like the product dropdown should be on the main form, rather than the subform. Also, there may be a disconnect here since we are not familiar with your business process. We don't understand your situation or what you want to do, because we don't understand your SOP.
 

jharding08

Member
Local time
Yesterday, 22:55
Joined
Feb 16, 2021
Messages
55
I think I wouldve needed to do the filtering with SQL in code to get it to work. I found a workaround by updating the form recordsource based on a subquery of the subform recordsource.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:55
Joined
Oct 29, 2018
Messages
21,358
I think I wouldve needed to do the filtering with SQL in code to get it to work. I found a workaround by updating the form recordsource based on a subquery of the subform recordsource.
Hi. Glad to hear you found a solution. Good luck with your project.
 

Users who are viewing this thread

Top Bottom