Susan Zeller
szeller at cce.umn.edu
Tue Apr 22 11:18:17 CDT 2003
Tim, First, you should start posting ot the access SQL Server list b/c you'll get tons of help there on stuff like this. What you need to do is have the record source of your report be a stored procedure and use the input parameters property of the report to reference the controls on your form where your users make their selections. In the sproc, use temp tables like: select fieldA, fieldB into #MyTempTable >From MyTable Where FieldC= @CParameter Then, to update that recordset, you can use a cursor. These use a lot of overhead and can be slow, but I have been using them without problems. Someone on the other list will probably have better advise on alternatives. I can send you a sproc that uses a temp table and cursors if you want. Email me off list. --Susan Susan B. Zeller Office of Information Systems College of Continuing Education University of Minnesota 306 Wesbrook Hall 77 Pleasant Street SE Minneapolis, MN 55455 Phone: 612-626-4785 Fax: 612-625-2568 -----Original Message----- From: Pain, T. (Tim) [mailto:Tim.Pain at e20.akzonobel.com] Sent: Tuesday, April 22, 2003 9:23 AM To: accessd at databaseadvisors.com Subject: [AccessD] Reports, temp tables and stored procedures I have a number of reports that are driven from forms that let the user input the report parameters, the code behind the form first clears a temporary work table, then builds and runs a query based on the forms parameters and then in code loops through this temp table updating other fields not populated by the first query. At the end of this it outputs a report based on the temp work table. Having recently moved the backend to SQL Server and I think I ought to be able to re-write the above to use #temp tables and stored procedures, but I have no real idea where to start. I reasonalby OK writing simple stored procedures but it's the creating and initial populating the #temp table, then looping back through it with further updates and then using the #temp table as the basis for the report that has me puzzled. Does anybody have a example they could let me have to get me started please? Many thanks Tim Pain Akzo Nobel Surface Chemistry Ltd <MailTo:Tim.Pain at AkzoNobel.com <MailTo:Tim.Pain at AkzoNobel.com> > -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030422/3607b857/attachment-0001.html>