Auto Populate Field in intermediate table? (1 Viewer)

LMacdonald

New member
Local time
Today, 07:39
Joined
Mar 18, 2020
Messages
2
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

I’m here to help
Local time
Yesterday, 23:39
Joined
Oct 29, 2018
Messages
10,252
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

New member
Local time
Today, 07:39
Joined
Mar 18, 2020
Messages
2
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

I’m here to help
Local time
Yesterday, 23:39
Joined
Oct 29, 2018
Messages
10,252
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

Super Moderator
Staff member
Local time
Today, 02:39
Joined
Feb 19, 2002
Messages
28,780
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom