Many-to-Many relationships: layout and form/subform application (1 Viewer)

[Geezer]

Have posted a similar question over in the Forms forum but this may be more relevant to Tables:

http://www.access-programmers.co.uk/forums/showpost.php?p=1018824&postcount=16

It would seem I should not be running multiple subforms over multiple tabs from the same record source. Does anyone have any recommendations on how to lay my tables out given that I'd like to maintain the current form design?

Two possible solutions:

1) split the subforms related table into groups dependant on the data in the tabs and link them all back to the one junction table.

2) split the tables as above and create individual junction tables for each.

My prefered option would be No 1 above but would having one junction table still cause problems with the record source as it'll still be needed in my queries to link back to the main form record source.

Thanks in advance.

 

[Rabbie]

Firstly we need to know more about your data structures before giving definite advice.
Secondly is your data properly normalised?

 

[Geezer]

Thanks Rabbie, besides the link to the above post in the Form forum how much more info would you like? Am happy to supply what I can.

In terms of normalisation I believe I've done a fairly good job in cleaning the original flat table up into individual tables and creating relationships (3NF). There are a couple other tables linked to the "main" table but they're on the other side of the above relationship.

 

[gemma-the-husky]

when you add a record on one subform, it won't automatically reflect in the other subforms, because they have already established a working "recordset" , if you will.

when you add a new outdoor model you then need to

mysubform1.requery
mysubform2.requery

that's why you get the #deleted as well.

note that requerying a form, repositions the cursor to the first record.

I think this might sort the problem out.

 

[Geezer]

Thanks Dave,

Can you comment on my table layout and that I should not be running multiple subforms off the same "recordset" or "record source". My subforms appear to be running fine except for the non-updating issue but if I shouldn't be running them off the same record source then I need to rethink my design.

Also, do we class a table and aquery based on the same table (or maybe a combination) as a different "record source"?

Thanks,

Gareth