Updating another field in the same record on update of another field

[mounty76]

Hi All,

I have three tables

Expenses - Parent
LineItems - Child
Currency - Linked to the child with a drop down menu to select a currency

All works fine and I have a form to enter the information into. However I have a field in the LineItems table called HomeCurrency, I need to add some VBA behind the 'after update' event for when the actual cost is entered so that it looks up the exchange rate in the currency table then multiplies this with the cost and puts it into the HomeCurrency field in the LineItems table.

I know I shouldn't really do this and calculated fields are better done using queries, however I need to keep the historical conversion in the lineitems table, if I just use a query each time then it would be at the current exchange rate and not that what it was on the date the entry was made.

Hope that makes sense! Any help much appreciated.....I'm thinking I need a DLookup VBA expression but any help would be great

 

[CJ_London]

if I just use a query each time then it would be at the current exchange rate and not that what it was on the date the entry was made.

doesn't need to be because you can keep a history of currency rates, including a 'date from' field. Match the expense date to the highest date on or before the expense date.

That said, the exchange rate used has 'legal' significance and so should be stored with the line item. The reason the above may be relevant is if you are entering expenses in October relating to August, and the exchange rate was updated in September, you are using the wrong rate if you don't have a history.

So just store the exchange rate you have used in the line items table - you can calculate the converted value using a query rather than storing that value.

 

[The_Doc_Man]

Hi All,

I have three tables

Expenses - Parent
LineItems - Child
Currency - Linked to the child with a drop down menu to select a currency

All works fine and I have a form to enter the information into. However I have a field in the LineItems table called HomeCurrency, I need to add some VBA behind the 'after update' event for when the actual cost is entered so that it looks up the exchange rate in the currency table then multiplies this with the cost and puts it into the HomeCurrency field in the LineItems table.

I know I shouldn't really do this and calculated fields are better done using queries, however I need to keep the historical conversion in the lineitems table, if I just use a query each time then it would be at the current exchange rate and not that what it was on the date the entry was made.

Hope that makes sense! Any help much appreciated.....I'm thinking I need a DLookup VBA expression but any help would be great

Something about your "All works fine..." paragraph bothers me so I want to clarify it. It seems to imply that you are updating the LineItems table from an _AfterUpdate event - when this kind of computation actually should occur in the _BeforeUpdate event. Your form seems to have a circular sequence of events. CJ's comment is probably the better way to approach this anyway, but I wanted to point out what seemed unusual to me.