• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 556
  • Last Modified:

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
0
boody
Asked:
boody
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
Jim Dettman (Microsoft MVP/ EE MVE)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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now