Access Report + SQL Server SPROC

  • Thread starter Thread starter Steveo
  • Start date Start date
S

Steveo

Access Report + SQL Server SPROC

Server = SQL Server 2000 SP3 + Windows 2000 SP4 (MDAC 2.8)
Client = Access 2000 SP3 + Windows 2000 SP4 (MDAC 2.8)

I have a sproc as the record source for an Access Form,
which is fine.
I have an Access Report which is based on the same sproc,
but it is not fine.

Every time the report loads, it pops up with Input box's
asking for the parameters to be passed, even though the
Record Source and Input Parameter properties are set in
the same way as the Form which works.

The code is on the onOpen Event of the report is this:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ERR_ERROR

Dim strYear As String
Dim strPeriod As String
Dim strCC As String
Dim strGroup As String
Dim strUser As String
Dim strParam As String

strYear = Nz([Forms]![frm_01_start]![txtYear], "%")
strPeriod = Nz([Forms]![frm_01_start]![txtPeriod], "%")
strGroup = Nz([Forms]![frm_01_start]![txtGroup], "%")
strUser = Nz([Forms]![frm_01_start]![txtUser], "%")
strCC = Nz([Forms]![frm_01_start]![txtCostCentre], "%")

strParam = "@Year char = " & strYear & ", @Period char = "
& strPeriod & _
", @CC char = '" & strCC & "', @Group char = '" &
strGroup & _
"', @User char = '" & strUser & "'"

Me.InputParameters = strParam
Me.RecordSource = "sp_Get_Lines_02_Reports"

Exit Sub
ERR_ERROR:
MsgBox "Error " & Err.Number & " " & Err.Description
End Sub


The code is the same for the report as the form, the form
loads using the correct criteria as passed via the Input
Parameter property on the onOpen Event.
The report does not, it starts with Input box's for each
of the expected parameters.

I've seen a couple of other people post this problem
elsewhere but with no luck. This appears to be a bug
which has existed for a long time, I wonder why it hasn't
been sorted....

Im going to post this in sql server and access newsgroup,
as maybe there are experiances/resolutions on both sides...

This is a really frustrating issue, any advice would be
greatfully recieved.

Many thanks.

Steve'o
 
SteveO:

You can't set the input parameters of an Access report dynamically at run
time. Access simply ignores you code, because before the report is actually
instantiated and the events start running Access will query the server based
on your record source and if it requires params will call form them from the
Input Parameters property of the report. So its a matter of timing.

With A2K, you must set the input params in the Input Parameters property of
the report before the report runs; period. So the best work around is to
use a form where the user can enter the input parameters and reference those
in the reports properties. If you want a work around for this, (albeit
somewhat complex,) then stop by our web and look in the Code and Design Tips
area under Reports and there's a method there to create dynamic sprocs with
params in code and have your report run in A2K based on that scenario.

On the other hand if you upgrade to Access 2002 or 2003, then you have a
totally separate option which is much more workable, and that is to, in the
On Open event set your row source to something like this; passing in the
parameters:

"Exec [Sales By Year] '01/01/1996','07/31/1997'"

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Steveo said:
Access Report + SQL Server SPROC

Server = SQL Server 2000 SP3 + Windows 2000 SP4 (MDAC 2.8)
Client = Access 2000 SP3 + Windows 2000 SP4 (MDAC 2.8)

I have a sproc as the record source for an Access Form,
which is fine.
I have an Access Report which is based on the same sproc,
but it is not fine.

Every time the report loads, it pops up with Input box's
asking for the parameters to be passed, even though the
Record Source and Input Parameter properties are set in
the same way as the Form which works.

The code is on the onOpen Event of the report is this:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ERR_ERROR

Dim strYear As String
Dim strPeriod As String
Dim strCC As String
Dim strGroup As String
Dim strUser As String
Dim strParam As String

strYear = Nz([Forms]![frm_01_start]![txtYear], "%")
strPeriod = Nz([Forms]![frm_01_start]![txtPeriod], "%")
strGroup = Nz([Forms]![frm_01_start]![txtGroup], "%")
strUser = Nz([Forms]![frm_01_start]![txtUser], "%")
strCC = Nz([Forms]![frm_01_start]![txtCostCentre], "%")

strParam = "@Year char = " & strYear & ", @Period char = "
& strPeriod & _
", @CC char = '" & strCC & "', @Group char = '" &
strGroup & _
"', @User char = '" & strUser & "'"

Me.InputParameters = strParam
Me.RecordSource = "sp_Get_Lines_02_Reports"

Exit Sub
ERR_ERROR:
MsgBox "Error " & Err.Number & " " & Err.Description
End Sub


The code is the same for the report as the form, the form
loads using the correct criteria as passed via the Input
Parameter property on the onOpen Event.
The report does not, it starts with Input box's for each
of the expected parameters.

I've seen a couple of other people post this problem
elsewhere but with no luck. This appears to be a bug
which has existed for a long time, I wonder why it hasn't
been sorted....

Im going to post this in sql server and access newsgroup,
as maybe there are experiances/resolutions on both sides...

This is a really frustrating issue, any advice would be
greatfully recieved.

Many thanks.

Steve'o
Click to expand...
 
Back
Top