How to create/update a recordset in a form with complex table relationship in Microsoft Access

Question

I have these tables:

tblOrders ('OrderId', 'Date', 'Client')
tblOrderDetails ('Id', 'OrderId', 'Product', 'Qty', 'Price')
tblPriceList ('PriceId', 'From Date', 'To Date', 'Client', 'Product, 'Price')

I have made a form based on tblOrders where the user can enter: 'Date' and 'Client'.
Then i made a subform based on tblOrderDetails where the user enter: 'Product' and 'Qty' for each order line.
Now i need to display each 'Price' next to 'Qty', that depends on the 'Product', 'Client' and 'Date' of the Order line, as the tblPriceList indicate.
Also when i change the client or date in the form i need these 'Price' values to be automatically updated in the form and also in the table tblOrderDetails.

I managed to create a query joining these 3 tables, but then i was unable to create a new recordset in the form because of it complex relationship.
I also tried by creating a textbox in the tblOrderDetails subform with a Dlookup that gets the correct 'Price' from the tblPricelist for each order line. But then i didn't find a way to store this values in the 'Price' field from tblOrderDetails.

Is there any way to resolve this? Maybe a change in the database design?

Skip Price field intblOrderDetailsand determ the Price in a query field by joiningtblOrderDetailswithtblOrdersonOrderIDthen jointblPricelistontblOrders.OrderID,tblOrders.ClientandtblOrderDetails.Productto get matchting Price. — ComputerVersteher

user3825344 user3825344 · Accepted Answer · 2019-06-25T00:40:36

Presumably when creating a new order detail, the user first selects a Product (from a dropdown?), and the Qty might default to 1.

o in your AfterUpdate event (using VBA) for the Product dropdown, put

Price = DLookup("Price","tblPriceList","Product = " & Me.Product & _ 
                                      " AND Client = " & Parent.Client & _ 
                                      " AND Date() Between [From Date] and [To Date]")

I'm assuming that the Client ID appears on the parent form somewhere in a control named 'Client'. Also put the same function in the AfterUpdate of the Qty, then you should be ok.

No need to use a complex query for Order details, just base the subform on the table, and use the Master/Child relationship to control it.

Are you reluctant to use VBA?

It's easy, just right click the Product dropdown control, select Properties, then Events tab
then click on AfterUpdate and select the elipsis. Click 'Code Builder' and enter your Me.Price = blah blah blah.
Do the same for the Qty control.

How to create/update a recordset in a form with complex table relationship in Microsoft Access

1 Answers