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.OpenReport method.
DOCMD.OpenReport stDocName, acPreview
How to call report passing a parameter from form (Forms!FormName!TextBox)
(solution with where clause doesn't match my problem).
13 3203
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
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
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 32,556
Recognized Expert Moderator MVP
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?
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.OpenReport 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 32,556
Recognized Expert Moderator MVP
When you run TEMP_VP as a simple Access query, what columns (exactly) are returned?
What is some example data for [VP]?
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 32,556
Recognized Expert Moderator MVP
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) : - [VP] is not a field that is returned in your function (SQL).
You have fields called [KODA_vp], [MINp] & [Maxp] only. - [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.
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) :- [VP] is not a field that is returned in your function (SQL).
You have fields called [KODA_vp], [MINp] & [Maxp] only. - [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 32,556
Recognized Expert Moderator MVP
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.
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 32,556
Recognized Expert Moderator MVP
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.
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 32,556
Recognized Expert Moderator MVP
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Fred Zuckerman |
last post by:
I have 8 reports that I want to run. Each report is only about 6-7 lines
long. I'd like to get them all on a single page.
So I created a parent report and made each of the 8 desired reports a...
|
by: Mike Conklin |
last post by:
This one really has me going. Probably something silly. I'm using
dcount for a report to determine the number of different types of
tests proctored in a semester.
My report is based on a...
|
by: Richard Hollenbeck |
last post by:
I tried to ask this question before on the 14th of January but I never got a
reply. I'm still struggling with the problem. I'll try to rephrase the
question:
I have a crosstab query with rows...
|
by: lauren quantrell |
last post by:
When I open an Access form I can have no recordset specified, then in
the form's OnOpen event I can do something like:
Me.paramaters = "@SomeColumn = 22)"
Me.recordsource = "dbo.sproc123"
But I...
| |
by: Bryan |
last post by:
I've been looking over this newsgroup, but I can't find an answer to my
problem. I see that a few others have posted this issue over the
years, but none of them got a response. I'll give it...
|
by: nqtrung |
last post by:
Hi all!
I want to create a report generator: The user should enter in a web page the parameters for the report (using JSP) and press 'Generate report'. As the result: the right data should be...
|
by: dana1 |
last post by:
Hello Experts!
Does anyone know if there is a way to set the values of query parameters from VBA for a report's recordsource? (i.e., I want to set the values of the parameters and NOT have the...
|
by: Richard Hollenbeck |
last post by:
I am very sorry about the (almost) re-post, but you will see that my first
question wasn't very clear; I have another question I posted this morning
called, "in DAO: Run time error 3061 Too few...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |