Solved

SQL Server Scalar UDF as report textbox controlsource

Posted on 2013-01-10
3
608 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

740 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