Lookup and Replace value in multiple tables

  • Thread starter Thread starter H J
  • Start date Start date
H

H J

I have an existing database that uses company name as the primary key in
multiple tables (I realize that this is not good practice, but at the time
the database was created, most of the company names were actually numbers).

A need has now arisen whereby the company name needs to change, if the user
simply enters a new company name, access reads this as a new record, and so
all history is lost. Is there a way to use query/code so that the user can
select the existing name from a combo box, enter a new name in a second
field and then click a button that will replace the name chosen from the
combo box with the name entered in the text field across all tables?

Thanks.
 
You can do this relatively easily if you have relationships created between
each of the tables that contain the [Company Name] field as either the PK or
an FK value, and if you have cascade updates checked.

1. Make a copy of your database so you can restore from backup if this does
not work the way you want.

2. Create the relationships (make sure you have checked every table because
you don't want to have to go back and fix it in those tables that you didn't
have the relationships defined in originally), and make sure you check the
Enforce Referential Integrity and Cascade Update Related Fields

3. Now, when you go into the table where the [Company Name] is the PK, and
edit that value, the new value should cascade throughout the entire database.
You could create a form for this, that would prevent you from chaning the
value to one that already exists, but would not automatically create a new
company if it didn't find the name you entered.

Once you have done this, I would open up two copies of your application, one
linked to the old data, and another to the modified data. Review a bunch of
records to ensure that they match. Once you have done that, you can start
using the new data.
 
Thank you.


Dale Fye said:
You can do this relatively easily if you have relationships created
between
each of the tables that contain the [Company Name] field as either the PK
or
an FK value, and if you have cascade updates checked.

1. Make a copy of your database so you can restore from backup if this
does
not work the way you want.

2. Create the relationships (make sure you have checked every table
because
you don't want to have to go back and fix it in those tables that you
didn't
have the relationships defined in originally), and make sure you check the
Enforce Referential Integrity and Cascade Update Related Fields

3. Now, when you go into the table where the [Company Name] is the PK,
and
edit that value, the new value should cascade throughout the entire
database.
You could create a form for this, that would prevent you from chaning the
value to one that already exists, but would not automatically create a new
company if it didn't find the name you entered.

Once you have done this, I would open up two copies of your application,
one
linked to the old data, and another to the modified data. Review a bunch
of
records to ensure that they match. Once you have done that, you can start
using the new data.

----
HTH
Dale



H J said:
I have an existing database that uses company name as the primary key in
multiple tables (I realize that this is not good practice, but at the
time
the database was created, most of the company names were actually
numbers).

A need has now arisen whereby the company name needs to change, if the
user
simply enters a new company name, access reads this as a new record, and
so
all history is lost. Is there a way to use query/code so that the user
can
select the existing name from a combo box, enter a new name in a second
field and then click a button that will replace the name chosen from the
combo box with the name entered in the text field across all tables?

Thanks.
Click to expand...
Click to expand...
 
Back
Top