updating all rows within same field that have same values from separate table

[justin@STI]

I have 2 tables and one form. the one table gets modified by the for
and the other does not. both tables have a field for username. But th
table that does not get modified ( Yet ) needs to update the usernam
if it is changed in the form. So once the username in the form i
changed and table 1 gets updated I need a way to also update table 2'
username. BUT this second table does not have unique usernames. it is
log table that shows activity of when someone logs into a certai
application that is put in manually yet I've been told to find a way t
update all fields of the specific username in table2 if table1 i
modified.

I have not played with VB much in about 4 years so I haven't th
foggiest how to attempt to update 2 tables with one value yet with 2n
table all the fields with the previous username have to be changed t
the new username.

this may be hard to understand but I dont know any other way to explai
it

 

[Marshall Barton]

I have 2 tables and one form. the one table gets modified by the form
and the other does not. both tables have a field for username. But the
table that does not get modified ( Yet ) needs to update the username
if it is changed in the form. So once the username in the form is
changed and table 1 gets updated I need a way to also update table 2's
username. BUT this second table does not have unique usernames. it is a
log table that shows activity of when someone logs into a certain
application that is put in manually yet I've been told to find a way to
update all fields of the specific username in table2 if table1 is
modified.

I have not played with VB much in about 4 years so I haven't the
foggiest how to attempt to update 2 tables with one value yet with 2nd
table all the fields with the previous username have to be changed to
the new username.

Your question reeks of an inadequate understanding of
relational database normal form basics. I'll try to point
you in the right directions, but you need to check out the
subject in Help or a book.

A fundamental concept of normalization is that you should
never duplicate data in a relational database. This means
you should adopt either one of two approaches to when
structuring your data into tables and their fields. One
approach is to use surrogate primary keys (usually a number)
so that you would never have those names in two tables.
Instead of your second table having a field with the name,
it would have a foreign key field containing the primary key
of the related record in the first table and thus never need
to be updated as your current structure requires.

The other approach is to use natural primary keys. In this
case, as long as you are using the name field as the first
table's primary key (a poor choice since lots of people can
have the same name), then the second table's foreign key
would actually contain the name. So you run into the point
of your question - how to keep them in sync when someone
changes their name. Fortunate;y, Access/Jet provide a
feature that makes sure you don't break the relationship
between the two tables. When you use the Relationships
window to specify the link between the primary key in the
first table and the foreign key in the second table, one of
the options provided by Enforce Referential Integrity is to
select Cascade Update so that Access/Jet will update the
foreign key field (i.e. the names) in the second table
automatically.

Some database systems do not provide a Cascade Update
feature, in which case it's up to the programmer to make
sure the data stays in sync, but bugs or a system crash can
pull the rug out from under the best of plans. The typical
technique is to execute an Update query to change the
foreign key of the appropriate records in the second table.
Using DAO, the code might look something like:

strSQL = "UPDATE table2 SET namefield =""" & newname _
& """ WHERE namefield =""" & oldname & """"
db.Execute strSQL, dbFailOnError

but you will have to determine when the name in the first
table is changed so you can be certain the update actually
happens. A major issue with doing it yourself, without the
benefits of referential integrity's Cascade Updates, is that
you can never allow a user see (or at least edit) the data
in either table using sheet view. You can only permit them
to see/edit the names using a form you've designed to trap
name changes so you can execute code like the above.

 

[John Vinson]

I have not played with VB much in about 4 years so I haven't the
foggiest how to attempt to update 2 tables with one value yet with 2nd
table all the fields with the previous username have to be changed to
the new username.

If Username is the Primary Key of the first table, you can set up
relationships to automatically update the second table.

Go into table design view, and open the Relationships window on the
toolbar (looks like three datasheets connected by lines). Add the two
tables and join them on Username.

Select the "Enforce Relational Integrity" and "Cascade Updates"
checkboxes. If (and I suspect this is NOT the case!!!) you want to
delete all records from the second table if a user record is deleted,
also check "Cascade Deletes".

Now any change to a Username in the first table will automatically
cascade to the second table.