Time sensitive data

[ViRi]

Hi

I have a Form frmInvoice with controls that are bound to a Table tblInvoice.

As well as the controls that are bound to tblInvoice frmInvoice also has 3 other controls PartName, UnitPrice, SerialNo that are bound to a separate Table tblParts. In order to populate these three controls I am using a Combo where the user selects the PartName required and the UnitPrice and SerialNo controls are populated. This works so long as the UnitPrice does not change.

The problem I have is that as time goes by the UnitPrice will change. I would like to freeze the UnitPrice control value of each record at transaction time so that when the UnitPrice changes in tblPats over time it will not be reflected in all the previous records that have been saved with the previous UnitPrice. I have used separate tables to try to adhere to Normalization rules as much as possible.

The Query behind the Combo reads as follows: SELECT Parts.PartsID, Parts.PartName FROM tblParts ORDER BY [PartName];

The Control Source of the Combo is a foreign key to tblParts fkPartsID

And the Relationship between tblParts and tblInvoice is One-To-Many i.e One Part can have many Invoices.

ViRi

[Mclaren]

Use a dlookup function to populate the tblinvoice Unit price field on the after update event of the part number.

This solution ensures that only the Part number is linked to tblparts.

[ViRi]

I'm sorry I don't really know what you mean. tblInvoice does not contain a UnitPrice field this field is in tblParts

ViRi

[maximus]

Let me get you straight the three controls PartName, Serial Number, and Unit Price are the a part of a subform. Maclern was right and you probably didnot understand whet he was trying to say.I don't know the Source these feilds may be you are using a query or a table. Well that is a seperate isuue altogether.

It seeme that you are maintaining a product table and all the product has a definite serial number now here is how I would have done.


Product_table:{ProductName,Serial_number,Unit_Pric e}
Invoice_Main:{PK..... Tables of your Choice.....}
Invoice_Sub_Table:{Link_id(FK),Product_Part_Name,S erial_number,Unit_price,Total}

Now in the table design view make the Serial_number a lookup field using the lookup wizard linked to Serial_number of Product_table.

Create a form with Invoice_main, and another form with Invoice_sub(Name Invoice_Subform). Put Invoice_Sub as a subform on the Invoice_main form link Pk with FK.

Now in the Invoice form you have two comboboxes Product_Part_name, Serial_number and Two text boxes Unit_price and Total.


and paste this code on the after update event of Serial_number combo on the Invoice Subform

Me.Unit_price=Dlookup("[Unit_Price]","Product_table","[Serial_number]="& Me.Serial_number)

This will ensure that only the current prices for the products will be copied to the Unit_price field and it will not change eny previous data when you change your price in the product field.


I would suggest you to look into the sample database called Northwind that comes along with your office pakage. It has a similar example with Orders and Orders subform. As far as I remember the Order Subfrom also has a code in its BeforeUpdateEvent and OnDeleteEvent to check whether the subform is opened as a stanalone form or in the main form. Check it out it will help you a lot.


If this solves your problem please mark this thread solved.