Making a Query a Control Source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created the query below that generates a sum that I am looking for:

SELECT Sum(tblPayments.PaySalDLCDPor) AS SumOfPaySalDLCDPor
FROM tblGrantSum INNER JOIN tblPayments ON tblGrantSum.[DLCDGrant#] =
tblPayments.[DLCDGrant#]
WHERE (((tblPayments.[DLCDGrant#])=[Forms].[frmProgress].[DLCDGrant#]) AND
((tblPayments.[RReq#])<[Me].[RReq#]));

When I run the query, it asks for an RReq#, I supply it and the correct
value is returned.

I would like to include this value on a form which has RReq# as a field on
the form. How do I go about this? If I paste the above SQl in the control
source of the unbound text box, I get a #Name? error. Do I need to add
concantination characters? DLCDGrant# is a string and RReq# is numeric.

Thanks.

Lloyd
 
hi,
without seeing your form, i can only make vaque
suggestions.
but according to access help you are suppost to be able to
paste an SQL statement as a control source.
you problem i think is that the query criteria and the
results of the query and on the same form which may be
presenting a timeing problem. you may need to put in a
requery statement after you enter the criteria and/or a
cancel statement on the query results box if the criteria
box is empty to prevent the query from running without the
required criteria.
 
I'm sorry, but to avoid confusion I have to point out that this is
incorrect. You can not use a SQL statement as a control source. A record
source or a row source, yes, but a control source, no.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


hi,
without seeing your form, i can only make vaque
suggestions.
but according to access help you are suppost to be able to
paste an SQL statement as a control source.
you problem i think is that the query criteria and the
results of the query and on the same form which may be
presenting a timeing problem. you may need to put in a
requery statement after you enter the criteria and/or a
cancel statement on the query results box if the criteria
box is empty to prevent the query from running without the
required criteria.
-----Original Message-----
I have created the query below that generates a sum that I am looking for:

SELECT Sum(tblPayments.PaySalDLCDPor) AS SumOfPaySalDLCDPor
FROM tblGrantSum INNER JOIN tblPayments ON tblGrantSum. [DLCDGrant#] =
tblPayments.[DLCDGrant#]
WHERE (((tblPayments.[DLCDGrant#])=[Forms].[frmProgress]. [DLCDGrant#]) AND
((tblPayments.[RReq#])<[Me].[RReq#]));

When I run the query, it asks for an RReq#, I supply it and the correct
value is returned.

I would like to include this value on a form which has RReq# as a field on
the form. How do I go about this? If I paste the above SQl in the control
source of the unbound text box, I get a #Name? error. Do I need to add
concantination characters? DLCDGrant# is a string and RReq# is numeric.

Thanks.

Lloyd
.
Click to expand...
Click to expand...
 
Thanks Brendan. Can you suggest a way to convert the Select statement into
an =Sum statement that can be included as the control source for this text
box?

Lloyd

Brendan Reynolds said:
I'm sorry, but to avoid confusion I have to point out that this is
incorrect. You can not use a SQL statement as a control source. A record
source or a row source, yes, but a control source, no.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


hi,
without seeing your form, i can only make vaque
suggestions.
but according to access help you are suppost to be able to
paste an SQL statement as a control source.
you problem i think is that the query criteria and the
results of the query and on the same form which may be
presenting a timeing problem. you may need to put in a
requery statement after you enter the criteria and/or a
cancel statement on the query results box if the criteria
box is empty to prevent the query from running without the
required criteria.
-----Original Message-----
I have created the query below that generates a sum that I am looking for:

SELECT Sum(tblPayments.PaySalDLCDPor) AS SumOfPaySalDLCDPor
FROM tblGrantSum INNER JOIN tblPayments ON tblGrantSum. [DLCDGrant#] =
tblPayments.[DLCDGrant#]
WHERE (((tblPayments.[DLCDGrant#])=[Forms].[frmProgress]. [DLCDGrant#]) AND
((tblPayments.[RReq#])<[Me].[RReq#]));

When I run the query, it asks for an RReq#, I supply it and the correct
value is returned.

I would like to include this value on a form which has RReq# as a field on
the form. How do I go about this? If I paste the above SQl in the control
source of the unbound text box, I get a #Name? error. Do I need to add
concantination characters? DLCDGrant# is a string and RReq# is numeric.

Thanks.

Lloyd
.
Click to expand...
Click to expand...
Click to expand...
 
Llyod,

I was having a similar issue and my work around was easier than I thought. I
created a query based on my SQL statement needs, then opened a form bound to
the query.



Lloyd said:
Thanks Brendan. Can you suggest a way to convert the Select statement into
an =Sum statement that can be included as the control source for this text
box?

Lloyd

Brendan Reynolds said:
I'm sorry, but to avoid confusion I have to point out that this is
incorrect. You can not use a SQL statement as a control source. A record
source or a row source, yes, but a control source, no.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


hi,
without seeing your form, i can only make vaque
suggestions.
but according to access help you are suppost to be able to
paste an SQL statement as a control source.
you problem i think is that the query criteria and the
results of the query and on the same form which may be
presenting a timeing problem. you may need to put in a
requery statement after you enter the criteria and/or a
cancel statement on the query results box if the criteria
box is empty to prevent the query from running without the
required criteria.

-----Original Message-----
I have created the query below that generates a sum that
I am looking for:

SELECT Sum(tblPayments.PaySalDLCDPor) AS
SumOfPaySalDLCDPor
FROM tblGrantSum INNER JOIN tblPayments ON tblGrantSum.
[DLCDGrant#] =
tblPayments.[DLCDGrant#]
WHERE (((tblPayments.[DLCDGrant#])=[Forms].[frmProgress].
[DLCDGrant#]) AND
((tblPayments.[RReq#])<[Me].[RReq#]));

When I run the query, it asks for an RReq#, I supply it
and the correct
value is returned.

I would like to include this value on a form which has
RReq# as a field on
the form. How do I go about this? If I paste the above
SQl in the control
source of the unbound text box, I get a #Name? error. Do
I need to add
concantination characters? DLCDGrant# is a string and
RReq# is numeric.

Thanks.

Lloyd
.
Click to expand...
Click to expand...
Click to expand...
 
You could check out the domain aggregate functions, DSum and DLookup. I'm
sorry I can't be more definite than that, this is not the kind of question
that I usually answer. I mostly stick to VBA coding questions, but I did not
like to think of you wasting your time trying to use the SQL statement as a
control source when I knew that would not work.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Lloyd said:
Thanks Brendan. Can you suggest a way to convert the Select statement
into
an =Sum statement that can be included as the control source for this text
box?

Lloyd

Brendan Reynolds said:
I'm sorry, but to avoid confusion I have to point out that this is
incorrect. You can not use a SQL statement as a control source. A record
source or a row source, yes, but a control source, no.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


hi,
without seeing your form, i can only make vaque
suggestions.
but according to access help you are suppost to be able to
paste an SQL statement as a control source.
you problem i think is that the query criteria and the
results of the query and on the same form which may be
presenting a timeing problem. you may need to put in a
requery statement after you enter the criteria and/or a
cancel statement on the query results box if the criteria
box is empty to prevent the query from running without the
required criteria.

-----Original Message-----
I have created the query below that generates a sum that
I am looking for:

SELECT Sum(tblPayments.PaySalDLCDPor) AS
SumOfPaySalDLCDPor
FROM tblGrantSum INNER JOIN tblPayments ON tblGrantSum.
[DLCDGrant#] =
tblPayments.[DLCDGrant#]
WHERE (((tblPayments.[DLCDGrant#])=[Forms].[frmProgress].
[DLCDGrant#]) AND
((tblPayments.[RReq#])<[Me].[RReq#]));

When I run the query, it asks for an RReq#, I supply it
and the correct
value is returned.

I would like to include this value on a form which has
RReq# as a field on
the form. How do I go about this? If I paste the above
SQl in the control
source of the unbound text box, I get a #Name? error. Do
I need to add
concantination characters? DLCDGrant# is a string and
RReq# is numeric.

Thanks.

Lloyd
.
Click to expand...
Click to expand...
Click to expand...
 
First, create a saved query that does what you want. (note
that you need the fully qualified control reference, not
Me.)

Once you have the query working, the text box can use
=DLookup("SumOfPaySalDLCDPor", "queryname")
--
Marsh
MVP [MS Access]


Thanks Brendan. Can you suggest a way to convert the Select statement into
an =Sum statement that can be included as the control source for this text
box?

Brendan Reynolds said:
I'm sorry, but to avoid confusion I have to point out that this is
incorrect. You can not use a SQL statement as a control source. A record
source or a row source, yes, but a control source, no.

without seeing your form, i can only make vaque
suggestions.
but according to access help you are suppost to be able to
paste an SQL statement as a control source.
you problem i think is that the query criteria and the
results of the query and on the same form which may be
presenting a timeing problem. you may need to put in a
requery statement after you enter the criteria and/or a
cancel statement on the query results box if the criteria
box is empty to prevent the query from running without the
required criteria.

-----Original Message-----
I have created the query below that generates a sum that
I am looking for:

SELECT Sum(tblPayments.PaySalDLCDPor) AS
SumOfPaySalDLCDPor
FROM tblGrantSum INNER JOIN tblPayments ON tblGrantSum.
[DLCDGrant#] =
tblPayments.[DLCDGrant#]
WHERE (((tblPayments.[DLCDGrant#])=[Forms].[frmProgress].
[DLCDGrant#]) AND
((tblPayments.[RReq#])<[Me].[RReq#]));

When I run the query, it asks for an RReq#, I supply it
and the correct
value is returned.

I would like to include this value on a form which has
RReq# as a field on
the form. How do I go about this? If I paste the above
SQl in the control
source of the unbound text box, I get a #Name? error. Do
I need to add
concantination characters? DLCDGrant# is a string and
RReq# is numeric.

Thanks.

Lloyd
.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top