Forms & Sub form link

[Andy Roberts]

I'm having a bit of trouble getting my head around this and I can't decide
whether my table design & relationships are wrong or whether its the set up
of my form / subform (I think it is the form)

I want to store progress on a daily basis about stretches of a river (called
reaches) and individual parts of each reach (called sections).

I have 3 tables - tblProgress linked to tblReach which is linked to
tblSection. On my mainform (source is the tblProgress) I pick all the
progress info such as date, individual etc as well as the ReachID. I then
have a subform to enter the data about each section. The available
sections from a cbo are determined by the reach I select on the mainform.
I pick a section and then enter details about it. The source of the subform
is tblSections (which is linked via tblReach to the tblProgress.

Normally I would have a subform which was linked to the mainform, however in
this case the subform is linked to another form which is then linked to the
main form. I understand that the subform needs to have the same ID control
as the mainform to be linked, but I then loose the filtering based on the
reach selection.

When I enter data is it not being stored correctly. I want the subform
information to be stored against each section in the tblSection but it
creates new records instead of adding to those fields already there

I'm sorry of this isn't explained the best. Let me know if you don't
understand and I'll try to explain some more.

Andy

 

[JamesDeckert]

On my mainform (source is the tblProgress) I pick all the
progress info such as date, individual etc as well as the ReachID.

I don't think ReachID should be in your tblProgress table.
My understanding is that tblProgress has a one to many relationship to
tblReach which has a one to many relationship to tblSection. Is this correct?

Maybe if you showed the fields for each table I'd understand.

James

 

[Andy Roberts]

James

You are correct. Look here (its setup as option2)
http://www.blue-bean.co.uk/rel.htm

Andy

 

[JamesDeckert]

Andy,
Your tables aren't set up the way I understand your description to be.
Your daily progress can have several reaches. And each reach can have
several sections. If this is correct then your table design is exactly
backwards.

On your relationship picture you have each section having several reaches
and each reach having several progresses. Note the 1 and infinity symbols
above the join lines connecting the tables.

If my understanding is correct you need to remove the join lines in your
relationships between these three tables. Then you should remove the reachID
from tblProgress. Remove SectionID from tblReach. Add ReachID to tblSections
(I just noticed you have a ReachID and ProgressID in tblSections). Remove the
ProgressID from tblSections. Add ProgressID to tblReach. Now join the three
tables via the ProgressID (tblProgress and tblReach) and ReachID (tblReach
and tblSections).

Hope this helps
James Deckert