How to filter a combobox in one subform, based on a combox in another subform

Hello Experts,
I am not an Access expert so hopefully I will use the proper terms. In summary, I want to filter the contents of a combobox in one subform, based on a field in a combox in another subform. A description follows...

I have an Access front end that consists of a form that has four tabbed pages. Each page has a subform on it. The data comes from an MS-SQL database.

The main form is called Form1. The four subforms are called Sub1-Recips, Sub2-Batches, Sub3-RptHdr, and Sub4- RptDtl.

Sub1-Recips enables the user to add, modify, view, and delete records from the TMS_ARTS_RECIPIENT table. (PK = recipient_id). This form works fine. Recipients are people who will receive reports from our automated reporting system.

Sub2-Batches enables the user to add, modify, view, and delete related records from the TMS_ARTS_BATCH table. (PK = uni_batch_id, FK = recipient_id). This form works fine. Batches are groups of reports that are to be generated by our automated reporting system. One recipient may have multiple batches of reports.

Sub3-RptHdr enables the user to add, modify, view, and delete related records from the TMS_ARTS_RPTHDR table. (PK = uni_rpthdr_id, FK = uni_batch_id). This form works fine. Report headers define the report type that is to be generated (examples: BOL, inventory, summary, etc.). One batch may have multiple report headers.

I am having trouble with Sub4-RptDtl. I want this form to enable the user to add, modify, view, and delete related records from the TMS_ARTS_RPTDTL table (PK = shipper, destination; FK = uni_rpthdr_id). I want the user to be able to select  a destination from a combobox that only includes data for the shipper in the related header record but no matter what I try, the combobox is either populated with records for all shippers, or it is populated for the wrong shipper, especially after selecting a different shipper in the sub3-rpthdr form. Destinations are specific location numbers that a report header may need; each report header may have zero or multiple destination/detail records. I might also mention that two different shippers might have the same destination records (example: shipper 1 and shipper 2 could both have a destination #10)

I have a textbox in the header of form1 named form1_shipper. The control source for this field =[TMS_ARTS_RPTHDR].[Form]![cbo_SHIPPER].[column](0) and it properly displays the shipper number of the selected report in the sub3-rpthdr  form.

Needless to say, I have tried to resolve this several ways before asking for help but I am certainly missing something somewhere. Thanks in advance for any help you may offer in this regard.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm having a hard time following the various subform and control names...

But basically if you want a combo selection in one subform to control the dropdown list of a combo in a 'sibling' subform, use VBA in the After Update event of the combo box in the first subform:

Me.Parent.[Other Subform Name].Form.[Combo Name].rowsource = "SELECT [SomeField] FROM [Some Table] WHERE [Shipper ID] = " & Me.[cbo_SHIPPER]

Open in new window

You'll have to replace my generic form and control names  (the names enclosed in brackets) with the actual names used in your database,
munequitoAuthor Commented:
To simplify-

Each detail row in subform Sub3-RptHdr has a combobox that includes the shipper number. Example: One row has shipper #2638 and another row has shipper #1095.

Each detail row in subform Sub4-RptDtl has a combobox that should only include destinations for the shipper that is selected in subform Sub3-RptHdr.

But this is what is currently happening: if I select shipper 2638 in Sub3-RptHdr, the combobox in Sub4-RptDtl correctly only has data from shipper 2638. If I go back to  Sub3-RptHdr and simply select a different row with shipper 1095, the combobox in Sub4-RptDtl *incorrectly* still has data from shipper 2638.
Ah... you're working with continuous forms.
The answer here from my perspective is that you can't/shouldn't do that. Even though you have multiple rows in a continuous form, the combo is a single control... so it will have the same properties in all rows.

You can try placing that code in the current event of your subform... which will change the list as the user moves between records,but you will have problems with that approach as well if you are using the limit to list property.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

munequitoAuthor Commented:
Thank you, mbizup. I had read elsewhere that you can have a textbox in the header of form1, which is populated with the shipper number from the currently selected row in subform Sub3-RptHdr. I have this working correctly. Example: If I select the first detail row in Sub3-RptHdr the textbox in the header has value 2638; if I select the second row, the textbox has value 1095.

I had also read that I could use that textbox to filter the contents of the combobox in my Sub4-RptDtl, but I have not been successful in that regard. Thanks for your help. If you or anyone else can provide additional guidance to step me through this I sure would appreciate it.
Form1 is the main/parent form, correct?

This is not very different from my last comment regarding the current event... it just simplifies the form references.

Try this from the  Current Event of Sub4-RptDtl:

Me.NameOfComboToFilter.Rowsource = "SELECT [SomeField] FROM [Some Table] WHERE [Shipper ID] =  " & Me.Parent.YourTextboxName

Open in new window

That will filter the combo box according to the value in the textbox.

However, the problem is that this filtering will happen in ALL records of your subform.

So if you happen to be using the Limit to List property in your combo box, you are likely to see selected values "going blank" in other records.

(If you are not limiting to list, however, it wont be an issue)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
munequitoAuthor Commented:
Thank you very much. This resolves my issue with filtering the combobox. I really appreciate your help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.