Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Saturday, November 24, 2018 4:45 PM
Using Access 2016 on Windows 10
I am using a sub form with a query “Record Source”, that has its “Default View” set to “Datasheet” with a Totals row toggled on. The Datasheet view is required to allow the user to filter and sort columns, reposition columns, hide and redisplay columns. The Totals row is required to allow the user to get the sum or average of some columns.
There are many posts complaining about the Totals row results (sum, average, count…) disappearing after the Parent form is recalculated. Simply selecting the Totals row by clicking its row designation box brings back the results.
What is the VBA code to select the Totals row on a sub form that has its “Default View” set to “Datasheet” with a Totals row toggled on?
Suggesting the use of a sub form with its “Default View” set to “Continuous Forms” is not an option.
I would be shocked if there was no way to select the Totals Row using VBA.
I have created a TEST button with the following code that I will use with suggested code.
Private Sub cmdTEST_Click()
Forms!fHome!fs_Summary.SetFocus
End Sub
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
All replies (11)
Monday, November 26, 2018 6:24 PM ✅Answered
After much testing I came up with a way to select the Totals row on a Datasheet view and bring back the values that disappeared after data was added and sub form is re-queried.
When I close the form used to add data, the following code fixes the Totals row.
Private Sub Form_Close()
Forms![fHome]![fs_Summary].Form.Requery
Forms![fHome]![fs_Detail].Form.Requery
Forms!fHome.SetFocus
Forms!fHome!fs_Summary.SetFocus
DoCmd.RunCommand acCmdRecordsGoToLast
SendKeys "{DOWN}", True
Recalc
End Sub
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
Tuesday, November 27, 2018 4:13 PM ✅Answered
Looking at the sample database, I see the problem manifesting after requerying the form (a subform, in this case) to reflect a record that was added externally to the form. I find that following the requery with a call to the form's Recalc method seems to solve the problem. So where you had
Forms![fHome]![fs_Summary]!Requery
I put
With Forms![fHome]![fs_Summary].Form
.Requery
.Recalc
End With
And that appears to solve the problem.
I have sent you a revised copy of your sample database. Let me know if that version works for you. The problem seems to be somewhat inconsistent, so it may just be an illusion that I've fixed it. <g>
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Saturday, November 24, 2018 6:53 PM
Hi,
I'm not sure I understand the question. When I navigate through the parent form or requery it, I still see the totals in the subforms. Are you saying the row is still there but the values disappear?
Saturday, November 24, 2018 11:57 PM
.theDBguy : "Are you saying the row is still there but the values disappear?"
Yes, that is exactly what I am saying, and Parent form is unbound.
Me.Requery on my cmdTEST button on the parent form does not bring back the values.
BUT, my question is simple.
What is the VBA code to select the Totals row on a sub form that has its “Default View” set to “Datasheet” with a Totals row toggled on?
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
Monday, November 26, 2018 6:37 PM
That may be the only way to do it, but bear in mind that SendKeys …
(a) isn't completely reliable (because some other window may possibly grab the focus just before your keystrokes are set, and
(b) has a long-standing bug where it toggles the NumLock key. Of course, if it doesn't do that for you or your users, this may not be an issue for you; also, if it does it consistently, you can also send the {NumLock} key along with the {Down} key.
Your original issue may be a video-driver issue, in which case you may be able to solve it by fiddling with the settings of your video driver.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Monday, November 26, 2018 7:22 PM
I tested on two different computers and a laptop, and found the values in the Totals row of the Datasheet view would always disappear after data was added and sub form re-queried. Could a video driver issue be the culprit in different PCs?
This issue has been around since 2014 see related post not from me.
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
Monday, November 26, 2018 7:29 PM
I tested on two different computers and a laptop, and found the values in the Totals row of the Datasheet view would always disappear after data was added and sub form re-queried. Could a video driver issue be the culprit in different PCs?
Possibly, but unlikely, I agree. If you aren't through with this issue, I'd love to look into it to see if I can find a better resolution than SendKeys. Can you post or e-mail me a minimal repro database to experiment with?
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Monday, November 26, 2018 9:04 PM
Hi,
I'd love to get a sample copy as well. As I said earlier, I tried this and did not see the totals disappear on my form/subform.
Tuesday, November 27, 2018 3:25 AM
Hi Dirk,
minimal repro database sent to your email.
Thanks.
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.
Tuesday, November 27, 2018 3:31 PM
I've received the database and will give it a look.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Tuesday, November 27, 2018 5:50 PM
Thank you so much Dirk. This will make the app run smoother.
If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.