Solved

SQL Server Scalar UDF as report textbox controlsource

Posted on 2013-01-10
3
593 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 142

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

773 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