[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1725
  • Last Modified:

DSUM not auto-updating on Access 2010 Form

Hello.  I have an Access database with a form that contains a subform.  The main form has a textbox with a DSUM formula in it.  The DSUm formula utilizes the data in the subform.  When using this database in Access 2003, the following behavior occured:

User enters a new row in the subform (e.g. a new record added to the linked table)
The textbox with DSUM formula on the main form would "automatically and immediately" update the total to reflect the addition of the new record in the subform

We recently upgraded our systems and are using Access 2010.  When we open the same exact database in Access 2010, the following happens:

User enters a new row in the subform (e.g. a new record added to the linked table)
The textbox with DSUM formula on the main form DOES NOT automatically update the total to reflect the addition of the new record in the subform

I have checked out the events available on the subform and there are only 2 of them (OnEnter and OnExit).  I have temporarily added a Me.Recalc command in the OnExit event but the person still has to exit the subform to see the update.  This is painful because they are entering many records in a row and need to see the updates.

Is there an alternative to DSUM that will behave the way it did in 2003?
0
dpmoney
Asked:
dpmoney
  • 8
  • 7
1 Solution
 
Dale FyeCommented:
Open the form that is your subform in design view.  With it open that way, you will see the entire list of events that are available for that form.

I would recommend the Form_AfterUpdate event, something like:

Private Sub Form_AfterUpdate

    me.Parent.txtYourControlName.Requery

End Sub

"txtYourControlName" would be the name of the control on the main form which contains the DSUM() domain function call.
0
 
dpmoneyAuthor Commented:
I thought of going down this road, but the subform on the main form is not bound to a separate, distinct form that I can edit.  Instead ,it is bound directly to the linked table and its view is the raw data entry view.  The linked table only has a few columns in it.  Basically, a primary key, a dollar amount and a percentage.  This approach has worked well for years in terms of data entry ease so if I can keep it that way, it would be ideal.  Do you have any ideas taking this additional data into consideration?
0
 
Dale FyeCommented:
Should make no difference.  if it is truly a subform, and it is only being used as a subform on this one main form, then the technique I mentioned above should work.

I'm honestly trying to figure out how you had it configured to work in 2003, because  a control on the main form would not normally update itself when an entry is made in the subform.

Is all the rest of your code working?  Access 2007 and 2010, disable macros automatically unless the application is saved to a "trusted location".  Do you see something that looks like the attached when you open your application?Access macro security warning
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dpmoneyAuthor Commented:
Yes, all code is working fine.  No warnings.  We have a step in our setup doc which adds the location of the Access DB on the user's local workstation to the Trusted Locations.  The Access DB is a front-end to a back-end SQL server (probably irrevlevant in this case).  Either way, I've attached 2 screenshots - one shows how the subform looks in the main form and the other shows the subform properties where you can see it is linked directly to the table vs. a form.  Hence, there are no events available to go after.  This was setup a long time ago, but has worked well for us.  Just annoying that 2010 doesn't treat the DSUM the same on the form.  Do you have any other thoughts on how to attack?
Mockup-1.PNG
Mockup2.PNG
0
 
Dale FyeCommented:
Open the main form in design view.  It should look something like the attached image, which displays a subform and the property sheet.  If you select the subform (note the arrow and the black box in the upper left of the subform), the Event properties associated with the subform will be visible.subform event optionsSelect the BeforeUpdate, set it to [Event Procedure] and then open the associated code window.  Enter my code from above in the code window, making the appropriate change to the name of your combo box and the parent forms textbox.  Then compile to make sure you have the syntax correct, then save and open your form in form view.
0
 
dpmoneyAuthor Commented:
I'm totally with you, but it seems like when you have a child form bound directly to a table vs. a actual form, the # of events available on that child are not the same.  I've attached a screenshot showing that there are only 2 events available when the child form is bound to a table.  Basically, the only events available are:  OnEnter and OnExit.  I notice in your example form that you've made, your subform is an actual form.  Change the binding property so it is a table, not a form and I think you will see what I'm seeing.  Please let me know...

Event Properties of child form (linked directly to table) - only 2 avaialble
0
 
Dale FyeCommented:
No, you have to click that little square in the upper left corner of the child form (the one the arrow is pointing at).  That gives you access to the events associated with the subform.  

What you have displayed above is the list of events associated with the subform control (which is the frame in which the actual subform resides).
0
 
dpmoneyAuthor Commented:
I see what you mean.  However, this subform is directly bound to a table, not a form, so I don't have that option.  There is nothing to click on.  I've included a screenshot from within Access 2010.  You can see the name of the linked table, not a form so it doesn't give me that option to click on.  I may be forced to make an actual form and link the subform control to the form vs. directly to the table.  I'm trying to avoid that.  Seems silly that Access 2010 would change the behavior of the DSUM on a form.

Design View in Access 2010
Do you see what I mean?
0
 
Dale FyeCommented:
Learn something new every day.  I never knew that you could actually use a Table as the SourceObject for a subform control.  I had never tried that, but just did it in 2007 and see what you mean.

This basically looks like a datasheet, so you should be able to create a form in datasheet mode and change the SourceObject (on the data tab) from the table to the new datasheet.  Then you can use the BeforeUpdate event of the datasheet form, as previously stated.
0
 
dpmoneyAuthor Commented:
I actually gave it a try, but am having some difficulty with which columns show up.  On my existing implementation, only 3 columns show up in the form (Primary Key, ValAmount, ValPercent).  In the actual table, there are 5 columns, the 3 above, plus 2 more, clientid and invoicenum.  If I move forward, I'd like to have it displayed the same way with just the 3 columns, but don't se how to do that.  If I make a form and set the columns I don't want to appear as visible=false, they still show up in datasheet view.  Realy weird.  I'm still bummed by the fact that the DSUM on the parent form does not detect the newly added record in the linked detail table and simply update like it did in 2003.  Can you think of any other way to force the DSUM calc'd field on the Master Form to detect and update on its own?
0
 
Dale FyeCommented:
let me see if I can get any of the other Access experts to chime in.
0
 
Dale FyeCommented:
if you don't want to see those columns, just delete them from the datasheet in design view.  It doesn't look like the table is actually bound to the form using a master/child relationship, so this should not make any difference to the application.
0
 
dpmoneyAuthor Commented:
I'm seeing this issue sprinkled throughout the DB, not only on this form.  It all comes down to the fact that calculated fields on a form with DSUM in the formula no longer update on their own.  It seems like you need to do a manual recalc to get the values to update.  This is disappointing.  I'm going to give you the points on this question becaue your advice got the form we discussed working.  I made a real form, and bound my subform control to it, then it all seems to work.  Now I have a bunch of programming updates to do.  Thanks.
0
 
Dale FyeCommented:
Sorry we were not able to figure out why the same functionality that seemed to work for you 2003 is not working in 2010.  But glad we found you a solution.
0
 
dpmoneyAuthor Commented:
Thanks.  I ended up putting in some manual me.recalc commands here and there on combo box values changing since the DSUM's rely on the ClientID associated with the combo boxes.  Seems to be working well.  Thanks for your time today!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now