forms and subforms (John Vinson pls read)

  • 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.
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!

I didn't explain myself well enough. They are 2 seperate
fields. I selected both simultaneously and hit the key
field button on the toolbar. They are as you said above.
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...

Lets call the 2 forms ORDERS, and ITEMS. They have a many
to many relationship because most orders will have more
than 1 item, and most items will be in more than 1 order.

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.

Im not sure what you mean here. How would the junction
table subform load the entered data into the ITEMS table?
(assuming the main form was ORDERS)

I'm a newb, and though I've been studying Access for
several months on my own, I'm obviously missing something
fudamental here. Thanks again for your help.

-Skippy
 
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.

Im not sure what you mean here. How would the junction
table subform load the entered data into the ITEMS table?
(assuming the main form was ORDERS)
Click to expand...

It wouldn't. It would let you PICK an existing item from the Items
table and associate it with this Order. Sorry if I misled you there!

Normally you would have a Combo Box on the subform, based on the Items
table; in that combo box's NotInList event you would put code to open
the Items form as a "dialog" form (one that appears and keeps the
focus until it's closed) to enter a new item if the user can't find
the desired one in the list.
I'm a newb, and though I've been studying Access for
several months on my own, I'm obviously missing something
fudamental here. Thanks again for your help.
Click to expand...

You might want to be sure that the "Northwind" sample database that
comes in the Samples folder on your Access CD was loaded, and take a
look at it. The Orders form is an example of exactly what we're
discussing here (with a few bells and whistles, some of which may get
in the way more than help, but that's the breaks!)
 
You might want to be sure that the "Northwind" sample database that
comes in the Samples folder on your Access CD was loaded, and take a
look at it. The Orders form is an example of exactly what we're
discussing here (with a few bells and whistles, some of which may get
in the way more than help, but that's the breaks!)

John W. Vinson[MVP]
Click to expand...
Thanks, I'll take a close look in Northwind :) -Skippy
 
Just thought you might be interested ----

I am in business to provide customers a resource for help with Access, Excel and
Word applications. I have provided tutoring to many customers. If you are
interested, contact me at the email address below.
 
Back
Top