Many to Many Relationship

[pl456]

I am aware of these but never had to make one, what I have done at the
moment is create two tables and a third junction table. I have created the one to many relationships to the junction table.

I have created a form and a subform to view the data.

What happens is I get a list of products in my sub form but when I enter data against them and move to the next record on the main form ths sub form data appears the same for each main record. I expected to get a list of teh same items but without any data against them.

Any ideas what I am doing wrong?

 

[Dennisk]

I suspect you do not have the subform 'linked' to the mainform.

For it to operate correctly you have to specify the unique key (PK on the one side) that links to the (FK) in the sub form.

look on the data tab of the subform.

 

[pl456]

I think i have done what you said but it has not quite worked.

I changed the child & master fields to link to the respective PKs.

What happens now is that I get only one record listed in the sub form which is different for each main form record, when I was expecting 5 records for each.

 

[neileg]

I wouldn't use a form/subform for this. I would construct a query that joins the 3 tables and base a form on that. I'd put the main table details in the form header and the subtable data in the details section.

 

[pl456]

I tried creatating a query but it doesnt work when the junction table is shown on the grid, mismatch in join.
When I remove the junction table the query runs and looks like what I kind of expected, however if you update a record it cascades through all of them.

 

[neileg]

Can you post the SQL of your query.

 

[pl456]

SELECT DISTINCTROW quetion.qpk, quetion.question, quetion.answer
FROM host INNER JOIN (quetion INNER JOIN [junction table] ON quetion.qpk = [junction table].qpk) ON host.hpk = [junction table].hpk;

This is the one that does not work until I remove the junction table

 

[pl456]

Still not got any further with this.

 

[neileg]

Why DISTINCTROW? Why have you joined the junction table in the query when you aren't pulling any data from either the junction table or whatever table it is linked to?