Updating a related table

[jgarnick]

I have two tables related by an id in a one to many relationship.&nbsp;&nbsp;There is a check total in table 1 and an amount total in table 2.&nbsp;&nbsp;There can be several different amounts per check. (One check can pay for different items.) I have a form that can be used to edit the records.&nbsp;&nbsp;On this form is a subform that is based on a query of the table 1 and table 2.&nbsp;&nbsp;The user needs to be able to edit the individual amounts, not just the check total.&nbsp;&nbsp;But I need the check total to be updated if the individual amounts are edited. <br><br>How can I get the many table to update the one table?<br><br>Thanks!! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[Chopper]

jgarnick,<br><br>Here is one way it can be done:<br>Base the subform on the 'many' table alone.&nbsp;&nbsp;On the afterUpdate event for the item amount field (the field you are changing), use a recordset to total up the item amounts for the ID you are working on and update the check amount in the 'one' table to reflect the change.<br>There is a reasonable amount of code involved in this, but it isn't too difficult.&nbsp;&nbsp;If you are unfamiliar with using DAO and recordsets, let me know and I will send you a sample.<br><br>I realized there is probably more than one way to get this done, but I know this method is reliable.<br><br>-Chopper<br><A HREF="mailto:kenneth.mai@exch.compass-usa.com">kenneth.mai@exch.compass-usa.com</A>

 

[jgarnick]

The problem with your suggestion would be that the user may also need to change data from the 'one' table too.&nbsp;&nbsp;Should I put those fields directly on the form and have the subform use the 'many' table fields? <br><br>Thanks for your help!<br> <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[jgarnick]

Does anyone have any other ideas, because I don't think Chopper's suggestion will meet my needs??&nbsp;&nbsp;I need to display multiple lines (a complete log) on the edit form with information from both tables.&nbsp;&nbsp;I need to know how to update the 'one' table after data in the 'many' table is changed!<br><br>Thanks!<br><br> <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>