linking values in fields via table or form

[SueM]

Hi there,

I'm having problems with a table I'm trying to get up &
running.

In my table I have 2 fields. One for the product group
named ProdGrp which contains the looks up all the major
product groups we hold from the product group table and
one for the product description called ProdDesc which
looks up all products we hold from a query on the products
table and the products group table which has a one to many
relationship. The one being on the product group side.

What I need to be able to do is to narrow down the
products in ProdDesc to only those within the product
group chosen by the user in the ProdGrp field.

I've tried all sorts but must be putting some thing in the
wrong place. In the table I've tried putting ProdGrp in
the criteria for the Query that ProdDesc runs off. But
this didn't work - I had no values returned in my list.

So I thought maybe this should be done on a form. I
creatde a form and for ProdDesc I put in ProdGrp as the
control source. But this doesn't work either - I still get
a full list of products in ProdDesc after choosing the
product group and after choosing from the product
description the product group updates to the correct one
for the product.

That bit is pretty cool, but isn't what I need as I want
to narrow down user choices to a product group basis from
the get go.

What am I doing wrong???

-Lost!

 

[Tim Ferguson]

In my table I have 2 fields. One for the product group
named ProdGrp which contains the looks up all the major
product groups we hold from the product group table and
one for the product description called ProdDesc which
looks up all products we hold from a query on the products
table and the products group table which has a one to many
relationship. The one being on the product group side.

I don't really understand this. If each Product belongs to one Group, then
I would put a BelongsToGroupID in the Product table. The description you
have here sounds more like a many-to-many relationship, where each Product
can belong to many Groups.

If you are trying to put together a form that shows details from the
Products table, but limited to products from the chosen Group, then there
is a number of ways to do that. But you have to be clear about your table
design first.

B Wishes


Tim F