Amending records across 4 tables

[TonyWilliams via AccessMonster.com]

I have a table (tblcompany) that contains company information. I have 3 other
tables that include various data streams but also include a company name
(txtcompany) which is linked to the company name in tblcompany.

I want to create a form that deals with a situation where a company changes
its name. I know that I can create an update query for each table to change
the name in each record where it appears but I want to create a user process.

What I had in mind was an unbound form that had a combo box based on
tblcompany from which the user could select the current company name. Then
another unbound control where they input the new company name. I would then
have a command button that would run code which would run through all the
tables, find records where the company name appears and change it.

Could someone point me in the right direction as to how the code would be
constructed?
Thanks
Tony

 

[Brendan Reynolds]

I have a table (tblcompany) that contains company information. I have 3
other
tables that include various data streams but also include a company name
(txtcompany) which is linked to the company name in tblcompany.

I want to create a form that deals with a situation where a company
changes
its name. I know that I can create an update query for each table to
change
the name in each record where it appears but I want to create a user
process.

What I had in mind was an unbound form that had a combo box based on
tblcompany from which the user could select the current company name. Then
another unbound control where they input the new company name. I would
then
have a command button that would run code which would run through all the
tables, find records where the company name appears and change it.

Could someone point me in the right direction as to how the code would be
constructed?
Thanks
Tony

Well, if you were going to do it that way, the code would look something
like this ...

CurrentDb.Execute "UPDATE table2 SET CompanyName = '" & NewCompanyControl &
"' WHERE CompanyName = '" & OldCompanyControl & "'"
CurrentDb.Execute "UPDATE table3 SET CompanyName = '" & NewCompanyControl &
"' WHERE CompanyName = '" & OldCompanyControl & "'"
CurrentDb.Execute "UPDATE table4 SET CompanyName = '" & NewCompanyControl &
"' WHERE CompanyName = '" & OldCompanyControl & "'"

Where "table2", "table3" and "table4" are the names of the tables to be
updated, "CompanyName" is the name of the field, "NewCompanyControl" is the
name of the control containing the new name, and "OldCompanyControl" is the
name of the control containing the old company name.

Alternatively, if you have relationships defined between the tables, with
referential integrity enforced and the 'Cascade Updates' option selected,
then none of this will be necessary. If CompanyName is the primary key in
the Companies table and a foreign key in the related tables, then changing
the value of the field in the Companies field will automatically change it
in the related tables.

All of this begs the question though, how are you going to handle different
companies with the same name? There are rather a lot of them. Just out of
curiosity, I tried a Google search for "Murphy Motors" and came up with ten
of them.

Another alternative is to use a numeric id as the primary key of the
Companies table. The numeric id would be the foreign key in the related
tables, with queries and combo boxes being used as necessary to display the
name rather than the id. For example, "table2" might contain fields
"somedata, companyid". To display the company name rather than the numeric
id you would join the Companies table and "table2" in a query like so:
"SELECT somedata, companyname FROM Companies INNER JOIN table2 ON
Companies.companyid = table2.companyid". Then the company name only ever has
to be changed in the Companies table. That would be my recommendation.

 

[TonyWilliams via AccessMonster.com]

Thanks Brendan that gives me something to really think about. It may take me
a while but I'll be back if I need any more guidance
Thanks again
Tony

I have a table (tblcompany) that contains company information. I have 3
other

[quoted text clipped - 19 lines]

Thanks
Tony

Well, if you were going to do it that way, the code would look something
like this ...

CurrentDb.Execute "UPDATE table2 SET CompanyName = '" & NewCompanyControl &
"' WHERE CompanyName = '" & OldCompanyControl & "'"
CurrentDb.Execute "UPDATE table3 SET CompanyName = '" & NewCompanyControl &
"' WHERE CompanyName = '" & OldCompanyControl & "'"
CurrentDb.Execute "UPDATE table4 SET CompanyName = '" & NewCompanyControl &
"' WHERE CompanyName = '" & OldCompanyControl & "'"

Where "table2", "table3" and "table4" are the names of the tables to be
updated, "CompanyName" is the name of the field, "NewCompanyControl" is the
name of the control containing the new name, and "OldCompanyControl" is the
name of the control containing the old company name.

Alternatively, if you have relationships defined between the tables, with
referential integrity enforced and the 'Cascade Updates' option selected,
then none of this will be necessary. If CompanyName is the primary key in
the Companies table and a foreign key in the related tables, then changing
the value of the field in the Companies field will automatically change it
in the related tables.

All of this begs the question though, how are you going to handle different
companies with the same name? There are rather a lot of them. Just out of
curiosity, I tried a Google search for "Murphy Motors" and came up with ten
of them.

Another alternative is to use a numeric id as the primary key of the
Companies table. The numeric id would be the foreign key in the related
tables, with queries and combo boxes being used as necessary to display the
name rather than the id. For example, "table2" might contain fields
"somedata, companyid". To display the company name rather than the numeric
id you would join the Companies table and "table2" in a query like so:
"SELECT somedata, companyname FROM Companies INNER JOIN table2 ON
Companies.companyid = table2.companyid". Then the company name only ever has
to be changed in the Companies table. That would be my recommendation.