Create form for 1:N relationsip

[dynalt]

Objective: Create a form that will allow a list of values to be displayed
where each item is from yet another table.

Tables:

tblMain
MainID Autonumber (PK)
.. . .


tblPartsList
MainID (PK)
PartID (PK)

Q: Is this the best way to implement the one-to-many relationship? I have
seen this done with a single autonumber as a PK and MainID and PartID as
data, but I don't see any advantage -- it seems that an index is needed
either way.

tblParts
PartID Autonumber (PK)
PartName Text
PartDesc Text


Subform:

The subform should display a list of PartName, PartDesc matching MainID from
the parent form.

I want to add or delete parts from the list on the subform, but not edit the
fields in tblParts.

There are few enough entries in tblParts that a combobox could be used to
select a new part by PartName.

I have several similar one-to-many relationships to tables of possible values.

This seems like the same structure as an order - line item, but neither the
books I have available or Google queries turn up an answer.

Q: Is there a resource that details how to do this?

When I develop a query from tblMain.MainId -> tblPartsList.PartID ->
tblParts, I can get a table with the correct values, but I can't seem to link
it correctly, since the fields dropdown in Link Master / Children has only
bound controls and doesn't include MainID.

Q: How do I link the subform / query correctly?

Q: Can I use a combobox by PartName to add parts to tblPartsList? I can
create the combobox, but how does it get associated with the field in the
datasheet on the subform?

 

[Beetle]

What you're describing is actually a many-to-many
relationship between tblMain and tblParts
with tblPartsList as the junction table

Typically you would have a main form based on tblMain
(or a query of tblMain) with a subform based on tblPartsList
linked via MainID. In the subform you would have a combo box
for selecting the parts with properties such as;

Control Source = PartID
Row Source = SELECT tblParts.PartID, tblParts.PartName, tblParts.PartDesc
FROM tblParts ORDER BY tblParts.PartName
Bound Column = 1
Column Count = 3
Column Widths = 0",1",1" (or whatever works best for your form)

You could add another unbound text box to display the part description
when the combo box is not expanded.
Set it's control source to =[YourComboBox].Column(2).

 

[dynalt]

That helps.

Is there a better way to structure the tables? I *think* they do what I
intend.

What you're describing is actually a many-to-many
relationship between tblMain and tblParts
with tblPartsList as the junction table

Typically you would have a main form based on tblMain
(or a query of tblMain) with a subform based on tblPartsList
linked via MainID. In the subform you would have a combo box
for selecting the parts with properties such as;

Control Source = PartID
Row Source = SELECT tblParts.PartID, tblParts.PartName, tblParts.PartDesc
FROM tblParts ORDER BY tblParts.PartName
Bound Column = 1
Column Count = 3
Column Widths = 0",1",1" (or whatever works best for your form)

You could add another unbound text box to display the part description
when the combo box is not expanded.
Set it's control source to =[YourComboBox].Column(2).
--
_________

Sean Bailey

Objective: Create a form that will allow a list of values to be displayed
where each item is from yet another table.

Tables:

tblMain
MainID Autonumber (PK)
. . .


tblPartsList
MainID (PK)
PartID (PK)

Q: Is this the best way to implement the one-to-many relationship? I have
seen this done with a single autonumber as a PK and MainID and PartID as
data, but I don't see any advantage -- it seems that an index is needed
either way.

tblParts
PartID Autonumber (PK)
PartName Text
PartDesc Text


Subform:

The subform should display a list of PartName, PartDesc matching MainID from
the parent form.

I want to add or delete parts from the list on the subform, but not edit the
fields in tblParts.

There are few enough entries in tblParts that a combobox could be used to
select a new part by PartName.

I have several similar one-to-many relationships to tables of possible values.

This seems like the same structure as an order - line item, but neither the
books I have available or Google queries turn up an answer.

Q: Is there a resource that details how to do this?

When I develop a query from tblMain.MainId -> tblPartsList.PartID ->
tblParts, I can get a table with the correct values, but I can't seem to link
it correctly, since the fields dropdown in Link Master / Children has only
bound controls and doesn't include MainID.

Q: How do I link the subform / query correctly?

Q: Can I use a combobox by PartName to add parts to tblPartsList? I can
create the combobox, but how does it get associated with the field in the
datasheet on the subform?

 

[Beetle]

Based on your OP, they appear to be correct. It's hard to say for sure
without knowing more about what other tables/fields you may have. If your
unsure, you can post back with more detail.
--
_________

Sean Bailey

That helps.

Is there a better way to structure the tables? I *think* they do what I
intend.

What you're describing is actually a many-to-many
relationship between tblMain and tblParts
with tblPartsList as the junction table

Typically you would have a main form based on tblMain
(or a query of tblMain) with a subform based on tblPartsList
linked via MainID. In the subform you would have a combo box
for selecting the parts with properties such as;

Control Source = PartID
Row Source = SELECT tblParts.PartID, tblParts.PartName, tblParts.PartDesc
FROM tblParts ORDER BY tblParts.PartName
Bound Column = 1
Column Count = 3
Column Widths = 0",1",1" (or whatever works best for your form)

You could add another unbound text box to display the part description
when the combo box is not expanded.
Set it's control source to =[YourComboBox].Column(2).
--
_________

Sean Bailey

Objective: Create a form that will allow a list of values to be displayed
where each item is from yet another table.

Tables:

tblMain
MainID Autonumber (PK)
. . .


tblPartsList
MainID (PK)
PartID (PK)

Q: Is this the best way to implement the one-to-many relationship? I have
seen this done with a single autonumber as a PK and MainID and PartID as
data, but I don't see any advantage -- it seems that an index is needed
either way.

tblParts
PartID Autonumber (PK)
PartName Text
PartDesc Text


Subform:

The subform should display a list of PartName, PartDesc matching MainID from
the parent form.

I want to add or delete parts from the list on the subform, but not edit the
fields in tblParts.

There are few enough entries in tblParts that a combobox could be used to
select a new part by PartName.

I have several similar one-to-many relationships to tables of possible values.

This seems like the same structure as an order - line item, but neither the
books I have available or Google queries turn up an answer.

Q: Is there a resource that details how to do this?

When I develop a query from tblMain.MainId -> tblPartsList.PartID ->
tblParts, I can get a table with the correct values, but I can't seem to link
it correctly, since the fields dropdown in Link Master / Children has only
bound controls and doesn't include MainID.

Q: How do I link the subform / query correctly?

Q: Can I use a combobox by PartName to add parts to tblPartsList? I can
create the combobox, but how does it get associated with the field in the
datasheet on the subform?