Many-to-many rels: Need an updating approach

[GaryS]

I'm new to Access but not new to data modeling--in fact, I
teach the latter! But I'd like some suggestions from the
MVPs on how to handle updating n-n relationships using
Forms.

For starters, I have an app now where I've got tA and tB
with a n-n relationship trelAB between them.

trelAB consists only of tA.id and tB.id, which are
Autonumber fields in tA and tB respectively. Those
foreign keys in trelAB are of type Number.

Let's say that tA is the primary table of interest to the
user. I have a Form frmA based on tA, which has several
fields.

Right now, I'm using a Subform to display the related tB
data. The Subform's recordsource is a query that joins tB
with trelAB, so that the parent & child connection between
the main and subforms is simply tA.id.

This arrangement works not only for display but seemingly
also for updates. IOW, if I do a NewRecord in the
Datacontrol selector in the subform display, Access seems
to magically handle the addition properly--that is, a new
row in trelAB and a new row (maybe) in tB.

Question: Am I just lucky in happening upon this
approach, or is subform the "way to go" for n-n rel.
updates?

I would appreciate any light you all could shed on this
matter before I propagate that approach to other
situations in the application!

Gary

 

[Douglas J. Steele]

Subforms are certainly one of the most common approaches to handling
many-to-many relationships.

 

[John Vinson]

Question: Am I just lucky in happening upon this
approach, or is subform the "way to go" for n-n rel.
updates?

You're doing it just fine.

The Subform can be based on a query joining trelAB to tB, or just
purely on trelAB using a Combo Box bound to the linking field; either
way can work, and either way can be preferable at times.

 

[Sandra Daigle]

Hi Gary,

There are two methods that I commonly use for displaying data with M-M
relationships. The first is the subform method - as you've already
described.

The second, which I use when I want to add records in a group (ie multiple
students to a class), involves a pair of MultiSelect list boxes with some
command buttons that indicate whether to add or remove data from the
junction table.

You can see examples of both methods in the SelectRecords sample on
http://www.daiglenet.com/msaccess.htm.

 

[GaryS]

Thanks, Doug. Now I can go ahead and think through
exactly why they work. I'm guessing that whatever I type
into the subform updates its underlying query which in
turn updates the under-underlying tables. Boggles the
mind, it does!

 

[GaryS]

Thanks, Sandra, for the reply. Someday I'm going to
summon the courage to attempt dual multi-selects, as you
illustrate, eventually with the capability of adding new
items in one of them.

Gary

-----Original Message-----
Hi Gary,

There are two methods that I commonly use for displaying data with M-M
relationships. The first is the subform method - as you've already
described.

The second, which I use when I want to add records in a group (ie multiple
students to a class), involves a pair of MultiSelect list boxes with some
command buttons that indicate whether to add or remove data from the
junction table.

You can see examples of both methods in the SelectRecords sample on
http://www.daiglenet.com/msaccess.htm.


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

I'm new to Access but not new to data modeling--in fact, I
teach the latter! But I'd like some suggestions from the
MVPs on how to handle updating n-n relationships using
Forms.

For starters, I have an app now where I've got tA and tB
with a n-n relationship trelAB between them.

trelAB consists only of tA.id and tB.id, which are
Autonumber fields in tA and tB respectively. Those
foreign keys in trelAB are of type Number.

Let's say that tA is the primary table of interest to the
user. I have a Form frmA based on tA, which has several
fields.

Right now, I'm using a Subform to display the related tB
data. The Subform's recordsource is a query that joins tB
with trelAB, so that the parent & child connection between
the main and subforms is simply tA.id.

This arrangement works not only for display but seemingly
also for updates. IOW, if I do a NewRecord in the
Datacontrol selector in the subform display, Access seems
to magically handle the addition properly--that is, a new
row in trelAB and a new row (maybe) in tB.

Question: Am I just lucky in happening upon this
approach, or is subform the "way to go" for n-n rel.
updates?

I would appreciate any light you all could shed on this
matter before I propagate that approach to other
situations in the application!

Gary

.

 

[GaryS]

John,

The Subform can be based on a query joining trelAB to tB

That's the one I'm now using, I think; but . . .

purely on trelAB using a Combo Box bound to the linking

field

. . . is what I'm having trouble understanding. I mean,
trelAB consists of just 2 non-intelligent ID's, so how
could any control based purely on that table display
anything usable? And which linking field are you
referring to--tA.id or tB.id? Am I confusing RecordSource
with ControlSource, or something?

Gary

 

[John Vinson]

field

. . . is what I'm having trouble understanding. I mean,
trelAB consists of just 2 non-intelligent ID's, so how
could any control based purely on that table display
anything usable? And which linking field are you
referring to--tA.id or tB.id? Am I confusing RecordSource
with ControlSource, or something?

You can create a Combo Box control using tB as its Row Source - where
the Combo gets its data; typically one would have the following
properties:

Row Source - a query such as

SELECT tB.ID, tB.CompanyName, <maybe other fields>
FROM tB Order By CompanyName;

Control Source - trelAB.tBID <the foreign key linking to tB, whatever
its name is); this is where the combo will store the selected value

Bound Column - 1, so that it stores tB.ID into trelAB.tBID

Column Widths - 0;1.25 so that the numeric ID is concealed from human
view (zero width) and the company name is displayed instead

 

[GaryS]

Thanks, John.

I'm not quite ready to say I understand this yet, so:

1. How does tAid come into the picture? When I select a particular
tBid in the Combo box, some elf has to associate an Aid with that Bid
to create a new record in trelAB. How that happens is not apparent to
me.

2. I can see how your suggestion would work for associating an
EXISTING record in tB with a tA record. But can I also type a NEW
(i.e., not in list) value into the tB.CompanyName column in the Combo
box and automatically have the new tB record created as well as a new
record in trelAB? That would be great magic if it worked!

Gary

 

[John Vinson]

Thanks, John.

I'm not quite ready to say I understand this yet, so:

1. How does tAid come into the picture? When I select a particular
tBid in the Combo box, some elf has to associate an Aid with that Bid
to create a new record in trelAB. How that happens is not apparent to
me.

tAID would be the Child Link Field, and ID would be the Master Link
Field - the "many" and the "one" side linking fields respectively. The
Master/Child Link Field is your "elf" and works just as you describe.

2. I can see how your suggestion would work for associating an
EXISTING record in tB with a tA record. But can I also type a NEW
(i.e., not in list) value into the tB.CompanyName column in the Combo
box and automatically have the new tB record created as well as a new
record in trelAB? That would be great magic if it worked!

For this you need to use some VBA code in the Combo Box's NotInList
event. For sample code see http://www.mvps.org/access and search for
"not in list". The details differ depending on just what you want to
do - if it's just a company name in tB, with no other data, you can
simply add what the user typed; but more often you'll want the
NotInList event to pop up a form to enter additional data into tB.

 

[Guest]

John,

tAID would be the Child Link Field, and ID would be the Master Link
Field - the "many" and the "one" side linking fields respectively. The
Master/Child Link Field is your "elf" and works just as

you describe.

From the outset in this thread I was thinking of a Combo
Box as an ALTERNATIVE to the Subform rather, as I think
you're saying, a combox as part of a subform (with the
master/child link fields). I guess, IOW, using a combox
in place of the datasheet view I now have in the
subform . . . right?

Gary

-----Original Message-----

 

[John Vinson]

From the outset in this thread I was thinking of a Combo
Box as an ALTERNATIVE to the Subform rather, as I think
you're saying, a combox as part of a subform (with the
master/child link fields). I guess, IOW, using a combox
in place of the datasheet view I now have in the
subform . . . right?

Exactly. Use a Continuous Subform with a combo box on it, bound to the
tBID field. There may well be other controls on the subform as well.