Input Parameters Fail To Reach SP

  • Thread starter Thread starter MikeC
  • Start date Start date
M

MikeC

Access Version: Access 2002 SP3
Operating System: Windows XP Home Edition SP2
Front End: ADP
Back End: MSDE 2000

I'm having a problem with a report that uses a stored procedure as a
recordsource. The problem is that the user-specified input parameters are
not being passed to the stored procedure. Consequently, no records are
returned. There are no error messages. The only problem is that no data is
returned to the report.

The *stored procedure* has been independently tested using exactly the same
parameters that were used for the report and the correct records were
returned. The stored procedure is working fine.

Debug.Print shows that the input parameters are created and processed by the
report's open event procedure (below). The *report* is failing to pass the
parameters from VBA. Maybe something is wrong with the timing or the the
recordsource needs to somehow be refreshed/requeried. Is there a way to do
this?

I have also found that if the input parameters are *manually* entered into
the report's property sheet, then the records are successfully returned from
the stored procedure. However, the input parameters need to change each
time the report is generated...which is why VBA is being used. ;-)

Can anyone tell me how to make the report pass the input parameters?


P.S. I'm cross-posting to microsoft.public.access.reports and
microsoft.public.access.adp.sqlserver.


Beware of line wrapping caused by the newsreader:
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler

'This procedure sets the report's recordsource using a portion of
OpenArgs
'as the input parameters for a stored procedure.

Dim varArray As Variant

With Me
If Len(.OpenArgs) > 0 Then

'Parse OpenArgs using the "~" delimiter. The first element in
the array
'contains input parameters (delimited by ";") to be used for the
stored
'procedure. The remaining array elements delimited by "~" are
used by the
'Report_Activate event procedure.
varArray = Split(.OpenArgs, "~")

'Parse the input parameters using the ";" delimiter.
varArray = Split(CStr(varArray(0)), ";")

.InputParameters = "@ClassIDList varchar(255) = '" & varArray(0)
_
& "', @DateMin datetime = '" & varArray(1) _
& "', @DateMax datetime = '" & varArray(2) &
"'"

'This debug statement confirms that the input parameters are
correct.
Debug.Print "InputParameters = " & .InputParameters

'Set the RecordSource and RecordSourceQualifier.
.RecordSource = "stp_GetClassRosterData"
.RecordSourceQualifier = "dbo"

Else
ErrorHandler 86, "The " & .Name & " report has not been opened
correctly" _
& " and will be closed. If this problem continues, please
contact" _
& " your application administrator.", "Report_Open", .Name
Cancel = True
End If
End With

Exit_Procedure:
On Error Resume Next
varArray = Null
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
ErrorHandler Err.Number, Err.Description, "Report_Open", Me.Name
End If
Resume Exit_Procedure

End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 
MikeC said:
Access Version: Access 2002 SP3
Operating System: Windows XP Home Edition SP2
Front End: ADP
Back End: MSDE 2000

I'm having a problem with a report that uses a stored procedure as a
recordsource. The problem is that the user-specified input parameters are
not being passed to the stored procedure. Consequently, no records are
returned. There are no error messages. The only problem is that no data
is returned to the report.

The *stored procedure* has been independently tested using exactly the
same parameters that were used for the report and the correct records were
returned. The stored procedure is working fine.

Debug.Print shows that the input parameters are created and processed by
the report's open event procedure (below). The *report* is failing to
pass the parameters from VBA. Maybe something is wrong with the timing or
the the recordsource needs to somehow be refreshed/requeried. Is there a
way to do this?

I have also found that if the input parameters are *manually* entered into
the report's property sheet, then the records are successfully returned
from the stored procedure. However, the input parameters need to change
each time the report is generated...which is why VBA is being used. ;-)

Can anyone tell me how to make the report pass the input parameters?


P.S. I'm cross-posting to microsoft.public.access.reports and
microsoft.public.access.adp.sqlserver.


Beware of line wrapping caused by the newsreader:
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler

'This procedure sets the report's recordsource using a portion of
OpenArgs
'as the input parameters for a stored procedure.

Dim varArray As Variant

With Me
If Len(.OpenArgs) > 0 Then

'Parse OpenArgs using the "~" delimiter. The first element in
the array
'contains input parameters (delimited by ";") to be used for
the stored
'procedure. The remaining array elements delimited by "~" are
used by the
'Report_Activate event procedure.
varArray = Split(.OpenArgs, "~")

'Parse the input parameters using the ";" delimiter.
varArray = Split(CStr(varArray(0)), ";")

.InputParameters = "@ClassIDList varchar(255) = '" &
varArray(0) _
& "', @DateMin datetime = '" & varArray(1)
_
& "', @DateMax datetime = '" & varArray(2)
& "'"

'This debug statement confirms that the input parameters are
correct.
Debug.Print "InputParameters = " & .InputParameters

'Set the RecordSource and RecordSourceQualifier.
.RecordSource = "stp_GetClassRosterData"
.RecordSourceQualifier = "dbo"

Else
ErrorHandler 86, "The " & .Name & " report has not been opened
correctly" _
& " and will be closed. If this problem continues, please
contact" _
& " your application administrator.", "Report_Open", .Name
Cancel = True
End If
End With

Exit_Procedure:
On Error Resume Next
varArray = Null
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
ErrorHandler Err.Number, Err.Description, "Report_Open", Me.Name
End If
Resume Exit_Procedure

End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Click to expand...
 
Mike:

If you are trying to set the input parameters as a property of the report in
the on open event, it won't work. The easiest way to do this with Access
2002 and later is like this:

Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource

Where you execute the SP using the Exec command with the input parameters
passed as part of the Exec command.
 
Thanks Steve. That completely solved the problem.

SA said:
Mike:

If you are trying to set the input parameters as a property of the report
in the on open event, it won't work. The easiest way to do this with
Access 2002 and later is like this:

Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource

Where you execute the SP using the Exec command with the input parameters
passed as part of the Exec command.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

MikeC said:
Access Version: Access 2002 SP3
Operating System: Windows XP Home Edition SP2
Front End: ADP
Back End: MSDE 2000

I'm having a problem with a report that uses a stored procedure as a
recordsource. The problem is that the user-specified input parameters
are not being passed to the stored procedure. Consequently, no records
are returned. There are no error messages. The only problem is that no
data is returned to the report.

The *stored procedure* has been independently tested using exactly the
same parameters that were used for the report and the correct records
were returned. The stored procedure is working fine.

Debug.Print shows that the input parameters are created and processed by
the report's open event procedure (below). The *report* is failing to
pass the parameters from VBA. Maybe something is wrong with the timing
or the the recordsource needs to somehow be refreshed/requeried. Is
there a way to do this?

I have also found that if the input parameters are *manually* entered
into the report's property sheet, then the records are successfully
returned from the stored procedure. However, the input parameters need
to change each time the report is generated...which is why VBA is being
used. ;-)

Can anyone tell me how to make the report pass the input parameters?


P.S. I'm cross-posting to microsoft.public.access.reports and
microsoft.public.access.adp.sqlserver.


Beware of line wrapping caused by the newsreader:
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Handler

'This procedure sets the report's recordsource using a portion of
OpenArgs
'as the input parameters for a stored procedure.

Dim varArray As Variant

With Me
If Len(.OpenArgs) > 0 Then

'Parse OpenArgs using the "~" delimiter. The first element in
the array
'contains input parameters (delimited by ";") to be used for
the stored
'procedure. The remaining array elements delimited by "~" are
used by the
'Report_Activate event procedure.
varArray = Split(.OpenArgs, "~")

'Parse the input parameters using the ";" delimiter.
varArray = Split(CStr(varArray(0)), ";")

.InputParameters = "@ClassIDList varchar(255) = '" &
varArray(0) _
& "', @DateMin datetime = '" & varArray(1)
_
& "', @DateMax datetime = '" & varArray(2)
& "'"

'This debug statement confirms that the input parameters are
correct.
Debug.Print "InputParameters = " & .InputParameters

'Set the RecordSource and RecordSourceQualifier.
.RecordSource = "stp_GetClassRosterData"
.RecordSourceQualifier = "dbo"

Else
ErrorHandler 86, "The " & .Name & " report has not been opened
correctly" _
& " and will be closed. If this problem continues, please
contact" _
& " your application administrator.", "Report_Open", .Name
Cancel = True
End If
End With

Exit_Procedure:
On Error Resume Next
varArray = Null
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
ErrorHandler Err.Number, Err.Description, "Report_Open", Me.Name
End If
Resume Exit_Procedure

End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Click to expand...
Click to expand...
 
Steve,

I'm having a similar problem as Mike and was very excited to know that a
solution was available. Unfortunately, your response was clipped. I was
wondering if maybe you post the info again. It would be greatly
appreciated. Thanks in advance.

Stan
 
Cicero,

Steve's response is below.


Mike:

If you are trying to set the input parameters as a property of the report in
the on open event, it won't work. The easiest way to do this with Access
2002 and later is like this:

Dim strRecordSource As String
strRecordSource = "Exec [Sales By Year] '01/01/1996','07/31/1997'"
Me.RecordSource = strRecordSource

Where you execute the SP using the Exec command with the input parameters
passed as part of the Exec command.
 
Back
Top