subforms?

[nelleh]

Hi,
I have 3 tables : Products, Description and ProductDescription. Products can have more then 1 description, Descriptions can be used by more then 1 product, that is why the third tabel ProductDescription exists (normalizing.. ). The Productdescription table has 3 fields: id, productid, descriptionid.

I want a form in which you choose a product, then you see all the descriptions along with it. In this same form I want to be able to add a description, e.g. by pushing a button NEW. After that user enters description form etc. After returning to the mainform, a new record must be automatically added to the ProductDescription table and ofcourse a new record has been added to the Description table. I thought I would use subforms to realize this but it isn't working.. . I have tried so many things that I dont know anymore what to do, i am totally confused. I know how to program in VB and ACCESS, but for now i don't see it anymore. Can someone please help me with this?

I just need to know how to start, what to use as subform and what as masterform, which links between fields, etc

nelleh

 

[Ayat]

M-M tables and their access through forms

I too have the same question. Here is my specific problem. Any help/guidance much appreciated.

Lets say I have three tables in a M-M relationship.

PRODUCTS
========
Prod_id
Prod_desc
xx
yy
:


DIVISION
========
Div_id
Div_name
Div_lead


PROD_DIV
========
Prod_id (FK)
Div_id (FK)


Now, I have a product form where i am displaying all the product
related information. I also want to show in this form all the
Divisions that this product belongs to.

So I created a subform (using the wizard. tried creating
independently too. however, I guess sny of the approach should work)
for the Divisions and put it into the products form with the hope
that for any product I should be able to associate one or many
divisions right from this form (the underlying M-M table threby
getting automatically populated) and for products that already have
divisions associated with it, should be able to see all the
divisions it belongs to in this product form.

So when creating the subform, I chose Prod_id and Div_id from the
PROD_DIV table and Div_name and Div_lead from the DIVISION table and
inserted this form in the Products form. When I have a particular
prod_id chosen in the Products form, I am seeing that id in the
subform (which is good) and am then trying to associate the
divisions (btw, I have already populated the DIVISIONS table with
some divisions and expect to select this in the product form for a
given product).

Problems are:

1. when I am trying to choose a Div_name (I created a drop-down list
for the Div_name field) for a product, it is creating another record
in the DIVISIONS table instead of just populating the PROD_DIV
table. this should not happen as I need to just associate an
existing division to a product in the products form, therefore only
the junction table needs to be filled. right?

2. When I choose the Div_name in the subform, shouln,t the
associated Div_lead (as defined in the DIVSIONS table) appear
automatically in the Div_Lead field in the subform?

I a nutshell, the basic question is how one deals with tables in M-M relationships through forms.

 

[nelleh]

forms-subforms

Ok, I didn't receive a lot of help (yet?). I made a form and on top I used a listbox containing the Products. then I made a subform wich is using a query which contains the Description and the ProductDescription table. Now when I choose a product then automaticaly all the descriptions are showed.

I made 2 buttons, new, and change. If you select an existing description, then choose change there is a form opened in which you can change the description.
the second button New should be clear, but it is not working properly yet. because I want to use the same form that changes the description. Only in this case you get a new record. before opening the form I have set the dataentry to TRUE and AllowEditions to TRUE, but it is not (always) working. I dont know at which point I have to change these settings?

Second, if a record is added in the Description table, how do I let the program add a new record in the conjunction table (ProductDescription)?

Third, Is it possible to make this work easier by using another query in the subform? So that if you add a new description, the conjunction table will also be filled?