SQL as Textbox Control source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FBxiii - 29 Jun 2006 15:08 GMT

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

Reply to this Message

Stefan Hoffmann - 29 Jun 2006 15:25 GMT

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 <--

Reply to this Message

FBxiii - 29 Jun 2006 15:55 GMT

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,
>
[quoted text clipped - 14 lines]
> mfG
> --> stefan <--

Reply to this Message

OfficeDev18 - 29 Jun 2006 16:20 GMT

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,
>
[quoted text clipped - 14 lines]
>mfG
>--> stefan <--

Signature

Sam

Reply to this Message