Link tables using Data Type: Lookup doesn't auto update

[Guest]

OK, I'm completely new to Access so I'm probably doing this all wrong. I
just entered all of my data, and then when I went back to update some product
names, the tables that I thought I linked didn't update, in fact the records
disappeared. Basically, I have "Companies" (stored in one table" with
"Agreements" (another table) for certain "Products" (Products). I thought
that I could make the Agreement table pull the CompanyName and ProductName
from their respective tables. I did this in the Agreement table design, by
selecting "Lookup" from the possible DataTypes and using the wizard to get
data from the other tables. When I'm entering information on the Product
table, it allows me to hit a little plus sign to drop down the info about
Agreements for that Product, and select the CompanyName from a drop down. So
I thought everything was working. But when I went back and corrected a
couple of Product names (thinking it would update the agreements that show up
on the Agreements table and in the drop down) the agreements simply
disappeared. What would have been the proper way to set this up? Sorry if
this is a garbled question, I'm very new to Access and am not too familiar
with proper terminology. I can elaborate if needed. Thanks!

 

[Dirk Goldgar]

OK, I'm completely new to Access so I'm probably doing this all
wrong. I just entered all of my data, and then when I went back to
update some product names, the tables that I thought I linked didn't
update, in fact the records disappeared. Basically, I have
"Companies" (stored in one table" with "Agreements" (another table)
for certain "Products" (Products). I thought that I could make the
Agreement table pull the CompanyName and ProductName from their
respective tables. I did this in the Agreement table design, by
selecting "Lookup" from the possible DataTypes and using the wizard
to get data from the other tables. When I'm entering information on
the Product table, it allows me to hit a little plus sign to drop
down the info about Agreements for that Product, and select the
CompanyName from a drop down. So I thought everything was working.
But when I went back and corrected a couple of Product names
(thinking it would update the agreements that show up on the
Agreements table and in the drop down) the agreements simply
disappeared. What would have been the proper way to set this up?
Sorry if this is a garbled question, I'm very new to Access and am
not too familiar with proper terminology. I can elaborate if needed.
Thanks!

This is what I think has happened, based on your description. I think
your Agreements table and your Companies table have a logical
relationship (many-to-one, with potentially many Agreements to any one
Company). Your CompanyName lookup field in the Agreements table
reflects this relationship. However, you haven't told Access that this
relationship is to be enforced, and to "cascade updates" between the
Companies table and any related records in Agreements. So when you
change the name of a company in the Companies table, the "lookup link"
between that company and the related agreements simply disappears.

Fortunately, the agreement records haven't actually disappeared;
they're just linked to a company name that no longer exists. If you
open the Agreements table, you should be able to find them with the old
company names, and fix them.

After you've done that, you'll want to open the Relationships window,
click the Show All Relationships button (or the menu items
Relationships -> Show All) to make sure the relationship is displayed in
the window, and edit the relationship. You do that by right-clicking
the join line between the tables and choosing "Edit Relationship...".
In the Edit relationship dialog, check the box for Enforce referential
Integrity, and then for Cascade Update Related Fields. Click OK to save
the change, and close the Relationships window.

That should take care of the problem with this pair of tables. You
probably need to do the same thing with the Agreeements<->Products
relationship.

When you ask, "What would have been the proper way to set this up?", you
open a can of worms. Most developers avoid using lookup fields at all
because of complications they introduce, and because they are geared
toward editing data directly in the tables, rather than by using forms
(where the same sort of lookup functionality can be implemented with
combo boxes). Forms give you enormously more control over how your data
is displayed and manipulated. You don't need to use lookup fields, by
the way, to get the nested subdatasheets such as you have there with
Companies and Agreements -- it's the relationship that makes the
subdatasheet available, not the lookup field itself.

However, if you're just getting started with Access, and if the data
displayed by your lookup fields is the same as the data actually stored
in the field -- that is, you don't *store* CompanyID but s*show*
CompanyName -- then I don't think there's any real problem with what
you're doing, so long as you define and enforce the relationships as I
described above.