Relationship Problems

[Gilberto Lawas via AccessMonster.com]

Hello,

I have a Many-To-Many relationship between [Main] & [RefMain], using a
junction table [JnxTbl]. It works fine. The problem I have is that I have
3 fields that point to [RefMain] for reference names.

But evey time that I make an entry, it doesn't add a record to the junction
table. I have to actually click on the expand button on the left of the
record, which opens a window to [JnxTbl] in order to get them to link.

Can someone tell me what I'm doing wrong?

For the fields in [Main] I put:

SELECT RefMain.ID AS xyz_ID_xyz, RefMain.RefName AS
xyz_DispExpr_xyz FROM RefMain;

When I look at the field, I get the drop down list, which makes me think
its working but no record in the [JnxTbl].

 

[John Vinson]

Hello,

I have a Many-To-Many relationship between [Main] & [RefMain], using a
junction table [JnxTbl]. It works fine. The problem I have is that I have
3 fields that point to [RefMain] for reference names.

Are these Lookup fields? If so... bear in mind that the Lookup table
field type is of VERY limited utility and a source of much confusion.

Your table *looks* like it has the reference name in it. However, it
does not: it has a concealed numeric ID. If you try to sort on the
reference name, or export it, or use it in another query, IT'S NOT
THERE - what's there is the ID.

Table datasheets are of *very* little use; they're good for design and
debugging but very little more. Forms (with Subforms, see below) are
much more effective for data entry and editing.

But evey time that I make an entry, it doesn't add a record to the junction
table. I have to actually click on the expand button on the left of the
record, which opens a window to [JnxTbl] in order to get them to link.

Can someone tell me what I'm doing wrong?

You're assuming that creating a record in Main *SHOULD* create a
record in JnxTbl. *It shouldn't*. A record in the junction table
should be created when - and ONLY WHEN - you are explicitly going to
link a record in Main to a record in RefMain.

The simplest way to do this is to use a Form based on Main, with a
Subform based on JnxTbl. On this Subform you would put a combo box
based on RefMain; the subform's master/child link field would be the
Main table's Primary Key and its corresponding foreign key. Thus when
you select a related record from the combo, a new JnxTbl record is
created (but only then).

Again... don't use table datasheets. They'll get you from A to B, or
(with some of these new features) all the way to D if you really
stretch. If you want to get any further, use a Form instead.

John W. Vinson[MVP]

 

[Gilberto Lawas via AccessMonster.com]

You hit it on the head. I just made a form. works great.

Thanx
G.Lawas