• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1798
  • Last Modified:

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.

Guy
0
munequito
Asked:
munequito
  • 3
  • 3
1 Solution
 
mbizupCommented:
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,
0
 
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.
0
 
mbizupCommented:
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.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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.
0
 
mbizupCommented:
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)
0
 
munequitoAuthor Commented:
Thank you very much. This resolves my issue with filtering the combobox. I really appreciate your help.

Guy
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now