Lock child field when parent field is changed on condition (1 Viewer)

SOLVED: Lock child field when parent field is changed on condition

I am new to this forum and so far I have managed to find answers to all my questions for my access project.
But I got stuck. I tried various options but they did not work... :banghead:

I have 2 tables:
T_Product
T_Transaction
where the ProductID is the link.

And 1 query:
Q_Transaction
from which i get the value of the transaction

When i change the buying price in T_Product, depending on the date i changed this price, i would like the price in Q_Transaction to update or not... Because, obviously, last year's transactions have not had their value changed.

I am sure it is a very common topic, but for some reasons, i just cannot work it out.

Thank you kindly for your POVs on this.

Matt

you cannot update a field in a query - only in the underlying table for that query. If the query is editable and you manually change a value - you are actually updating the underlying table,

From your description, you need a field in T_Transaction to store the price at the time the record was created - one of the few occasions where data is 'duplicated'

Alternatively your T_Products table (or more likely a separate table) contains a history of prices and when they were effective from so you query would look up the price based on the transaction date.

please see my posts here and here

Thank you so much guys, this is really helpful.

The links you posted jdraw are exactly what i was looking for

I will get cracking!