title: How to create/update a recordset in a form with complex table relationship in Microsoft Access
body: <p>I have these tables:</p>
  
  <ul>
  <li><strong>tblOrders</strong> ('OrderId', 'Date', 'Client')</li>
  <li><strong>tblOrderDetails</strong> ('Id', 'OrderId', 'Product', 'Qty', 'Price')</li>
  <li><strong>tblPriceList</strong> ('PriceId', 'From Date', 'To Date', 'Client', 'Product, 'Price')</li>
  </ul>
  
  <p>I have made a form based  on <strong>tblOrders</strong> where the user can enter: 'Date' and 'Client'.<br/>
  Then i made a subform based on <strong>tblOrderDetails</strong> where the user enter: 'Product' and 'Qty' for each order line.<br/>
  Now i need to display each 'Price' next to 'Qty', that depends on the 'Product', 'Client' and 'Date' of the Order line, as the <strong>tblPriceList</strong> indicate.<br/>
  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 <strong>tblOrderDetails</strong>.</p>
  
  <p>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.<br/>
  I also tried by creating a textbox in the <strong>tblOrderDetails</strong> subform with a Dlookup that gets the correct 'Price' from the <strong>tblPricelist</strong> for each order line. But then i didn't find a way to store this values in the 'Price' field from <strong>tblOrderDetails</strong>.</p>
  
  <p>Is there any way to resolve this? Maybe a change in the database design?</p>