Hi All!

I have a field called txtAcctNum that may change occassionally. I have
a LOT of tables to deal with and am tired of setting up an update
function for each table. Is there a way I can programmatically check
all the tables for the txtAcctNum field and then change that field
from "1234" to "9876"?

Seems like a lot.

Any help is appreciated.

Thanks,
Laura

You might take a look a your (table) design first.

Having a LOT of tables with the same field doesn't sound very good.

If there is no way to alter that, consider creating a lookup table with values for txtAcctNum. Also create relationships between the lookup table and all the other tables. If you set the properties of the relationships right,you only have to change the value in the lookup table and the values in the other tables will change automatically.

Peter
http://access.xps350.com/

I didn't build the thing. I would've never done it that way.

Unfortunately I'm stuck with it until I can change it into a ADP or
SharePoint.

:-(

On Jun 10, 10:34*am, XPS350 <xps... (AT) gmail (DOT) com> wrote:

If all these tables are related using txtAcctNum and txtAcctNum can change
then it should not be used as a primary or foreign key.

Have you set up relationships between the Account table and all the other
tables? If so, you might try using the Cascade Update option. When you
change the value of the primary key in one table, it updates the foreign key
in all the other tables.

Another option would be to redesign your tables and use an autonumber as the
primary key and the foreign key in the other tables would be a number of type
Long. Then you store the account number in only one table (Accounts?) and
when you need to account number you link to that table.

Of course to do this you would be forced to do a series of update queries on
all your existing tables.

Your final choice would be to write a VBA procedure to do this. Assuming that
the field in all the tables is named txtAcctNum.

You would need to step through a recordset of all the tableNames
Step through the tables in the proper order
Check for the existence of a field named txtAcctNum
Construct an update query to change one value to another value.
One problem being that if you have set up relationships between the tables and
have set the relationship to be enforced,
you would need to create a new record in the Accounts table with the new value
for txtAcctNum and all the other values for the old record. That record would
have to exist PRIOR to attempting to update the other tables. Once you have
updated all the subordinate (child) tables, then you can delete the original
record.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 6/10/2011 10:29 AM, musicloverlch wrote: