Filter Field Selection from Previous Field

[Jason Lopez]

I have a form with two subforms where a field on the master form filters the
drop-down box query for the two subforms. How would I code the "Row Source"
in the Combo Box properties so that this would work.

Table1 has 8 fields that hold the core project information including which
department did the project and a unique identifier for each project. Table2
and Table3 are linked as "One-to-Many" by way of the unique identifier.
Each table holds a field of ProdName or ProgName based on what the table is
to being recording: product names or program names. That field is fill from
a master Program/Product (ProdProg) table that is filtered to each table
based on Product or Program. However, I am trying to figure out how to get
the ProdName or ProgName field to filter further based on the department
when I get to creating the form. Thereby filtering only the products or
programs that are related to that and only that department.

So, Table1 would be the foundation of the master form where Table2 and
Table3 would be subforms for recording the products (Table2) and programs
(Table3) used or referred to in each master project (Table1). So, how would
I filter the ProdProg table in the subform to only show the department
specific products (for subform1) and programs (subform2)?

Jason Lopez

 

[Tom van Stiphout]

On Mon, 18 May 2009 20:54:45 -0400, "Jason Lopez"
<jasonlopez32453-at-comcast.net> wrote:

You can make the query for the rowsource for the dropdowns refer to a
field in the main form. Something like:
select ProductID, ProductName
from Products
where DepartmentID = Forms!myParentForm!myControl

-Tom.
Microsoft Access MVP

 

[Jason Lopez]

I found something like that and have seen some good things as I have tried
to implement it. However, when I go to the next record or a new record on
the master form, the subform drop down boxes remain the same where the
department on the main form has changed. How do I get the subform drop-down
boxes to requery based on the new department (if applicable) presented in
the master form fields. Additionally, I get duplicates of the filtering.
What I mean is that I have two products for department1 and three products
for department2. When I filter on the first record, I get five records
where the two applicable ones are duplicated until I hit a total of five in
the list. What would cause that?

Jason Lopez