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.