Passthru query as control source

  • Thread starter Thread starter TomT
  • Start date Start date
T

TomT

Is there a way to set a passthrough query as the control
source for an unbound textbox?

I thought this would be simple, but I can't get it to
work. I've used a select statement as the control source,
but always get a #Name result, even when using another,
non-passthrough query in the select statement.

Any help would be greatly appreciated..

TT
 
TomT said:
Is there a way to set a passthrough query as the control
source for an unbound textbox?

I thought this would be simple, but I can't get it to
work. I've used a select statement as the control source,
but always get a #Name result, even when using another,
non-passthrough query in the select statement.

Any help would be greatly appreciated..
Click to expand...

A ControlSource must return a single Value. That is why you can't use a
query. While your particular query might only produce a single value
Access has no way of knowing that. Use DLookup() as a ControlSource to
retrieve the value from the query.
 
Hello Tom,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your description, I understand that you set a pass-through query as the control source for
an unbound textbox, but you got the #Name result.

The #Name error means Access doesn't recognize the control source property as a valid
column or expression. I am wondering if the pass-through query returns varied columns and
causes this problem. For dynamic column names, we may need to write some code to update
the control source property accordingly.

Although I'm unsure of the scenario on your side, I also agree with Rick that you can try to use
functions like DLookup() to retrieve the value. If it doesn't solve your problem, please specify
detailed information so that we can easily narrow down the problem and provide a quick and
efficient resolution. Thanks in advance!

If there is anything I can do to assist you, please don't hesitate to let us know.

Best regards,

Billy Yao
Microsoft Online Support
 
thanks very much for your responses. The passthrough
query returns on column, always text (it's based on an
sql stored procedure which builds and returns an address).

The output is always, e.g.

Name
Address
City, State zip

The pt query is working fine, just can't use it for the
unbound control. I hadn't tried this kind of thing before
for a text box, but thought it might work.

I'll try the DLookup() suggestion (as much as I try to
avoid those domain functions).

Thanks
-----Original Message-----
Hello Tom,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this issue.

From your description, I understand that you set a pass-
Click to expand...
through query as the control source for
an unbound textbox, but you got the #Name result.

The #Name error means Access doesn't recognize the
Click to expand...
control source property as a valid
column or expression. I am wondering if the pass-through
Click to expand...
query returns varied columns and
causes this problem. For dynamic column names, we may
Click to expand...
need to write some code to update
the control source property accordingly.

Although I'm unsure of the scenario on your side, I also
Click to expand...
agree with Rick that you can try to use
functions like DLookup() to retrieve the value. If it
Click to expand...
doesn't solve your problem, please specify
detailed information so that we can easily narrow down
Click to expand...
the problem and provide a quick and
 
Hi Tom,

Thank you for your update!

I understand that the pass-through query works fine on your side but when applied to the
unbound box as a Control Source, the content cannot be display normally.

I perform a further testing on my side. I was able to create a pass-through query in Access
"exec PTQry" which the stored procedure PTQry is defined (in SQL Server) as below:

---------------------------------------------------------
CREATE PROCEDURE [PTQry] AS

Set NOCOUNT ON
Select FirstName, LastName, Address, City, Postalcode from Employees

GO
---------------------------------------------------------

The pass-through query runs fine and returns the expected resultset.

When I bind the query to a form (Record Source: PTQry), the textbox Control Source can then
show all the columns returned from the query - from the stored procedure. I can use these
exact column names and the column content displays normally in the textbox.

Tom, I'm wondering if you have bound the pass-through query to your form, or done
something in the stored procedure so that it may return invalid resultset. If there is something
difference between our behaviors, please feel free to let me know.

For more information on using a SQL pass-through query, please search the Help index on
"SQL pass-through query" and choose topic " Create a pass-through query that sends
commands to an SQL database "

You may use a simple SQL pass-through query rather than store procedure to narrow down
and troubleshoot this problem. For your reference, see the following steps:

1. Create a new Select Query
2. Add your table to the query
3. Choose from the menu Query | SQL Specific | Pass Through
(For example : Select FirstName, LastName, Address, City, Postalcode from Employees)
4. Save the Query
5. Open the Query and connect to your DSN if it has been created, else create a DSN to your
database.

Tom, please apply my suggestions and let me know if this helps resolve your problem. If
there is anything more I can do to assist you, please feel free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Support
 
Back
Top