Inconsistant behavior passing InputParameters to a TVF

  • Thread starter Thread starter BSy
  • Start date Start date
B

BSy

Hi. Any help appreciated.

I converted an Access 2007 .accdb to an .adp database. A number of the
queries converted to TVF. My attempt to convert one of them to deal with
parameters, shown under “TVF:†below, works fine when run from the Queries
Navigation Pane in Access 2007 – user is prompted for the parameters.

Upon opening a report that has its Record Source set, on the Data tab, to
[qrAll_Analysts_Report] and Record Source Qualifier set to dbo and
InputParameters set as indicated in the Report_Open event proc below,
inconsistent behavior occurs. The inconsistent behavior noticed is:

1. Upon running the application, the first call to the report yields correct
selection for the dates passed.
2. Subsequent calls, passing other dates, yields selection based upon first
set of dates passed. New dates seem to be ignored.
3. Switching the report to design view and back to report view yields
correct selection for dates passed.

Visual Basic:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sDates As String
Dim sSDate As String
Dim sEDate As String

'receive arguments
sDates = Me.OpenArgs

'parse out string token date parameters and wrap for literal string
sSDate = "#" & GetToken(sDates, 1, ";") & "#"
sEDate = "#" & GetToken(sDates, 2, ";") & "#"

'Append to pass both dates
sDates = sSDate & ", " & sEDate

'load dates parameters
Me.InputParameters = sDates
'Me.InputParameters = "#9/4/2008#, #9/5/2008#"

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Report_Open
End Sub

TVF:
CREATE FUNCTION [dbo].[qrAll_Analysts_Report]
(@SDate datetime,
@EDate datetime)
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT
dbo.tbLookup_Employee.empFName + ' ' + dbo.tbLookup_Employee.empLName
AS Analyst
, dbo.tbPIRCatalog.Done
, dbo.tbPIRCatalog.Req
, dbo.tbPIRCatalog.Loc
, dbo.tbPIRCatalog.Requester
, dbo.tbPIRCatalog.Description
, dbo.tbLookup_Employee.empInitials + ': ' + dbo.tbLookup_Employee.empFName
+ ' ' + dbo.tbLookup_Employee.empLName AS AnalystName
, dbo.tbLookup_Employee.Hide
FROM
dbo.tbLookup_Employee INNER JOIN
dbo.tbPIRCatalog ON
dbo.tbLookup_Employee.ID_Emp = dbo.tbPIRCatalog.ID_Emp_fk
WHERE
(dbo.tbLookup_Employee.Hide = 0)
AND
(dbo.tbPIRCatalog.Done BETWEEN @SDate AND @EDate)
ORDER BY Analyst, dbo.tbPIRCatalog.Done )
 
You cannot use the Report_Open() to modify the InputParameters property (or
local variables to be referenced by the data source) because the data source
of a report is queried before the report is opened; so in essence, it's
always too late at this stage.

Convert your TVFs to stored procedures, set these SP as the reports data
source and set the InputParameters to point toward controls (or public
variables) of an open form; something in the line of:

@IdRoute int = Forms!d_Rapports!ComboIdRoute, ....

For Access 2007, I don't know but for some previous versions of Access (like
A2000), these parameters must be defined in the InputParameters property
with exactly the same order as they are on the SP. (Don't really know if
this is still necessary but I'm always doing it this way to be sure.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


BSy said:
Hi. Any help appreciated.

I converted an Access 2007 .accdb to an .adp database. A number of the
queries converted to TVF. My attempt to convert one of them to deal with
parameters, shown under "TVF:" below, works fine when run from the Queries
Navigation Pane in Access 2007 - user is prompted for the parameters.

Upon opening a report that has its Record Source set, on the Data tab, to
[qrAll_Analysts_Report] and Record Source Qualifier set to dbo and
InputParameters set as indicated in the Report_Open event proc below,
inconsistent behavior occurs. The inconsistent behavior noticed is:

1. Upon running the application, the first call to the report yields
correct
selection for the dates passed.
2. Subsequent calls, passing other dates, yields selection based upon
first
set of dates passed. New dates seem to be ignored.
3. Switching the report to design view and back to report view yields
correct selection for dates passed.

Visual Basic:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sDates As String
Dim sSDate As String
Dim sEDate As String

'receive arguments
sDates = Me.OpenArgs

'parse out string token date parameters and wrap for literal string
sSDate = "#" & GetToken(sDates, 1, ";") & "#"
sEDate = "#" & GetToken(sDates, 2, ";") & "#"

'Append to pass both dates
sDates = sSDate & ", " & sEDate

'load dates parameters
Me.InputParameters = sDates
'Me.InputParameters = "#9/4/2008#, #9/5/2008#"

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Report_Open
End Sub

TVF:
CREATE FUNCTION [dbo].[qrAll_Analysts_Report]
(@SDate datetime,
@EDate datetime)
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT
dbo.tbLookup_Employee.empFName + ' ' + dbo.tbLookup_Employee.empLName
AS Analyst
, dbo.tbPIRCatalog.Done
, dbo.tbPIRCatalog.Req
, dbo.tbPIRCatalog.Loc
, dbo.tbPIRCatalog.Requester
, dbo.tbPIRCatalog.Description
, dbo.tbLookup_Employee.empInitials + ': ' +
dbo.tbLookup_Employee.empFName
+ ' ' + dbo.tbLookup_Employee.empLName AS AnalystName
, dbo.tbLookup_Employee.Hide
FROM
dbo.tbLookup_Employee INNER JOIN
dbo.tbPIRCatalog ON
dbo.tbLookup_Employee.ID_Emp = dbo.tbPIRCatalog.ID_Emp_fk
WHERE
(dbo.tbLookup_Employee.Hide = 0)
AND
(dbo.tbPIRCatalog.Done BETWEEN @SDate AND @EDate)
ORDER BY Analyst, dbo.tbPIRCatalog.Done )
Click to expand...
 
I now understand the problem as you've explained it, thank you. I was also
exploring how to use a TVF with a ADODB.Recordset but I continue to receive
this error running the VB segment shown below.

“The request for procedure ‘qrALL_Analysts_Report’ failed because
‘qrALL_Analysts_Report’ is a table valued function objectâ€

Dim rs As ADODB.Recordset
Dim sTr As String
Dim oReport As Report

sTr = "EXEC qrAll_Analysts_Report @SDate='" & Me!StartDate & "', @Edate='" &
Me!EndDate & "'"

‘Also tried with same error result on line rs.Open sTr,…:
‘ sTr = [qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"
‘ sTr = "[qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"
‘ sTr = "[dbo].[qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"

rs.Open sTr, CurrentProject.Connection, adOpenStatic, adLockReadOnly

So, if the function returns a set of table records, why can't a recordset be
established?

Sylvain Lafontaine said:
You cannot use the Report_Open() to modify the InputParameters property (or
local variables to be referenced by the data source) because the data source
of a report is queried before the report is opened; so in essence, it's
always too late at this stage.

Convert your TVFs to stored procedures, set these SP as the reports data
source and set the InputParameters to point toward controls (or public
variables) of an open form; something in the line of:

@IdRoute int = Forms!d_Rapports!ComboIdRoute, ....

For Access 2007, I don't know but for some previous versions of Access (like
A2000), these parameters must be defined in the InputParameters property
with exactly the same order as they are on the SP. (Don't really know if
this is still necessary but I'm always doing it this way to be sure.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


BSy said:
Hi. Any help appreciated.

I converted an Access 2007 .accdb to an .adp database. A number of the
queries converted to TVF. My attempt to convert one of them to deal with
parameters, shown under "TVF:" below, works fine when run from the Queries
Navigation Pane in Access 2007 - user is prompted for the parameters.

Upon opening a report that has its Record Source set, on the Data tab, to
[qrAll_Analysts_Report] and Record Source Qualifier set to dbo and
InputParameters set as indicated in the Report_Open event proc below,
inconsistent behavior occurs. The inconsistent behavior noticed is:

1. Upon running the application, the first call to the report yields
correct
selection for the dates passed.
2. Subsequent calls, passing other dates, yields selection based upon
first
set of dates passed. New dates seem to be ignored.
3. Switching the report to design view and back to report view yields
correct selection for dates passed.

Visual Basic:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sDates As String
Dim sSDate As String
Dim sEDate As String

'receive arguments
sDates = Me.OpenArgs

'parse out string token date parameters and wrap for literal string
sSDate = "#" & GetToken(sDates, 1, ";") & "#"
sEDate = "#" & GetToken(sDates, 2, ";") & "#"

'Append to pass both dates
sDates = sSDate & ", " & sEDate

'load dates parameters
Me.InputParameters = sDates
'Me.InputParameters = "#9/4/2008#, #9/5/2008#"

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Report_Open
End Sub

TVF:
CREATE FUNCTION [dbo].[qrAll_Analysts_Report]
(@SDate datetime,
@EDate datetime)
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT
dbo.tbLookup_Employee.empFName + ' ' + dbo.tbLookup_Employee.empLName
AS Analyst
, dbo.tbPIRCatalog.Done
, dbo.tbPIRCatalog.Req
, dbo.tbPIRCatalog.Loc
, dbo.tbPIRCatalog.Requester
, dbo.tbPIRCatalog.Description
, dbo.tbLookup_Employee.empInitials + ': ' +
dbo.tbLookup_Employee.empFName
+ ' ' + dbo.tbLookup_Employee.empLName AS AnalystName
, dbo.tbLookup_Employee.Hide
FROM
dbo.tbLookup_Employee INNER JOIN
dbo.tbPIRCatalog ON
dbo.tbLookup_Employee.ID_Emp = dbo.tbPIRCatalog.ID_Emp_fk
WHERE
(dbo.tbLookup_Employee.Hide = 0)
AND
(dbo.tbPIRCatalog.Done BETWEEN @SDate AND @EDate)
ORDER BY Analyst, dbo.tbPIRCatalog.Done )
Click to expand...
Click to expand...
 
A function cannot return data over the wire because it cannot return a
resultset and only a resultset can be returned over the wire. Use a Select
statement or hide your TVF inside a SP:

sTr = "Select * from qrAll_Analysts_Report ('" & Me!StartDate & "', '" &
Me!EndDate & "'")

Add an Order By statement at the end because the Order By statement inside
the TVF has no significance.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


BSy said:
I now understand the problem as you've explained it, thank you. I was also
exploring how to use a TVF with a ADODB.Recordset but I continue to
receive
this error running the VB segment shown below.

"The request for procedure 'qrALL_Analysts_Report' failed because
'qrALL_Analysts_Report' is a table valued function object"

Dim rs As ADODB.Recordset
Dim sTr As String
Dim oReport As Report

sTr = "EXEC qrAll_Analysts_Report @SDate='" & Me!StartDate & "', @Edate='"
&
Me!EndDate & "'"

'Also tried with same error result on line rs.Open sTr,.:
' sTr = [qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"
' sTr = "[qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"
' sTr = "[dbo].[qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"

rs.Open sTr, CurrentProject.Connection, adOpenStatic, adLockReadOnly

So, if the function returns a set of table records, why can't a recordset
be
established?

Sylvain Lafontaine said:
You cannot use the Report_Open() to modify the InputParameters property
(or
local variables to be referenced by the data source) because the data
source
of a report is queried before the report is opened; so in essence, it's
always too late at this stage.

Convert your TVFs to stored procedures, set these SP as the reports data
source and set the InputParameters to point toward controls (or public
variables) of an open form; something in the line of:

@IdRoute int = Forms!d_Rapports!ComboIdRoute, ....

For Access 2007, I don't know but for some previous versions of Access
(like
A2000), these parameters must be defined in the InputParameters property
with exactly the same order as they are on the SP. (Don't really know if
this is still necessary but I'm always doing it this way to be sure.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


BSy said:
Hi. Any help appreciated.

I converted an Access 2007 .accdb to an .adp database. A number of the
queries converted to TVF. My attempt to convert one of them to deal
with
parameters, shown under "TVF:" below, works fine when run from the
Queries
Navigation Pane in Access 2007 - user is prompted for the parameters.

Upon opening a report that has its Record Source set, on the Data tab,
to
[qrAll_Analysts_Report] and Record Source Qualifier set to dbo and
InputParameters set as indicated in the Report_Open event proc below,
inconsistent behavior occurs. The inconsistent behavior noticed is:

1. Upon running the application, the first call to the report yields
correct
selection for the dates passed.
2. Subsequent calls, passing other dates, yields selection based upon
first
set of dates passed. New dates seem to be ignored.
3. Switching the report to design view and back to report view yields
correct selection for dates passed.

Visual Basic:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sDates As String
Dim sSDate As String
Dim sEDate As String

'receive arguments
sDates = Me.OpenArgs

'parse out string token date parameters and wrap for literal string
sSDate = "#" & GetToken(sDates, 1, ";") & "#"
sEDate = "#" & GetToken(sDates, 2, ";") & "#"

'Append to pass both dates
sDates = sSDate & ", " & sEDate

'load dates parameters
Me.InputParameters = sDates
'Me.InputParameters = "#9/4/2008#, #9/5/2008#"

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Report_Open
End Sub

TVF:
CREATE FUNCTION [dbo].[qrAll_Analysts_Report]
(@SDate datetime,
@EDate datetime)
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT
dbo.tbLookup_Employee.empFName + ' ' +
dbo.tbLookup_Employee.empLName
AS Analyst
, dbo.tbPIRCatalog.Done
, dbo.tbPIRCatalog.Req
, dbo.tbPIRCatalog.Loc
, dbo.tbPIRCatalog.Requester
, dbo.tbPIRCatalog.Description
, dbo.tbLookup_Employee.empInitials + ': ' +
dbo.tbLookup_Employee.empFName
+ ' ' + dbo.tbLookup_Employee.empLName AS AnalystName
, dbo.tbLookup_Employee.Hide
FROM
dbo.tbLookup_Employee INNER JOIN
dbo.tbPIRCatalog ON
dbo.tbLookup_Employee.ID_Emp = dbo.tbPIRCatalog.ID_Emp_fk
WHERE
(dbo.tbLookup_Employee.Hide = 0)
AND
(dbo.tbPIRCatalog.Done BETWEEN @SDate AND @EDate)
ORDER BY Analyst, dbo.tbPIRCatalog.Done )
Click to expand...
Click to expand...
Click to expand...
 
Once again thank you.

Sylvain Lafontaine said:
A function cannot return data over the wire because it cannot return a
resultset and only a resultset can be returned over the wire. Use a Select
statement or hide your TVF inside a SP:

sTr = "Select * from qrAll_Analysts_Report ('" & Me!StartDate & "', '" &
Me!EndDate & "'")

Add an Order By statement at the end because the Order By statement inside
the TVF has no significance.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


BSy said:
I now understand the problem as you've explained it, thank you. I was also
exploring how to use a TVF with a ADODB.Recordset but I continue to
receive
this error running the VB segment shown below.

"The request for procedure 'qrALL_Analysts_Report' failed because
'qrALL_Analysts_Report' is a table valued function object"

Dim rs As ADODB.Recordset
Dim sTr As String
Dim oReport As Report

sTr = "EXEC qrAll_Analysts_Report @SDate='" & Me!StartDate & "', @Edate='"
&
Me!EndDate & "'"

'Also tried with same error result on line rs.Open sTr,.:
' sTr = [qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"
' sTr = "[qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"
' sTr = "[dbo].[qrAll_Analysts_Report] @SDate='" & Me!StartDate & "',
@Edate='" & Me!EndDate & "'"

rs.Open sTr, CurrentProject.Connection, adOpenStatic, adLockReadOnly

So, if the function returns a set of table records, why can't a recordset
be
established?

Sylvain Lafontaine said:
You cannot use the Report_Open() to modify the InputParameters property
(or
local variables to be referenced by the data source) because the data
source
of a report is queried before the report is opened; so in essence, it's
always too late at this stage.

Convert your TVFs to stored procedures, set these SP as the reports data
source and set the InputParameters to point toward controls (or public
variables) of an open form; something in the line of:

@IdRoute int = Forms!d_Rapports!ComboIdRoute, ....

For Access 2007, I don't know but for some previous versions of Access
(like
A2000), these parameters must be defined in the InputParameters property
with exactly the same order as they are on the SP. (Don't really know if
this is still necessary but I'm always doing it this way to be sure.).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Hi. Any help appreciated.

I converted an Access 2007 .accdb to an .adp database. A number of the
queries converted to TVF. My attempt to convert one of them to deal
with
parameters, shown under "TVF:" below, works fine when run from the
Queries
Navigation Pane in Access 2007 - user is prompted for the parameters.

Upon opening a report that has its Record Source set, on the Data tab,
to
[qrAll_Analysts_Report] and Record Source Qualifier set to dbo and
InputParameters set as indicated in the Report_Open event proc below,
inconsistent behavior occurs. The inconsistent behavior noticed is:

1. Upon running the application, the first call to the report yields
correct
selection for the dates passed.
2. Subsequent calls, passing other dates, yields selection based upon
first
set of dates passed. New dates seem to be ignored.
3. Switching the report to design view and back to report view yields
correct selection for dates passed.

Visual Basic:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
Dim sDates As String
Dim sSDate As String
Dim sEDate As String

'receive arguments
sDates = Me.OpenArgs

'parse out string token date parameters and wrap for literal string
sSDate = "#" & GetToken(sDates, 1, ";") & "#"
sEDate = "#" & GetToken(sDates, 2, ";") & "#"

'Append to pass both dates
sDates = sSDate & ", " & sEDate

'load dates parameters
Me.InputParameters = sDates
'Me.InputParameters = "#9/4/2008#, #9/5/2008#"

Exit_Report_Open:
Exit Sub
Err_Report_Open:
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume Exit_Report_Open
End Sub

TVF:
CREATE FUNCTION [dbo].[qrAll_Analysts_Report]
(@SDate datetime,
@EDate datetime)
RETURNS TABLE
AS
RETURN
(SELECT TOP 100 PERCENT
dbo.tbLookup_Employee.empFName + ' ' +
dbo.tbLookup_Employee.empLName
AS Analyst
, dbo.tbPIRCatalog.Done
, dbo.tbPIRCatalog.Req
, dbo.tbPIRCatalog.Loc
, dbo.tbPIRCatalog.Requester
, dbo.tbPIRCatalog.Description
, dbo.tbLookup_Employee.empInitials + ': ' +
dbo.tbLookup_Employee.empFName
+ ' ' + dbo.tbLookup_Employee.empLName AS AnalystName
, dbo.tbLookup_Employee.Hide
FROM
dbo.tbLookup_Employee INNER JOIN
dbo.tbPIRCatalog ON
dbo.tbLookup_Employee.ID_Emp = dbo.tbPIRCatalog.ID_Emp_fk
WHERE
(dbo.tbLookup_Employee.Hide = 0)
AND
(dbo.tbPIRCatalog.Done BETWEEN @SDate AND @EDate)
ORDER BY Analyst, dbo.tbPIRCatalog.Done )
Click to expand...
Click to expand...
Click to expand...
 
Back
Top