Using a combo box to change a foreign key relationship...

[John Clark]

Help?

I have a form/subform that displays information from two different
tables based on a foreign key relationship between the two tables. What
I would like to do is present the user with a combo box that would allow
them to change the object referenced by the foreign key. The foreign
key is a long integer that references an autonumber primary key on the
other table, but I would like to present the user with a list of names
of the other object (the names are also required to be unique). When
the user changes the value displayed in the combo box, the table with
the foreign key should be updated to point at the newly selected row in
the primary key table. Also - the user should have the ability to
create a new row in the primary key table by selecting a '<NEW>' list
item in the combo box (or something similar).

I would think this would be a fairly common operation, but the two
Access books I have don't address this type of thing using forms. Can
anyone provide pointers on how to set this up?

I have a similar problem in a more complex scenario as well. In this
scenario I have two tables (TableA and TableB) which have a many-to-many
relationship between them. The many-to-many relationship is resolved
with a junction table that has it's own primary key (Table AB). The
junction table also contains a foreign key to a third table (Table C).
I would like to setup a a form that would present a (non-updateable)
record of TableA, and a (non-updateable) list of the rows from TableB
that the current record is related to. Then when the user selects a row
in the TableB list, a subform would display the details of Table C that
are referenced from that particular junction table row. Additionally,
if more than one instance of the junction table references that same row
in Table C, the list should show (by multiple selection) the TableB
instances in the list that are related to the current Table A instance
and this instance of Table C. Then (only to complicate matters further)
If the user updates the subform, the program needs to figure out based
on the selection in the TableB list whether the original Table C record
can be updated (based on whether all instances of Table B that reference
this record in Table C are selected in the list), or if a new Table C
record needs to be created and related to the selected members of the
TableC list.

Hopefully I have explained this clearly - does anyone have advice on how
to create this functionality within an Access Forms application?

Thanks,
-jdc

 

[SteveM]

To address the first part of your question:
Do you want a combo box that allows you to change the record displayed in
the parent form or do you want to change the subform's link to another
foreign key in the primary table?

Steve

 

[John Clark]

Steve:

I want the combo box to change the record that is related to the record
shown on the parent form. So if ParentInstanceA is being displayed and
currently stores a foreign key to ChildInstanceA, the combo box would
provide a list of ChildInstances with ChildInstanceA being currently
selected. If the user should select ChildInstanceB from the combo box,
that should alter the value stored in ParentInstanceA's foreign key to
then point at ChildInstanceB.

Does that help explain?

-jdc

To address the first part of your question:
Do you want a combo box that allows you to change the record
displayed in the parent form or do you want to change the subform's
link to another foreign key in the primary table?

Steve

--


John Clark
(e-mail address removed)
(o) 718.802.1044
(c) 917.331.8999

 

[John Clark]

The parent form is currently displaying ParentInstanceA, which is
currently related (through the foreign key) to ChildInstanceA. The
combo box should display the list of ChildInstances with ChildInstanceA
selected. If the user changes the combobox selection to ChildInstanceB,
ParentInstanceA should be updated so the foreignkey field references
ChildInstanceB. Hopefully, the subform will refresh, now displaying the
contents of ChildInstanceB.

Does that help clarify?

-jdc

 

[SteveM]

The simplest solution is probably to display the foreign key field on your
form. Set the control type to Combo and set the rowsource to the appropriate
options.
In the AfterUpdate event of the Combo use Me.Refresh or requery the subform.

Steve