SQL as Textbox Control source

[Guest]

According to the help files, a Textbox can have a ControlSource of an SQL
statement.

I have created my SQL query and inserted it into the textboxes control source:

=(SELECT Count(MPR) FROM zSYS_tblMPR_Tracker WHERE
Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue] AND
[Complete?]=False

When I open the form though, the text box just displays #Name?

Can anyone clarify things?

Cheers,
Steve

 

[Stefan Hoffmann]

hi,

According to the help files, a Textbox can have a ControlSource of an SQL
statement.

This cannot work. The control source must be either a constant, a
function or a field.

I have created my SQL query and inserted it into the textboxes control source:

=(SELECT Count(MPR) FROM zSYS_tblMPR_Tracker WHERE
Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue] AND
[Complete?]=False

Use
=DCount("MPR","zSYS_tblMPR_Tracker","Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue]
AND [Complete?]=False")


mfG
--> stefan <--

 

[Guest]

Thanks for the reply.

I have tried this, but am now getting #Error? in the textbox instead!!!

The form that should display the total is within a sub form
(subForm_Display) on the Form zSYS_frmWorkflow, could this be affecting
things?

Another note is if I open the form on its own, I get the #Error, when
opening it through the 'main' form, it just seems to hang and I have to end
task.....

Thanks,
Steve.

hi,

According to the help files, a Textbox can have a ControlSource of an SQL
statement.

This cannot work. The control source must be either a constant, a
function or a field.

I have created my SQL query and inserted it into the textboxes control source:

=(SELECT Count(MPR) FROM zSYS_tblMPR_Tracker WHERE
Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue] AND
[Complete?]=False

Use
=DCount("MPR","zSYS_tblMPR_Tracker","Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue]
AND [Complete?]=False")


mfG
--> stefan <--

 

[OfficeDev18 via AccessMonster.com]

Steve,

The reason the statement failed is because the field you selected was left
dangling. In other words, the field had no name, which is why you got the
#Name error. Try giving the field a name, like this:

=(SELECT Count(MPR) As CountOfMPR FROM zSYS_tblMPR_Tracker WHERE
Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue] AND
[Complete?]=False

Hope this helps,

Sam

hi,

According to the help files, a Textbox can have a ControlSource of an SQL
statement.

This cannot work. The control source must be either a constant, a
function or a field.

I have created my SQL query and inserted it into the textboxes control source:

=(SELECT Count(MPR) FROM zSYS_tblMPR_Tracker WHERE
Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue] AND
[Complete?]=False

Use
=DCount("MPR","zSYS_tblMPR_Tracker","Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue]
AND [Complete?]=False")

mfG
--> stefan <--