How do you refresh the parent form when there are changes made to data in the subform

[evander]

Name: Capture.PNG  Views: 17  Size: 31.0 KB  ID: 18878" class="thumbnail" style="float:CONFIG" />

Hi everyone!

As shown above, I have a form with a subform in it. The fields "Price", "Discount", and "Net Price" in the parent form refer to the sums of the fields in the subform. They have the same names so you can easily refer to them.

The "summary" fields in the parent form are actual table fields, and not calculated fields. I enforce consistency in calculating the sums by running some code behind the scenes. Both forms obtain their sources from SQL Server via ODBC. My problem is, every time I enter, edit, or delete some data, the Access form does not immediately display the results, so I need to refresh the forms.

I placed a simple piece of code in the Form_AfterUpdate event of the subform so that every time a record is inserted, edited, or deleted in the subform, the parent form is refreshed to reflect the sums of those three fields. However, when I try to move the cursor away from the current record (by pressing up or down key), the cursor remains in the active row of the datasheet, so I still have to press up or down arrow key again to successfully move to the next row.

An end user will immediately find this behavior frustrating.

Is there a way for me to refresh the parent form whenever I make changes in the subform? I tried tinkering with "Refresh Links" and "ODBC Refresh" but they didn't work for me. I also made sure that the form's recordset type is set to dynaset. These options still do not allow the form to immediately reflect changes in the underlying database data, so I resorted to refreshing or requerying the parent form whenever there are changes made in the subform.

Thank you for helping me. Please let me know if I need to explain my situation in more detail.


Evander

[June7]

Ideally, calculated data is not saved, especially aggregate data - calculate when needed.

If you have expression in main form textbox that references a textbox in footer of subform with Sum calc, the main form will immediately reflect the revised data when the subform record is committed and the Sum recalculates.

Then if you must save the calculated value, use code behind the main form to save, I am not sure what event would be best.

[evander]

Thank you for replying June 7. When you say, "Ideally, calculated data is not saved..." I really know what you mean. But when designing accounting systems, you need to break the rules of normalization a bit, and I've seen this concept implemented in all accounting systems I have used, including Quickbooks, SAP Business One, Sage, etc.. Otherwise, you will suffer from performance issues every time you run aggregated queries especially when you have large amounts of data. If you've read Murach's SQL Server and seen the accounts payable system that the author has designed, you will understand where I'm coming from. Real-world accounting systems employ the technique of storing aggregated values in the interest of efficiency.

Anyway, in my case, I don't need to create a code behind to calculate the total values because I implemented triggers and stored procedures in the SQL Server backend to do this job. That is, every time I make changes in the "detail" table, summary values of that detail table are immediately stored in the "parent" table. What I want is Access to display these updated records in the form, which the Refresh or Requery methods accomplishes quite effectively.

However, the Requery method causes the recordset to scroll to the beginning, so I prefer Refresh, as concurrency is not an issue. Still, putting the Refresh method in the Form AfterUpdate event of the "subform" prevents the cursor from moving downwards if you press the down arrow key. You have to press the down arrow key again if you want to move the cursor to the next record.

I can upload a sample database to illustrate this.


Thanks June7.


Evander

[evander]

Problem solved.


I used the subform's Current event. It contains a simple IF statement that refreshes the parent form if the subform is dirty.