Create form for 1:N relationsip

  • Thread starter Thread starter dynalt
  • Start date Start date
D

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?
 
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).
 
That helps.

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



Beetle said:
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


dynalt said:
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?
Click to expand...
Click to expand...
 
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


dynalt said:
That helps.

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



Beetle said:
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


dynalt said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top