Lookup on a table based on previous field on a form (1 Viewer)

[Bob M]

I have a data entry/view form which has a field Machine from a table (tblMachineProduction) and a combo field ProductID from (tblProduct) which will populate the field ProductID in tblMachineProduction. I would like to have the operator enter a machine number and restrict the lookup on the ProductID field to products having a itemclass equal to the item class in the machine master file (tblMachineMaster). The tblMachineProduction table and the tblMachineMaster are linked via intItemClass an indexed field in both tables. tblProduct and tblMachineProduction are linked via ProductID which are indexes in both tables.
I hope this isn't too crazy.

 

[RichMorrison]

Bob,

Do a search on "cascading combo boxes".

There are many threads with examples on this topic.

RichM

 

[Bob M]

I should have mentioned that the relationship between the machine master and the Product table is many to many.
In other words each machine in the machine master can have multiple item classes associated with it. Machines 21-30 can all have item classes 43-47 produced on them.

 

[RichMorrison]

You wrote
<<
I should have mentioned that the relationship between the machine master and the Product table is many to many.
>>

That's not a problem. You make a "link" table that contains the primary key of Product and machine master. This allows a many-to-many relationship.

RichM

 

[Bob M]

I've created the join table containing the primary keys from Machine Master and Product Master. In the join table we have machine numbers 61 and 62 with 204 product keys each 1 thru 204. that accounts for 408 records total. When I click the drop down arrow on the form to select a machine, I get 204 occurrances of machine 61 and 204 for machine 62. I select machine 61. When I go to the drop down arrow to select the product I get nothing.
I'm following the example from the Microsoft Knowledge Base Article Q209576 "How to Create Synchronized Combo Boxes".

Where am I going wrong??

 

[RichMorrison]

Bob,

To recap:

1) you have a Machine Master table
2) you have a Product Master table
3) you have a "join table" with 2 columns; primary key of Machine Master and primary key of Product Master. Each row in this table means there is an association between Machine and Product.

Right ?

I expect you also have a form with 2 combo boxes; Machine and Product. One of these combo boxes is the logical parent and the other is the logical child. I don't know which it is for you but that doesn't matter much.

The parent combo box should be based on the parent table. The child combo box should be based on a query that joins the "join table" to the child table. This query contains a criteria for the parent primary key. The criteria refers to the parent combo box.

That's a sketchy view. See other threads for more detail.

RichM

 

[Bob M]

I put a text box containing the Machine field from the Machine Master table. Then I put a combo box which had the row source sql statement (SELECT tblProduct.ProductID from tblProduct INNER JOIN tblMachineJoin ON tblProduct.ProductKey=tblMachineJoin.ProductKey WHERE tblMachineJoin.Machine=Forms![frmMachineProduction2]![Machine].

tblProduct is the child.
tblMachineJoin is the Join table
ProductKey is the link between the Join and child tables

I enter the Machine then I click the down arrow for the ProductID and the system asks me to enter both the tblMachineJoin.Machine and the Form![frmMachineProduction2]![Machine]. After I enter the same machine number as I entered before I get the correct information.

I suspect I'm close but don't know where to go from here.

Sorry for all the questions.

 

[Bob M]

Never mind that last post. I put form instead of forms for retreiving the machine from the master file.
Thanks so much for all your help and patience. This forum is a fabulous resource for all us who are new to Access.