Call report based on UDF with parameters

[masteraccess2008]

I create in Access report based on user defined function(UDF) in SQL Server which returns table.

CREATE FUNCTION MyFunc_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN (SELECT......

In Access, I am calling a report with DoCmd.OpenRepor t method.

DOCMD.OpenRepor t stDocName, acPreview

How to call report passing a parameter from form (Forms!FormName !TextBox)
(solution with where clause doesn't match my problem).

[JConsulting]

have you tried using the report's filter option?

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

wherecondition = A string expression that's a valid SQL WHERE clause without the word WHERE.

DOCMD.OpenReport stDocName, acPreview,,"[somefield] = " & Forms!FormName!TextBox

[masteraccess2008]

have you tried using the report's filter option?

DoCmd.OpenRepor t reportname[, view][, filtername][, wherecondition]

wherecondition = A string expression that's a valid SQL WHERE clause without the word WHERE.



DOCMD.OpenRepor t stDocName, acPreview,,"[somefield] = " & Forms!FormName! TextBox

Thank you, but..
in Access Report i use user defined function(UDF) instead of query. Parameters in UDF has no relation with where part of SQL statement, so your solution doens't work for me. Is the any other way to pass parameters calling report?

[NeoPa]

Can you show what you are using.
I can't understand why the Filter would not work, even for a T-SQL UDF.

You have tried it haven't you?

[masteraccess2008]

Can you show what you are using.
I can't understand why the Filter would not work, even for a T-SQL UDF.

You have tried it haven't you?

My steps were as follows:
1. Create in SQL Server 2000 UDF:
CREATE FUNCTION TEMP_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN.......
2. In Access open new project using existing data
3. Create Report named "MyReport" based on function TEMP_VP
When i Start this report program ask me: Enter Parameter Value / VP
4. Create new form with text field "FormField" and call report from this form:
Dim stDocName As String
Dim WhereC As String
stDocName = "MyReport"
WhereC = "[VP] = " + Form!FormField
DoCmd.OpenRepor t stDocName, acPreview, , WhereC
5. When i call report, program still ask me Enter Parameter Value/VP and reports "Invalid Column Name VP".

How to solve this problem?

[NeoPa]

When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?

What is some example data for [VP]?

[masteraccess2008]

When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?

What is some example data for [VP]?

TEMP_VP is really very simple query:

CREATE FUNCTION TEMP_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN (SELECT MAX(KODA_VP) AS KODA_vp,MIN(PRICE) AS MINp,MAx(PRICE) AS Maxp FROM SJ_PRICES WHERE KODA_VP=@vp)

example of data for VP: "GOOG"

This function is prepared only for this forum, in real solution function is much more complex..

[NeoPa]

I'd really much rather you answered my question than giving me some SQL to look through - especially T-SQL.

However, from looking at your code I can see two major problems with your test of the filter (WhereC = "[VP] = " + Form!FormField) :

1. [VP] is not a field that is returned in your function (SQL).
   You have fields called [KODA_vp], [MINp] & [Maxp] only.
2. [VP] is a text field so the filter would need to include single quotes around the value.
   

   WhereC = "[VP] = '" + Form!FormField & "'"

I would suggest a filter is still the best option for you. I'd be happy to help further if you have any questions.

[masteraccess2008]

I'd really much rather you answered my question than giving me some SQL to look through - especially T-SQL.

However, from looking at your code I can see two major problems with your test of the filter (WhereC = "[VP] = " + Form!FormField) :

1. [VP] is not a field that is returned in your function (SQL).
   You have fields called [KODA_vp], [MINp] & [Maxp] only.
2. [VP] is a text field so the filter would need to include single quotes around the value.
   

   WhereC = "[VP] = '" + Form!FormField & "'"

I would suggest a filter is still the best option for you. I'd be happy to help further if you have any questions.

Answers to your questions:
Query returns: KRKG 6.21 126.58
Value of Parameter: 'GOOG'

I ran code just like you suggest:

 Dim WhereC As String
    stDocName = "MyReport"
    WhereC = "[VP] = '" + Form!FormField & "'"
    DoCmd.OpenReport stDocName, acPreview, WhereC

When I run this code (Form!FormField is of course filled in) program stil ask me "Enter Parameter Value".
I'm very unhappy about this.

[NeoPa]

You have fixed the problem numbered #2 in my post but #1 is still an issue for you. You are trying to select on a field ([VP]) which doesn't exist as far as I can tell.

When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?

What is some example data for [VP]?

This is a quote of my post (#6) where I asked the questions. I am actually interested in the column names rather than any example data in them.

The second question you've already answered quite adequately.
The first I think I know from your posted SQL but it would be interesting to have this confirmed.

[masteraccess2008]

Example how to use this function:

SELECT * FROm TEMP_VP('GOOG')

Result of this function is table:
koda_vp mINp mAXp
GOOG 100.01 741.79


Code of this function Is:

CREATE FUNCTION TEMP_VP (@VP varchar(12))
RETURNS TABLE
AS
RETURN (SELECT MAX(KODA_VP) AS KODA_vp,MIN(PRICE) AS MINp,MAx(PRICE) AS Maxp FROM SJ_PRICES WHERE KODA_VP=@vp)

@VP is a parameter of function and don't understand your first question....

[NeoPa]

You have now responded to my first question by giving the column names from the results of the function.

Now I understand your situation better, I can say that you have quite a tricky problem. I assume that redesigning the T-SQL end is not an option.

I think the only way to pass a parameter across to a T-SQL UDF on a SQL Server is via a Pass-Thru query. I suspect you will need to set one up as a QueryDef and see if the form control reference will work in a Pass-Thru query.

It's a bit of a weird design, but I expect it was part of the system you were given to work within, so we just have to de the best we can to find a way through it.

Let us know how you get on with this idea.

[masteraccess2008]

You have now responded to my first question by giving the column names from the results of the function.

Now I understand your situation better, I can say that you have quite a tricky problem. I assume that redesigning the T-SQL end is not an option.

I think the only way to pass a parameter across to a T-SQL UDF on a SQL Server is via a Pass-Thru query. I suspect you will need to set one up as a QueryDef and see if the form control reference will work in a Pass-Thru query.

It's a bit of a weird design, but I expect it was part of the system you were given to work within, so we just have to de the best we can to find a way through it.

Let us know how you get on with this idea.

Thank you very much. It finally works.

Solution is here:
define global variable:
1)

Global GlobalVar As String
Option Explicit
Option Compare Database

2)
call report from my form:

  
  GlobalVar = Form!FormField
    DoCmd.OpenReport stDocName, acPreview

3) delete property RecordSource on report and put this code:

Private Sub Report_Open(Cancel As Integer)
Dim K As String
    Dim strRecordSource As String
    strRecordSource = "select Koda_VP,MinP,MaxP from temp_VP('" + GlobalVar + "')"
    Me.RecordSource = strRecordSource
End Sub

[NeoPa]

No worries. I'm glad you managed to get that working.

In truth, I hesitated to suggest the Report Open procedure to change the RecordSource, but it seems you not only "got" that, but managed it competently on your own :)

I would make a couple of relatively unimportant points that you can regard as tips :

• Try to make the Option lines the first lines of all your modules for consistency.
• The Global keyword is still supported but Public is recommended going forward.
• The report is perfectly able to get the parameter directly from the form if required (A Public variable is not even required).
• Alternatively you can pass the parameter via OpenArgs.
• Using "+" as a concatenation character should probably only be used if Null cancellation is required. As a general rule I would use "&" instead.

I will give examples of how to do this by passing the parameter in the open call.

DoCmd.OpenReport stDocName, acPreview, , , , Form!FormField

Private Sub Report_Open(Cancel As Integer)
    Dim strParam As String

    'Me.OpenArgs MUST be processed first as it is reset after other code is run
    If Not IsNull(Me.OpenArgs) Then strParam = Me.OpenArgs
    Me.RecordSource = "SELECT Koda_VP,MinP,MaxP FROM temp_VP('" & strParam & "')"
End Sub