By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

MS Access - how-to set control source for text box to query

P: n/a
Bob
I've got a bound report with a query as the record source. I'm adding
a total to the footer and have inserted a text box to display it. I'm
attempting to set the control source of the text box to a different sum
aggregate query using the expression builder. When I select the query
and it's single field, single row field it inserts the following in the
control source box for the text box.

=[Qrydetsum_offtotal]![Office_total]

I've tested the query ( Qrydetsum_offtotal ) independently and it runs
and returns a single value each time for the calculated field,
Office_total. But when the report runs it simply displays #error.
As an alternative I've tried to load the result of the query into a
variable and then reference the variable in the control source of the
text box, but when I do that, nothing seems to happen.

I'm sort of new to VBA, so if anyone has suggestions about how to do
this or a simpler way, I'd appreciate the input.

Thanks, Bob

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Bob:

If you are looking up a single value then the DLookup function will probably
work. There is also a DSum function when you need to calculate a sum from a
table or query different from the record source. Information on both of
these can be found in Access Help.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
"Bob" <rm*******@network-spectrum.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
I've got a bound report with a query as the record source. I'm adding
a total to the footer and have inserted a text box to display it. I'm
attempting to set the control source of the text box to a different sum
aggregate query using the expression builder. When I select the query
and it's single field, single row field it inserts the following in the
control source box for the text box.

=[Qrydetsum_offtotal]![Office_total]

I've tested the query ( Qrydetsum_offtotal ) independently and it runs
and returns a single value each time for the calculated field,
Office_total. But when the report runs it simply displays #error.
As an alternative I've tried to load the result of the query into a
variable and then reference the variable in the control source of the
text box, but when I do that, nothing seems to happen.

I'm sort of new to VBA, so if anyone has suggestions about how to do
this or a simpler way, I'd appreciate the input.

Thanks, Bob
Nov 13 '05 #2

P: n/a
On 1 Jun 2005 20:42:42 -0700, Bob wrote:
I've got a bound report with a query as the record source. I'm adding
a total to the footer and have inserted a text box to display it. I'm
attempting to set the control source of the text box to a different sum
aggregate query using the expression builder. When I select the query
and it's single field, single row field it inserts the following in the
control source box for the text box.

=[Qrydetsum_offtotal]![Office_total]

I've tested the query ( Qrydetsum_offtotal ) independently and it runs
and returns a single value each time for the calculated field,
Office_total. But when the report runs it simply displays #error.
As an alternative I've tried to load the result of the query into a
variable and then reference the variable in the control source of the
text box, but when I do that, nothing seems to happen.

I'm sort of new to VBA, so if anyone has suggestions about how to do
this or a simpler way, I'd appreciate the input.

Thanks, Bob


As long as the query returns just one value:

=DlookUp("[Office_total]","[Qrydetsum_offtotal]")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.