SQL as Textbox Control source

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

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
 
hi,
According to the help files, a Textbox can have a ControlSource of an SQL
statement.
Click to expand...
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;)
Click to expand...
Use
=DCount("MPR","zSYS_tblMPR_Tracker","Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue]
AND [Complete?]=False")


mfG
--> stefan <--
 
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.

Stefan Hoffmann said:
hi,
According to the help files, a Textbox can have a ControlSource of an SQL
statement.
Click to expand...
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;)
Click to expand...
Use
=DCount("MPR","zSYS_tblMPR_Tracker","Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue]
AND [Complete?]=False")


mfG
--> stefan <--
Click to expand...
 
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

Stefan said:
hi,
According to the help files, a Textbox can have a ControlSource of an SQL
statement.
Click to expand...
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;)
Click to expand...
Use
=DCount("MPR","zSYS_tblMPR_Tracker","Queue_Type=[forms]![zSYS_frmWorkflow]![txtSelected_Queue]
AND [Complete?]=False")

mfG
--> stefan <--
Click to expand...
 
Back
Top