• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • 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)President / OwnerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
boodyAuthor Commented:
Jim,

How do I replace the 1 with calculation?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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)President / OwnerCommented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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