I'm having some problems with generating complex reports from Access 2002 front end on a SQL Server 2000 Database. The stored procedure behind the report draws its data from several views with grouping and calculated fields. The report itself has further grouping.

The report runs well when run manually from the Database Window with manually input parameters but it won't work when the parameters are passed from a form via the Input Parameters property box in the Report. An error message says that the Report does not permit calculated fields such as =sum([[fieldname]). When I run the report without such group totals it shows a CALC (______Parameter::) error.

I would like to try the Open method using
Exec usp_My_Stored_Procedure Parameter1, Parameter 2, Paramater 3 suggested in Topic 4785 and would appreciate some help with some sample code to show exactly where to place this line in a Private Sub.

My stored procedure is called "udsp_Job_Activity_Report_By_Employee_And_DateZone " and the Parameters are @Employee_Number, @Start_Date and @End_Date.
The form is called "FEmployee Job Time Selector" with three text boxes Text2, Text4 and text6. (I promise to name them properly when I get them to work, honest.)

For example, what would be the correct syntax for the suggested Exec command and parameter information and where should it be placed in the following Private Sub?

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click

    Dim stDocName As String

    stDocName = "Employee Job Activity Report"
    DoCmd.OpenReport stDocName, acPreview

Exit_Command10_Click:
    Exit Sub

Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click

End Sub

I've tried a few variations but am obviously missing something.

Your assistance will be greatly appreciated

Alan

Alan,
   I have the same setup Access 2002 FE with a SQL Server 2K BE. Here is what I do when I want to base a report on a stored proc.

   Create a pass-through query with the name of the stored proc and the parameters. Use DAO when in the open event of the report to set the SQL property of the pass-through query to correct parameter values. Then set the Record Source of the Report to the name of the pass-through query. I make the name of the pass-through query the same as the stored proc for readability purposes only.

   I have found a Microsoftism with ADO record sets and the report recordset property. The help file says that the recordset propertyof the report object can hold either a DAO or ADO recordset just like the form object (which does work), but in reality you cannot set the recordset property of the report to a recordset, you will get a 2593 error. This is a known bug in Access. See white paper Q287437 from Microsoft.

Hope this helps,
Mike (aka Dataman)

Mike,

Thanks for responding. Much appreciated. Unfortunately, I am not having much luck with the pass-through query and must be missing something fundamental (and probably very simple.

Does one write this query in SQL Server or in Access. I read Access Help and it describes how to create a pass-through query for Access Databases but the menu item is not available for Access Project.

I have tried to write

execute usp_MyStoredProcedure Parameter1, Parameter 2, Parameter 3
in the SQL Code pane in both Access and SQL Server to no avail.

Would be good enough to explain to me how to create one of these.

Alan,
   I thought it was an Access database (mdb) you were talking about not an Access project which has the extension (adp). I'm not an expert with adp's...I work more with mdb's using Access as a Frontend to SQL Server. I'm not sure I would be the one to help solve an ADP problem. If you are using an mdb...I can steer you in the write direction.

Regards,
Mike

OK Mike, Thanks for the help.

Would anybody else have any suggestions?

 

Name the textboxes
Text2 = Employee_Number
Text4 = Start_Date
Text6 = End_Date

Your controls must have the same names as the parameters in the stored procedure.

Let me knokw if that works. And, you are using an .adp. Right???



Sal

Sal,

Sorry for the delay in responding.

Regretfully that didn't work. It gave the same errors as before. I am using an .adp.

Would it be possible for you to explain the open method using the exec command?

Hi Alan, Did u find the answer to ur query?

Having something along the lines of:

@Parm1 = Forms!frm.txt1, @Parm2 = Forms!frm.txt2, @Parm3 = Forms!frm.txt3

in your InputParameters will usually work - if you're trying to pass calculated values, then calculate them on your form in hidden text boxes.


Brian Skelton
Braxis Computer Services Ltd.