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

0
votes

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

2
vbams-accesssubform
Can you give some details of your form design and table structures? You probably need something along the lines of Me.Parent!Address.Form.Filter = "companyID = " & Combo51 although it seems from your limited description as though you might not need subforms at all - are you displaying multiple address / CompanyInformation records for a single Company? – Skippy
I tried the above code there were no errors but it did not work. I am displaying multiple address per multiple companies. Also have multiple quotes with multiple companies. I need to be able to navigate through all the different address for all the companies along with there quotes. So I will be displaying multiple address/CompanyInformation records for a single company. – ZacAttack
Sorry, still having trouble understanding your data structure. Please provide tables and relationships to make this clearer and allow others to help. When you talk about "multiple addresses per multiple companies" and "multiple quotes for multiple companies" - are these both many-to-many relationships? Can one company have many addresses? Can one address relate to multiple companies? Can one quote relate to multiple companies? Can one company get multiple quotes? I'm guessing that the answers to these questions are not all "yes"! – Skippy
I have a tblQuotes Which has a primary key QuoteID which has a one to many relationship with tblCustomerNamesFromQuotes. Then tblCompanyNames has a primary key CustomerNameID and it has a one to many relationship with tblCustomerNamesFromQuotes also. So a customer can have many quotes and a quote can have many customers. – ZacAttack
Next I have two other tables tbl AddressType and tblCustomerAddressesFromQuotes. tblCAFQ right now has three primary keys first AddressID then CompanyID form tblCompanyNames, and finally AddressTypeID which is from tblAddressType. Table address type has a primary key called AddressTypeId and it references whether or not it is a billing or shipping address or both. I don't not have any relationships established at this time for these tables because I was getting errors in data entry on my form. – ZacAttack

2 Answers

1
votes

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?

0
votes

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