Search code examples
formsms-accessmany-to-manylookup

Many to Many Lookup Access

I have a database that contains a table "tbl_Formulation" that pulls multiple values of StockID from the same table "tbl_MaterialStock" via five different queries. I am designing a form that looks up the material relating to each of these StockID values in an earlier table "tbl_material".

How do I lookup the Material for a specific stock usage in my formulation form. I've tried using dlookup to do this but I can't work out a way to specify the lookup of a specific type of StockID.

I've attached a couple of pictures to try to explain the problem better.

Relationship Structure Relationship Structure

One StockID type One StockID type

Another Another

Current control source code Current control source code

A lil simplified explanation in paint. A simplified explanation in paint

Solution

  • Simply run an INNER JOIN across tbl_Material, tbl_MaterialStock, and corresponding query in the combo boxes's RowSource query. Below demonstrates for Filler:

    SELECT m.Material, q.StockID 
FROM (tbl_Material m
INNER JOIN tbl_MaterialStock s
ON m.MaterialID = s.MaterialID)
INNER JOIN qry_Filler q
ON q.StockID = s.StockID

    Be sure to specify 

    Format tab
Column Count: 2

Data tab
Bound Column: 2