Many to One relationships

[Ryan Langton]

I'm having some trouble wrapping my head around Form design for Many-To-One
relationships. For example, say I have an Item table and an ItemGroup
table. I want to design a form that has every detail for the Item (ItemID,
ItemName, ItemQuantity, etc). This form also needs to include a drop-down
box that allows me to select an Item Group, and then shows me the details of
that item group (and allows editing - ItemGroupName, ItemGroupDescription,
etc).

I've tried setting my recordsouce for the form to a Select that includes a
join between the tables Item and ItemGroup. This doesn't work because it
won't allow me to edit the data fields for ItemGroup. I'm trying now to
just have Item on the main form, and then use a subform (with invisible
borders so it appears to be on the same form) for ItemGroup. This, however,
is creating a new record in ItemGroup every time I try to select a group
from the combo-box. This is understandable though because when the form
loads the Item does not belong to an ItemGroup so I imagine the subform is
set to a new record?

I'm guessing this is a common design issue and hope somebody can nudge me in
the right direction

Thanks,
Ryan

 

[Ryan Langton]

I'm still struggling with this. I believe that using a subform is probably
the way to go. My recordsource for the main form is the ITEM table, my
recordsource for the subform is the ITEMGROUP table, the form-subform is
linked on ITEMGROUPID (the primary key of ITEMGROUP - an autonumber). I'm
still having trouble figuring out how to make this work so that the user can
change the ITEMGROUP that the ITEM belongs to. Whenever I use the combobox
containing itemgroup names, it creates a new record in the ITEMGROUP table.
I know this has got to be an easy problem I'm just not getting, please help


Ryan