Changing ControlSource VBA Syntax

  • Thread starter Thread starter Judy
  • Start date Start date
J

Judy

I'm having difficulty setting the control source of an object on my
report. When I use

Me.Budget.ControlSource = "Budget1"

everything works fine, but I really need to add two fields (or more)
together, so I tried

Me.Budget.ControlSource = "=[Budget1] + [Budget2]"

I believe I have used this syntax before. This time the data on my
report is wrong. Within a group, each line has the same data as the
first line item in the group. Any ideas about what I'm doing wrong?
Thanks.
 
Judy said:
I'm having difficulty setting the control source of an object on my
report. When I use

Me.Budget.ControlSource = "Budget1"

everything works fine, but I really need to add two fields (or more)
together, so I tried

Me.Budget.ControlSource = "=[Budget1] + [Budget2]"

I believe I have used this syntax before. This time the data on my
report is wrong. Within a group, each line has the same data as the
first line item in the group. Any ideas about what I'm doing wrong?
Thanks.
Click to expand...

In what event are you setting this controlsource? Could you explain
what sort of grouping is going on, and where the text box is in the
grouping sections?

A test worked fine for me in a simple, ungrouped report.
 
Thank you for your reply. It is in the open event of the report. Each
line item of the report is a purchased item. They are grouped first by
project and then by Director.

Something like

Director Smith
Project 1
Item 1
Item 2
Item 3
Project 2
Item 4
Item 5
Director Jones
Project 3
Item 6
Item 7
Project 4
Item 8
Director Fox
etc
 
Judy said:
Thank you for your reply. It is in the open event of the report. Each
line item of the report is a purchased item. They are grouped first
by project and then by Director.

Something like

Director Smith
Project 1
Item 1
Item 2
Item 3
Project 2
Item 4
Item 5
Director Jones
Project 3
Item 6
Item 7
Project 4
Item 8
Director Fox
etc
Click to expand...

I'm not able to reproduce this so far. What version of Access? Would
you care to send me a cut-down copy of the database to look at? If so,
eliminate all but the elements necessary to demonstrate the problem,
compact it, and zip it to less than 1MB in size (preferably much
smaller) -- I'll have a look at it, time permitting. You can send it
to the address derived by removing NO SPAM from the reply address of
this message. If that address isn't visible to you, you can get it from
my web site, which is listed in my sig. Do *not* post my real address
in the newsgroup -- I don't want to be buried in spam and viruses.
 
Thanks for your kind offer. I think I have found a work around for now
(combining them in the query that the report is based on, not very
elegant I realize). I am so grateful for your time. Judy
 
Judy said:
Thanks for your kind offer. I think I have found a work around for
now (combining them in the query that the report is based on, not very
elegant I realize). I am so grateful for your time. Judy
Click to expand...

But then why are you modifying the controlsource at run time in the
first place?
 
Basically, there are six columns on the report. Which six depends on
the user input and the current date. There are 39 fields that need to
be combined in various ways to get the appropriate value for the column.
 
Judy said:
Basically, there are six columns on the report. Which six depends on
the user input and the current date. There are 39 fields that need to
be combined in various ways to get the appropriate value for the
column.
Click to expand...

Another alternative would be to have text boxes for all possible
combinations, overlaid on the report, but only make those visible that
you want for a particular run. But I don't know why your original code
wasn't working, as I was unable to reproduce the problem. Another thing
to do is make sure that every field you want to use in a calculation
also exists in a bound text box on the report, even if that text box is
invisible. I don't know if this could be the cause of your problem or
not.
 
Lol, that was the way I started out doing it before I decided to learn
VBA. The reports were so difficult to modify and edit with all those
boxes stacked on top of each other. I am also perplexed as to why it
doesn't work, as I have used that sytax in similar situations on other
reports. Could the problem be that the field names have a space?

I will try adding the invisible fields to the report, as per your last
suggestion. Thanks so much for your help.
Judy
 
Judy said:
Lol, that was the way I started out doing it before I decided to learn
VBA. The reports were so difficult to modify and edit with all those
boxes stacked on top of each other.
Click to expand...

True, they would be.
I am also perplexed as to why it
doesn't work, as I have used that sytax in similar situations on other
reports. Could the problem be that the field names have a space?
Click to expand...

I don't think so, since you seem to have them properly bracketed.
I will try adding the invisible fields to the report, as per your last
suggestion. Thanks so much for your help.
Click to expand...

Please let me know if that makes things better.
 
Adding the fields didn't change anything. I don't mind using a query.
My big concern is that I have an underlying issue that will come back
to haunt me later.
 
Judy said:
I tried taking the spaces out and it seems to fix it the problem.
Weird, huh?
Click to expand...

The only thing I can think of is that you have a name conflict of some
kind. Bear in mind that you can't have, for example, a control named
"Budget1" that is bound to the expression "=[Budget1]+[Budget2]". If
that was the cause of the problem, taking spaces out of field names
might have eliminated the conflict.

That's all speculation, though.
 
Judy said:
No, my Controls all had unique names. Thank you so much for your time
and effort.
Click to expand...

You've got me, then, and I'm sorry I couldn't be more help. But if
you've got it working now, all's (reasonably) well. You're welcome.
 
Back
Top