forms based on many to many

  • Thread starter Thread starter Skippy
  • Start date Start date
S

Skippy

I have 2 tables linked by a junction table in a many to
many relationship. I am trying to make 2 forms, each
based on the 2 different tables, with a subform showing
related records from the other table.

In other words, I am trying to have the capability to
view and edit data from both sides of the relationship.
The problem is that when I enter data in the main and
subforms of either pair, it does not update the junction
table fields.

The 2 fields in the junction table are combined into one
key field, if that helps. Individually, they are the
foreign key fields, for the key fields in their
respective tables as they should be.

Can anyone please give me a nudge in the right direction?
I'm a newb and not sure what part of Access I need to be
tackling to fix this problem.

Thanks!
 
i think you mean that when both forms are open at the same
time, changes on one form are not immediately visible on
the second form

if this is the case, then what you need to do is call
Requery for any controls (on the other form) you want
updates for. you may need to go Forms!Form2.Controls!
Control1.Requery, for example

also note that to get a SubForm to requery, just reset the
SourceObject property.

so you'd go Forms!Form2.Controls!MySubForm.SourceObject =
Forms!Form2.Controls!MySubForm.SourceObject

it doesn't seem like you'd ever want to do that but it
triggers a requery
 
I have 2 tables linked by a junction table in a many to
many relationship. I am trying to make 2 forms, each
based on the 2 different tables, with a subform showing
related records from the other table.
Click to expand...

That's where you went wrong...
In other words, I am trying to have the capability to
view and edit data from both sides of the relationship.
The problem is that when I enter data in the main and
subforms of either pair, it does not update the junction
table fields.
Click to expand...

The Junction Table itself should be the Recordsource of the subform
(not the other "one" side table).
The 2 fields in the junction table are combined into one
key field, if that helps. Individually, they are the
foreign key fields, for the key fields in their
respective tables as they should be.
Click to expand...

That's not the correct design. Your junction table should have TWO
fields, one of them linked to the left-side "one" table, the other
linked to the "right". These two fields should be a joint, two-field
Primary Key, but it is neither necessary nor beneficial to combine
them into a single field!
Can anyone please give me a nudge in the right direction?
I'm a newb and not sure what part of Access I need to be
tackling to fix this problem.
Click to expand...

Typically in this situation you would have a Form based on either of
the "One" side tables, with a Subform based on the junction table. On
the Subform you would have a Combo Box using the other "one" side
table as its RowSource, allowing you to pick the desired ID.
 
Thanks for your fast response :)
-----Original Message-----
i think you mean that when both forms are open at the
same time, changes on one form are not immediately
visible on the second form.
Click to expand...

By both forms, do you mean one of the main forms with its
subform? I tried entering data in one of the main forms
and its sub, then closing them, exiting access, and
reentering to check the tables. The junction table was
not updated at all.
if this is the case, then what you need to do is call
Requery for any controls (on the other form) you want
updates for. you may need to go Forms!Form2.Controls!
Control1.Requery, for example
Click to expand...

Should I be putting this requery instruction(?) in the
properties of the 2 fields in my junction table?
also note that to get a SubForm to requery, just reset
the SourceObject property.

so you'd go Forms!Form2.Controls!MySubForm.SourceObject =
Forms!Form2.Controls!MySubForm.SourceObject
Click to expand...

I'm not sure what your saying here. Remember I'm a newb :)
I don't think I need explicit directions, just a nudge
towards what I need to focus on to get the job done.
Thanks again for your help.
 
Back
Top