updating same data stored in 2 different tables

[Guest]

I have a couple of fields that I store in 2 different tables. Lets say amount
billed and amount paid are stored in the Pledge table and the Pay Schedule
table. How can I link these two tables so when I make a change in the pledge
table it is reflect in the pay schedule table and I don't have to do double
work.???
Thanks

 

[John Vinson]

I have a couple of fields that I store in 2 different tables.

Well... that's almost by definition bad design. Storing data
redundantly is exactly what relational databases are designed to
*prevent*!

Lets say amount
billed and amount paid are stored in the Pledge table and the Pay Schedule
table.

If that's a realistic description then these are NOT the same value.
Sure, *usually* the amount paid will equal the amount pledged; but the
person may change their mind and pay less, or pay more.

How can I link these two tables so when I make a change in the pledge
table it is reflect in the pay schedule table and I don't have to do double
work.???

If it's really the same value then it should be stored in only one
place, and you should use a query to link the two tables to display it
in conjunction with the data from the other table. If it's different,
then it should be entered separately. You can set the Default property
of the PaidAmount textbox (*not* a table field) to a DLookUp
expression so that it starts out the same:

Me![Pay Amount].DefaultValue = DLookUp("[PledgeAmount]", "[Pledge]", _
"[DonorID] = " & [DonorID])

in the AfterUpdate event of the Donor control.

John W. Vinson[MVP]