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]!
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.
Guy
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]!
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.
Guy
ASKER
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.
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. This resolves my issue with filtering the combobox. I really appreciate your help.
Guy
Guy
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:
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,