how do I filter a subform from a combobox that is loctated in another subform in access?

Question

I have found a lot of tutorials and questions and answers on how to filter a subform from a parent form, but I am having trouble finding a way to filter a subform with a combobox located in entirely different subform?

Private Sub Combo51_AfterUpdate()
      Me.Parent!Address.Form.Filter!Address2.Value = Combo51.Value
End Sub

I am getting an object required Error

Address2 is linked to my companyID(control Source) in the Address Form Combo51 holds the company Id values

Combo51 is located on the subform CompanyInformation and Address2 is located on the subform Address

The companyinformation form is the child of the parent form Called Quotes. So quotes is my main form with two sub forms CompanyInformation and Address. The Id form the parent form is linked to CompanyInformation; now I want to filter from company information to the Address form by using the companyID from combobox51.

I have success updating text and comboboxes from subform to subform but I just don't quite understand the filter method. Any help would be appreciated.

I am using a query for the record source for the Address form

Answer 1

I am intrigued by your design. I must say that it's unusual to have a many-to-many relationship between Quotes and Companies, but if that's what you need then go with it! [Edit: Just read your latest comments and I can see why you've done this now.] However I am concerned by the "3 primary keys" in tblCAFQ. I am interpreting this as being a "composite primary key" made up of the three columns AddressID, CompanyID and AddressTypeID, in which case I would expect these three IDs to also be foreign keys to other tables. You have identified that CompanyID and AddressTypeID are indeed foreign keys, pointing to tblCompanyNames and tblAddressType respectively, but you haven't said where AddressID points to. I suspect that AddressID is in fact the primary key and the other two IDs should just be foreign keys. Anyway, that aside, from the information you have posted so far, I think what you need is

Private Sub Combo51_AfterUpdate()
    Me.Parent!Address.Form.Filter = "CompanyID = " & Combo51
    Me.Parent!Address.Form.FilterOn = True
    Me.Parent!Address.Form.Requery    'You might not need this, I can't remember if it happens automatically after setting FilterOn = True
End Sub

This is assuming that the bound column of Combo51 is picking up the CompanyID (from tblCustomerNamesFromQuotes) on the CompanyInformation subform?

Answer 2

Try also to turn FilterOn:

Private Sub Combo51_AfterUpdate()
      Me.Parent!Address.Form.Filter = "Address2 = '" & Me!Combo51.Value & "'"
      Me.Parent!Address.Form.FilterOn = True
End Sub