One-Many, Many-One

[Guest]

One part of the database that I am building is about SupplyItems and the
Packages that they come in.

I have three tables:
Packages available (keyed by PackageID)
SupplyItems available (keyed by SupplyItemID)
and a third table to resolve the many to many (keyed by both of the primary
keys)

I'm trying to build a query that will be the basis of a subform that will
allow me to associate supply items and packages. But the query that I have
that relates these three tables will let me update records but not insert new.

I know I'm thinking wrong - but I don't know how. Can anyone steer me right?

 

[Graham Mandeno]

Hi Pam

Your query for the subform should NOT include the table on which your main
form is based (Packages?) but only the other two tables.

Your subform control should have LinkMasterFields set to the name of the
primary key field of your main form (PackageID) and LinkChildFields should
be the name of the related field in your junction table.

Then it should just be automatic.

Often it is necessary to base the subform on the junction table only, and
the related SupplyItems are selected using a combo box bound to the
SupplyItemID field in the junction table, and with its RowSource based on
the SupplyItems table.

 

[Guest]

Graham - Bingo! - I'm embarassed to say how long I beat my head against the
wall on this - thanks so much.

Pam

Hi Pam

Your query for the subform should NOT include the table on which your main
form is based (Packages?) but only the other two tables.

Your subform control should have LinkMasterFields set to the name of the
primary key field of your main form (PackageID) and LinkChildFields should
be the name of the related field in your junction table.

Then it should just be automatic.

Often it is necessary to base the subform on the junction table only, and
the related SupplyItems are selected using a combo box bound to the
SupplyItemID field in the junction table, and with its RowSource based on
the SupplyItems table.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

One part of the database that I am building is about SupplyItems and the
Packages that they come in.

I have three tables:
Packages available (keyed by PackageID)
SupplyItems available (keyed by SupplyItemID)
and a third table to resolve the many to many (keyed by both of the
primary
keys)

I'm trying to build a query that will be the basis of a subform that will
allow me to associate supply items and packages. But the query that I have
that relates these three tables will let me update records but not insert
new.

I know I'm thinking wrong - but I don't know how. Can anyone steer me
right?