I come up with an error -- can't set record source property after printing has started. I am running Access 2000 with SQL Server database (adp). My report has a subreport that creates a SQL stmt in the Report Open event. I'm aware that 'adp' does not support parameters in a stored procedure in a Subreport, so my work around was to set the recordsource to the SQL stmt in the subreport Open event. What can I do to solve this problem? I am trying to create a report with a subreport linked by Location. The subreport has a date parameter.

Have you tried to use an ado command object for the subreport? That way you can pass your parameter to your stored procedure, or just a sql string.



Sal

Just as I clicked the submit button, I remembered...

Create the stored procedure for the subreport and give the field/textbox the same name as the parameter in the stored procedure ( or the other way around).

Main report name: Report1
subReport name: subReport1
StoredProcedure name: proc1

CREATE PROCEDURE proc1 @inempid INT
     AS
     Select * from employees where empid = @inempid
RETURN

Add the subReport1 to the Report1

Use the stored procedure for the subreport and under input parameters for the sub-report enter something like this

@inempid INT = report!subReport1!inempid







Sal

Does this mean that I can't use the LINK CHILD and MASTER fields? I had tried to link by LOCATION Code, but it displays all records instead of limiting the records by location.
  Location #1 AAA
                  BBB
                  CCC
  Location #2 AAA
                  BBB
                  CCC
instead of:
  Location #1 AAA
  Location #2 BBB
                  CCC

There is no need to use the link child/master fields with this method.



Sal

I tried that out but it doesn't seem to work. I can see how your suggestion would work for 1 employee/report, but I'm producing a report that displays multiple locations with attached details. For example, using employee:
REPORT --------------------------------------------------------
    John Doe Dept: 200
                  Nov 10 10 Hrs <-- Subreport detail
                  Nov 11 8 Hrs

    Jane Smith Dept: 202
                  Nov 9 8 Hrs <-- Subreport detail
 etc.
---------------------------------------------------------------

I just re-created the same with a dummy adp and I used the northwind database and it worked fine.



Sal

Sorry for the delay .. I had to attend to other matters. This is what I have so far:
Main Report: Report1
              subreport in Location Footer
SubReport: Report1Sub
              LocnCode in Detail section
              REPORT Properties: Record Source = Stored Procedure
                                  Input Parameter = @LocnCode nvarchar(4) = Report!Report1Sub!LocnCode
When I run the report, the subReport info does not show. If I run the subreport standalone, after prompting for LocnCode, it displays the correct detail. Any idea what I'm doing wrong? Thanks.

Create a new report with the wizard. (do not rename anything).Then drag the stored procedure to the report design view and run it.

It should work.



Sal

Sorry for the delay. I tried recreating Report1 with the wizard. Not sure how to do what you suggested with the drag the stored procedure. If I drag it from the database window (Stored Procedures), it creates a textbox on Report1. I must be missing something. Thanks.