Changed relationship; records no longer linked

[CW]

I have a Corporates table holding details of company customers.
I have a CorpContacts table holding details of our contact persons at those
companies.
Originally I had a relationship (one to many) between Corporates.CorpName
and CorpContacts.CorpName.
I had a Corporates form which contained a CorpContacts subform displaying
the contact persons' details. All working fine.
But if we updated the name of the company i.e. Corporates.CorpName, the link
was lost and the contact persons were no longer displayed.
So I introduced new fields in each table ("CorpID"), i.e. something that
would not be subject to change. I reset the relationship to use those fields
instead of CorpName. I added the fields to the two forms. Now the
CorpContacts form doesn't even display any names at all!!
Where have I gone wrong???
Many thanks
CW

 

[Daryl S]

CW -

If you forms are working except that the CorpName is not showing up on the
subform, then just add a text field with a DLOOKUP of the CorpName based on
the CorpID. (Look up help on DLOOKUP). You do not need to store this field
in the CorpContacts table.

If there is nothing showing up on the subform, then I suspect you need to
reset the child/parent link to use the CorpID field instead of the CorpName
field.

 

[CW]

Daryl -
It is the second problem i.e. nothing showing. I did already change the
child/parent link. Is the subform empty because no link existed between the
respective IDs when the records were created (because at that time it was
using CorpName to set the link) , and now that link has gone it doesn't know
which ones relate to which?
Thanks
CW

 

[Daryl S]

CW -

Are the two new fields CorpID both fully populated in both tables? Is the
field type Autonumber in the Corporates table, and Long Integer in the
CorpContacts table? Did you update the CorpID field in the CorpContacts
table with the correct CorpID based on the CorpName in the Corporates table?

It doesn't matter if the data is entered after the form is built - if the
data is is in the tables, and the formats are correct, then the data should
show up. If you are still having problems, give us a sample record from
each table that should be linked.

 

[CW]

Daryl -
Thanks for those suggestions - it's a Yes to each one, already done.
Meantime, I have tried deleting the existing CorpContacts subform and adding
a completely new one. This works perfectly when I change the CorpName or do
anything else. So obviously there was something lurking under the surface of
the old one, that hadn't got changed. It'll take a while to refine the design
of the new one to the way it should look (i.e. like the old one) but at least
I'll have it working properly.
Many thanks for your help
CW

 

[Daryl S]

Glad it is working for you!

--
Daryl S

Daryl -
Thanks for those suggestions - it's a Yes to each one, already done.
Meantime, I have tried deleting the existing CorpContacts subform and adding
a completely new one. This works perfectly when I change the CorpName or do
anything else. So obviously there was something lurking under the surface of
the old one, that hadn't got changed. It'll take a while to refine the design
of the new one to the way it should look (i.e. like the old one) but at least
I'll have it working properly.
Many thanks for your help
CW