Junction table

[vbnetman via AccessMonster.com]

A portion of my db deals with parts and what they fit. For example, one piece
of equipment can have many parts and more than one piece of equipment can
have the same part, an oil filter for example. I’ve arranged a junction table
to handle this.

I have 3 tables:
TblUnits – unitId is PK
TblParts – partNo is PK
TblFits(junction table) – field refUnitId is FK to TblUnits
Field refPartNo is FK to Parts

On my form, a user may select a unit from the units table, associate a part
with it from the parts table and both selections go into the fits table. I’m
using a listbox for the parts table and the fits table. How do I get
selections (unitId and PartNo) into the fits table? Seems like I need an
append query although I’m not sure about how to proceed from here. Can
someone jump in and assist?

vb

 

[Marshall Barton]

A portion of my db deals with parts and what they fit. For example, one piece
of equipment can have many parts and more than one piece of equipment can
have the same part, an oil filter for example. I’ve arranged a junction table
to handle this.

I have 3 tables:
TblUnits – unitId is PK
TblParts – partNo is PK
TblFits(junction table) – field refUnitId is FK to TblUnits
Field refPartNo is FK to Parts

On my form, a user may select a unit from the units table, associate a part
with it from the parts table and both selections go into the fits table. I’m
using a listbox for the parts table and the fits table. How do I get
selections (unitId and PartNo) into the fits table? Seems like I need an
append query although I’m not sure about how to proceed from here. Can
someone jump in and assist?

If the form is bound to the fits table, then the list boxes
can be bound to their respective fields and the table is
updated automatically.

A far more common design is to have a main form bound to the
units table and a continuous subform bound to the fits
table. The subform control's LinkMaster would be the unitID
and the LinkChild would be the refUnitId field. Then the
subform would have a hidden text box bound to the refUnitId
field and a combo box with its RowSource set to a query on
the parts table.

 

[vbnetman via AccessMonster.com]

MB,
I am trying your suggestion. Could you elaborate on the 'hidden text box'
purpose? The subform already has a refUnitId field in this scenario and it
seems redundant.

Thank you

vb

 

[Marshall Barton]

I am trying your suggestion. Could you elaborate on the 'hidden text box'
purpose? The subform already has a refUnitId field in this scenario and it
seems redundant.

Yes, it would be redundant if the subform already has a text
box bound to the refUnitId field. The point is that there
is no need to display it because the main form has the same
value in its unitID text box and, if you display it, users
might be tempted to edit it.

 

[vbnetman via AccessMonster.com]

OK, I understand. Now the combo box. Have a user make a parts selection with
this and then code something like refPartsID.value = combo box.value to
populate to fits table?

vb

 

[Marshall Barton]

If the subform is bound to the fits table and the combo box
is bound to the refPartNo field, there is no need for any
code. The (sub)form will update/save the value
automatically.

Make sure the combo box's Row Source is a query on TblParts:

SELECT PartNo, [part name]
FROM TblParts
ORDER BY [part name]

set the combo box's ColumnCount to 2 and BoundColumn to 1

 

[John W. Vinson]

OK, I understand. Now the combo box. Have a user make a parts selection with
this and then code something like refPartsID.value = combo box.value to
populate to fits table?

You don't have to write code for everything.

In fact for an application like this you don't need to write *any code at
all*.

A combo has several properties: it gets data from its "Row Source", usually a
query; it has a "Bound Column", one privileged field from that query; and it
has a "Control Source", the table field into which the bound column will be
inserted when the user makes a selection. Just put the combo on the form, set
the properties, and you're done.

 

[vbnetman via AccessMonster.com]

JV,
Thank you for your input, simplifying to combo. Also, I've got this method to
work but I'm not a big fan of the continuous form.It just does not seem as
'clean'. I prefer the listbox look. So, I'm still looking for a unitID combo
box on top, bottom left a listbox that shows the parts assigned or associated
with that unit and bottom right a listbox that shows a list of parts that a
user can choose from to assign to that unit.

I’ve got the parts assignment listbox (fits table) setup to show only those
parts associated with the unit selected. The parts available listbox shows
all parts. So going back, will an append query do it or a double click event
for the listbox?

vb

 

[vbnetman via AccessMonster.com]

Guys, i think I have this resolved. I went with 2 listboxes and a combo box.
A user selects a unit from the combo and parts from a listbox which then
populates the fits table, the second listbox. I'm still working out a couple
kinks namely requerying the list box at the right time to have it updated
correctly...probably another thread. thanx a bunch for your help!

vb

JV,
Thank you for your input, simplifying to combo. Also, I've got this method to
work but I'm not a big fan of the continuous form.It just does not seem as
'clean'. I prefer the listbox look. So, I'm still looking for a unitID combo
box on top, bottom left a listbox that shows the parts assigned or associated
with that unit and bottom right a listbox that shows a list of parts that a
user can choose from to assign to that unit.

I’ve got the parts assignment listbox (fits table) setup to show only those
parts associated with the unit selected. The parts available listbox shows
all parts. So going back, will an append query do it or a double click event
for the listbox?

vb
[quoted text clipped - 10 lines]

inserted when the user makes a selection. Just put the combo on the form, set
the properties, and you're done.