Subform woes...any suggestions?

[oich]

Hello,

I posted this problem elsewhere without success and I'd really appreciate
your collective help!

My database is for displaying words in a foreign language and the table
structure is as follows:

tblWords (contains all the words):

WordID (Primary Key)
OriginalLanguageWord
PhoneticReading
English


tblRelateTypes (contains the type of cross-references than can exist between
the words):

RelateTypeID (Primary Key)
RelateDescription


tblWordRelates (contains the cross-references between the words):

WordRelatesID (Primary Key)
OriginalWordID (tblWords WordID is foreign key)
RelateWordID (tblWords WordID is foreign key)
RelateTypeID (tblRelateTypes RelateTypeID is foreign key)


I want to have a subform on frmWords which displays the appropriate
WordRelates for each tblWords record. The problem I have is that I cannot
make the subform display the WordRelates data properly.

An example:

tblWords:
WordID: 18
OriginalLanguageWord: houfu
PhoneticReading: houfu
English: wealth, abundance

WordID: 85
OriginalLanguageWord: yutaka
PhoneticReading: yutaka
English: abundance


tblRelateTypes:
RelateTypeID: 1
RelateDescription: Same meaning


tblWordRelates:
WordRelatesID: 1
OriginalWordID: 18
RelateWordID: 85
RelateTypeID: 1


When I navigate to Record 18 in frmForms, I want the subform to display data
about record 85 (the related record). However, when I navigate to record 18,
the subform displays the OriginalLanguageWord and PhoneticReading fields of
record 18 again, NOT of record 85, the related record.

With all the methods (queries, wizards, relationships window, etc.) I can
find no way to have this happen - Access has decided any time I reference
OriginalLanguageWord and PhoneticReading, it's always that of the original
word, not the related one.

Any ideas as to a solution would be most appreciated.

 

[Douglas J. Steele]

The recordsource for the subform needs to be a query joining tblWordRelates
and tblWords linking RelateWordID to WordID

The LinkMasterField for the subform control needs to point to WordID

The LinkChildField for the subform control needs to point to RelateWordID

 

[oich]

Hello,

Thanks for the swift reply. I'm afraid I'm going to have to ask you to go a
bit more basic for me to understand...

I tried making a query as you suggested. The join between the tables and
links between the fields already exists when I add them to the query design.
I tried simply adding the fields I need to the query and setting the
subform's link fields as you wrote below but the result was that the query
produced empty fields. I tried using the wizard, and again any use of the
WordID field produced data relating to the original word. I assume a Select
Query is the right starting point. A quick step-by-step as to how to create
the query would be most appreciated.

Thanks for your time up to now.

 

[Douglas J. Steele]

Which fields were joined when you created the query?

If there was a line between OriginalWordID in tblWordRelates and WordID in
tblWords, delete it. The only line you want should be between RelateWordID
in tblWordRelates and WordID in tblWords.

 

[oich via AccessMonster.com]

Thanks for your reply. The field tblWords WordID was a foreign key to both
OriginalWordID and RelateWordID in tblWordRelates.

I deleted the relationship between WordID and OriginalWordID as you suggested
and recreated the query (simple Select Query with fields RelateWordID, WordID,
OriginalLanguageWord, PhoneticReading and RelateTypeDescription). I set the
child and master link fields as you suggested above. However, some reason the
subform still displays the wrong data. For the data mentioned above, when I
nagivate to record 18, it should show the RelateWordID 85 and the data for
the record 85 - "yutaka". Navigating to record 18 doesn't display anything in
the subform.

However, when I go to record 85 is displays something - the RelateWordID 85
and the data from the main form for record 85 again (!).

I've checked in tblWordRelates and the RelateWordID is correctly listed as 18
there. I have no idea how the query can take the correct RelateWordID from
the table then change it to the wrong one to display in the subform.

If I nagivate to record 85 then manually change the RelateWordID to 18 on the
subform the OriginalLanguageWord and other fields update to show the data
from record 18 (as I want it to always display). However, if I navigate to
another record then return, then the subform record has moved to record 18,
where it's displaying the data from record 18.

Quite a (depressing) mystery to me....any ideas about what's going on? It
seems like the RelateWordID field refuses to be different from the
OriginalWordID field.

Thanks again for your help.

Which fields were joined when you created the query?

If there was a line between OriginalWordID in tblWordRelates and WordID in
tblWords, delete it. The only line you want should be between RelateWordID
in tblWordRelates and WordID in tblWords.

Hello,

[quoted text clipped - 23 lines]