using vba to sum subform values

hey guys,

i know that if i want to sum the subform's records for a particular field i can put a textbox in the header or footer with the control source of "=sum([fieldname])"

however, how can i do it with VBA?

i want to sum it based on the subform's recordset not a dsum using the same criteria. essentially what the user sees should be what is being summed. (excluding filters - they're disabled = ))   )

thanks in advance guys!!!! = ))
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Use the .RecordsetClone of the subform.
Then loop this and sum the values of the field in question.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
<i want to sum it based on the subform's recordset not a dsum using the same criteria. >
...Can I ask why not?
0
Gustav BrockCIOCommented:
Because you have the RecordsetClone ready at hand, so no reason to reread the data.

/gustav
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

developingprogrammerAuthor Commented:
Yup guys, I want to try and be as consistent as possible by using the same recordset. But instead if loop through each of them, is there a faster way gustav? Something that does it all at one go
0
Gustav BrockCIOCommented:
No that I know of. But it is very fast.

/gustav
0
developingprogrammerAuthor Commented:
Ok cool thanks gustav!! Thanks boag2000 as well!! = ))
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
developingprogrammerAuthor Commented:
= ))
0
Jeffrey CoachmanMIS LiasonCommented:
Thanks, but I really was not seeking any points, I was just curious...

You can give all the points to cactus_data

JeffCoachman
0
Gustav BrockCIOCommented:
Not a problem for me, Jeff.

/gustav
0
developingprogrammerAuthor Commented:
well Jeff your question have always helped me question myself in a very constructive way too so if gustav doesn't mind i would like to show my appreciation for you taking a look at my question and helping me out! = ))
0
Jeffrey CoachmanMIS LiasonCommented:
Sometimes asking questions actually helps me learn too...

Sometimes a person will have an interesting reason for asking a question...

;-)

Jeff
0
developingprogrammerAuthor Commented:
Haha yup yup I've noticed and I've learnt so much from all the questions people've asked on my behalf prior to me even thinking of it!! Haha = )) thanks Jeff!! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.