Invoice help

[IvanStf]

Hi,
Im new to this forum and new to MS Access so if a question is easy or stupid please do not mind. Anyways, I use Excel almost exclusively in my company and until recently we had specialized software for invoicing and orders. The deal between IT provider and us is off now cause they demand more money. So that until the implementation of new software i need a quick solution.
Based on some web Access tutorial I managed to build some tables and forms and everything works just fine. I enter data in invoice form that also contains subform and every invoice has separate number (autonumber). Subform is made from query that has prices from products table. So, onto the problem. As I said, everything works good until I change price of the product. I dont change prices often but sometimes I have to. When I change price, I want it to apply only to the latest invoice(form). In my case it changes price to all previous invoices which i dont want.
So, my question is, is there a way to, once I am done with the invoice to save it without possibility of being affected by future change in price.
Thanks in advanced, help would be appreciated.

 

[CJ_London]

the normal rule in databases is not to store calculated data, however this is possibly one of those cases where the rule does not apply - you can store the price in the invoice detail record so it is only added at the time of creation (same usually goes for tax rates as well)

The alternative is a bit more complicated. you need an extra table to store prices against the product (rather than it being in the product table) together with a from date and possibly a to date (tho the latter is often not required, only if it is to be discontinued).

so

tblProducts
ProductPK autonumber
ProductDesc text


tblPrices
PricePK autonumber
ProductFK long (to link to tblProducts)
Price Currency
PriceFrom date


then in your invoice form to find the price on the invoice date you would have something like this in the product afterupdate event on the subform

 dim rst as dao.recordset
  
 set rst=currentdb.openrecordset("SELECT TOP 1 Price 
 FROM tblPrices 
 WHERE ProductFK=[COLOR=red]" & me.ProductFK[/COLOR] [COLOR=red]& "[/COLOR] And PriceFrom<=[COLOR=red]" & Parent.InvDate[/COLOR] [COLOR=red]& "[/COLOR] Order By PriceFrom Desc")
 me.price=rst!price
 set rst=nothing

depending on how you are actually doing this, the bits in red may be called from somewhere else and therefore referred to differently.

Another benefit of this method is that you can price up in advance - if you only have a few, probably doesn't matter but for large volumes, being able to get ahead of the game can be worthwhile - and you now have a pricing history which may be useful for analysis and strategic decision making

 

[IvanStf]

Thanks for a quick reply. I will focus on your second solution cause I need to price up in advance and of course I need data for analysis.
Thanks again for help.