Update two tables at same time

  • Thread starter Thread starter Ultraviolet47
  • Start date Start date
U

Ultraviolet47

Hi

I have code that extends the date in the field by one year:
Me.[DateExpire] = DateAdd("yyyy", 1, Me.[DateExpire])

I would also like it to update the corresponding record in another
table (there is a payee and a receiver, I would like it to update not
just the receiver field but the payee one as well) I have tried various
references to the table, but just debugs.

Does anyone know the code to acheive this? The table I want it to
update to is Tbl_Payee, has same field name, and using Access 03.

Thanks, much appeciated.
 
If the payment and receipt date are the same, or one can be derived from the
other by some constant (e.g. if the receipt date is always n days after the
payment date) in the related rows of each table then you should store the
date only once as one can always be derived from the other on the fly.

If the dates are the same, but are required in both tables as they form part
of a composite foreign key in one referencing a composite primary key in the
other, then updating them both simultaneously is simply a matter of enforcing
cascade updates in the relationship.

It this latter scenario is not the case, without knowing just what the
tables are modelling its difficult to say which of the two tables would be
the one in which to retain the payment column, or indeed whether a third
table is required to model the relationship between the other two. This
would be the case for instance if transactions are being recorded in which
there is a many-to-many relationship between payees and receivers i.e. where
each payee might be making payments to one or more receivers and each
receiver might be receiving payments from one or more payees. In this
scenario a Payments table with PayeeID and ReceiverID columns referencing the
primary keys of the Payees and Receivers tables would model the relationship,
and would also have columns for the date etc., i.e. for each attribute of the
Payments. In this scenario you might well have a single 'People' table for
both payees and receivers with the PayeeID and ReceiverID columns of Payments
both referencing its primary key; a payee could thus also be a receiver and
vice versa. Your business model might be completely different of course, but
the problem you are faced with does tend to suggest that your database might
not be modelling the reality correctly, and its underlying logical model
might need some consideration.

Ken Sheridan
Stafford, England
 
Back
Top