Transaction log

[OptimisticYid]

I have what I figure is a pretty standard format transaction log which is formed in part by three main tables - Order, Product and Transaction. Order can have many transactions, many transactions can have one product. Works well, produces a pretty invoice, feeds off into other tables as required but I have one 'simple' problem which is foxing me.

I can put in the price associated with the transaction manually, ideally i would read it in dynamically from the product table. This I can do but it leaves me with the problem that if a product price changes it will cascade throughout the whole system. So what I need to do is take the product price dynamically from the product table at the time of the transaction and store it as the price associated with the transaction, just not sure how best to achieve this.

Any help much appreciated as always.

 

[DCrake]

Simple Software Solutions

In your transaction table, which I assume is the basis for your Invoice create new fields in there and at the point of creating the transaction save the price at that point in time. Get your invoce query to query this price and not the current price in your products table. As you say changes in prices will cascade through all your historic data. Also consider what else might change over time that will affect historic data, such as VAT rates, VAT Charges, Currency exchange rates,etc.

CodeMaster:

 

[OptimisticYid]

What would I put in the control source for the Price field (text box) in my transaction table? At the moment it is just set to the transaction price as opposed to the product price, guess I need to select the price from the price field in the products table and write it into the price field on the transaction table, seems it would be easy enough if there was a row source equivalent for text boxes as there is for combos.

 

[DCrake]

Simple Software Solutions

When you create an invoice it is based on the cost of products and services at that point in time. Therefore think of the items that may change over time and create the appropriate fields in the transactions table. As explained earlier. Then at the point when you select an item to to attach to a transaction, such a product (widget). A widget costs £1 at today's prices according to the products table. However next week you are updating your product list and increasing prices by 10% so after next week they will be £.10each. So you need to save the price of £1 in the transaction table because if you generate the invoice after next week it will show the price as being £1.10p.

David

 

[OptimisticYid]

As per my first post .......

"So what I need to do is take the product price dynamically from the product table at the time of the transaction and store it as the price associated with the transaction, just not sure how best to achieve this."

So I understand the logic, just not the practicalities of the code that is going to achieve it for me

 

[OptimisticYid]

Have solved it myself, tahnks anyway. As usual I was trying to make things too complicated, cup of coffee and all becomes clear!

 

[missinglinq]

Forum etiquette requires that when you solve a problem on your own, you post the solution, so that anyone coming on the thread can benefit!

 

[OptimisticYid]

Forum etiquette requires that when you solve a problem on your own, you post the solution, so that anyone coming on the thread can benefit!

Certainly, I restructured the subform query to include my products and transactions table. I then added Me.Price = Me.ProductPrice to the after update property of my ProductID field so that when the ProductID is selected the price from the product table (productprice) is dumped into the price field in the treansaction table. Hope that makes sense