Auto Populate Field in intermediate table? (1 Viewer)

[LMacdonald]

Hello there,

Apologies if this has been posted before but I am unable to find a solution.

Say I am trying to create a database to record sales and I have the following tables (simplified for easier posting here):

Orders tbl:

• OrderID (Primary Key)
• Date

Stock tbl:

• Product Name (Primary Key)
• Price

Orders Stock tbl (Intermediate Table):

• OrderID (Foreign Key)
• Product Name (Foreign Key)
• Quantity
• Price

When populating an order the Orders Stock table is used so that more than one product can be assigned to each order. The Product Name in the intermediate table operates using a lookup table from the Stocks Primary Key. How may I arrange it so that when selecting a product the Price in the intermediate table automatically updates to match the price in the Stocks table when selecting the product name.

I am aware this is bordering duplication of data but I wish for the current price to be recorded when placing the order so that the price in the Stock table can be changed and thus update future orders with a new price whilst keeping a record of old orders accurate to the price they were sold.

Any help would be much appreciated.

Kind Regards,

 

[theDBguy]

Hi. Welcome to AWF! If you're using a form for data entry, then you could use code to auto assign the price. Look into the Column property of a Combobox.

 

[LMacdonald]

Thank you very much, I am trying to use a form but have the Orders form and then Orders Stock as a subform so I don't think it is possible to adapt the properties of a subform?

 

[theDBguy]

Thank you very much, I am trying to use a form but have the Orders form and then Orders Stock as a subform so I don't think it is possible to adapt the properties of a subform?

Yes, you should be able to use code in subforms too. Give it a shot and let us know what happens. Did you look up the Column property?

 

[Pat Hartman]

Include the price as the second column of the combo. Then in the AfterUpdate event of the combo that selects the product, add:

Me.Price = Me.ProductName.Column(1)

Notice the .Column(1). That is referencing the SECOND field of the combo's rowsource since the columns are a zero based array. If the price were the third column of the RowSource, the reference would be .Column(2)

PS, this is not a violation of normal forms since you are storing the price AT a POINT IN TIME.