Solved

SQL Server Scalar UDF as report textbox controlsource

Posted on 2013-01-10
3
600 Views
Last Modified: 2013-01-10
I have a Scalar UDF in SQL Server that returns a single value for a passed parameter.

I have an MS Access mdb that uses that links to the SQL Server.

I have a report where I want to bind a textbox to the UDF. Is that possible?

If not, how would I write a VBA function that calls the UDF and returns the value?

While waiting for an answer, I am going to look to see if I'm able to just include the UDF in the SQL Server recordsource that the report is already using. That would be a much cleaner solution. But, in case I can't do that, I'd like an answer to the above.

Thanks!
0
Comment
Question by:Kaprice
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38762115
>I want to bind a textbox to the UDF.
as you need some parameter, I don't see how that could work out.
also, a textbox is for "data input", while UDF refers to "data display only", so you eventually want to say "label" instead, or at least a read-only textbox.

>If not, how would I write a VBA function that calls the UDF and returns the value?
like any other sql query you would run towards the sql server:
select dbo.Your_UDF(<parameter)
the question is: do you already have the connection part in the VBA? what exactly would you need there?

>I am going to look to see if I'm able to just include the UDF in the SQL Server recordsource that the report is already using.
sure. you can add a UDF function in a select
0
 

Author Comment

by:Kaprice
ID: 38763720
as you need some parameter, I don't see how that could work out.
 
Well, it would be very straightforward if it were an Access function. In the control's controlsource, I would put "=myfunction(txtOtherControl)"
a textbox is for "data input", while UDF refers to "data display only", so you eventually want to say "label" instead, or at least a read-only textbox.
They still call it a textbox in  a report, even if it's for display only. At least I use the same texbox control from the toolbox.
select dbo.Your_UDF(<parameter)
If adding it directly to the report's recordsource, I'll try this.
I'll report back later today.
Thanks.
0
 

Author Closing Comment

by:Kaprice
ID: 38766147
I was able to add the function to the select statement in SQL Server that was the report's recordsource.

Expert's answer gave me confidence with options to try.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question