Setting a textbox's controlsource in VBA on a report

Simple question,

I have an unbound txtbox on a report, we can call it txtValue.  In the report I want to take the values from three other txtboxes that are dynamically generated, sum them, and have that value displayed in txtValue.  I don't understand why when I write the code and I reference txtValue that it doesn't give me the option for controlsource.  I have the summed value, i just don't have a way to give it to txtValue so it can be shown on the report.

Please help

Thanks,
Boody
boodyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Then in the OnFormat, simply do:

 Me![year3Total] = Me![Year1] + Me![Year2] + Me![Year3]

for example (but you need to use your actual control names)

Jim.
0
 
boodyAuthor Commented:
By the way, I'm using a recordset to get the values that I want for the other textboxes.
0
 
maatthiasCommented:
Try summing it in the report detail's OnFormat event.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Reports are a bit special.  In the way that controls and fields work together.  The approach is not to set the controlsource, but simply fill the control in with a value:

  Me![mycontrolname] = 1

  Would put a "1" in the control.  Replace the "1" with the rsult of your calculation.

Jim.
0
 
boodyAuthor Commented:
Jim,

How do I replace the 1 with calculation?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You said you had the summed value correct?  Just do:

 Me![mycontrolname] = <summed value>

 in the OnFormat event of the section where the control is located.

  or did I miss something?

Jim.
0
 
boodyAuthor Commented:
Actually, I have five textboxes that are receiving values.  I'm just trying to get the total of the first three. I got the total by using a variable and looping it through the first three.  That's in VBA, but I can't use that in the expression builder.

My report looks like this:

Year1 ... Year2 ...Year3 ...Year4 ...Year5 ... 3Yeartotal
12345.....12345....12345....12345....12345.....37305

I don't have a running sum or anything like that. All of the year captions and values get their data from a recordset since they change depending on what the year the user wants to specify.
0
 
maatthiasCommented:
In that case there shouldn't be any need for coding in OnFormat event.  Just set the textbox's controlsource as an expression sum it up.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.