Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 11th, 2003, 09:13 PM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subreport problem: ADP + SQL Server

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.

 
Old November 11th, 2003, 09:47 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 11th, 2003, 10:31 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 12th, 2003, 03:50 AM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old November 12th, 2003, 10:31 AM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
 
Old November 12th, 2003, 08:43 PM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
---------------------------------------------------------------


 
Old November 12th, 2003, 10:05 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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



Sal
 
Old November 17th, 2003, 04:16 AM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old November 18th, 2003, 02:58 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 27th, 2003, 01:26 PM
Authorized User
 
Join Date: Jul 2003
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Set value in .adp form to UDT in SQL Server mantle51 SQL Server 2000 0 August 8th, 2007 11:28 AM
access adp subform connected to sql 2005 skype Access 1 February 5th, 2007 08:45 AM
adp- stored query problem LeoTiger1 Access 0 June 13th, 2006 04:11 PM
SQL 2000 SERVER+ACCESS ADP DLOOKUP MHONG Access ASP 1 October 25th, 2005 08:36 AM
Subreport Problem scorpion509 BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 August 3rd, 2004 05:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.