<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1126" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2>Tim,</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>First,
you should start posting ot the access SQL Server list b/c you'll get tons of
help there on stuff like this.</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>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:</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>select
fieldA, fieldB</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>into
#MyTempTable</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>From
MyTable</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>Where
FieldC= @CParameter</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff size=2>I can
send you a sproc that uses a temp table and cursors if you want. Email me
off list.</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#0000ff
size=2>--Susan</FONT></SPAN></DIV>
<DIV><SPAN class=896381416-22042003><FONT face=Arial color=#008080>Susan B.
Zeller</FONT> <BR><FONT face=CopperplateTLig size=2>Office of Information
Systems</FONT> <BR><FONT face=CopperplateTLig size=2>College of Continuing
Education</FONT> <BR><FONT face=CopperplateTLig size=2>University of
Minnesota</FONT> <BR><FONT face=CopperplateTLig size=2>306 Wesbrook Hall</FONT>
<BR><FONT face=CopperplateTLig size=2>77 Pleasant Street SE</FONT> <BR><FONT
face=CopperplateTLig size=2>Minneapolis, MN 55455</FONT> <BR><FONT
face=CopperplateTLig size=2>Phone: 612-626-4785</FONT> <BR><FONT
face=CopperplateTLig size=2>Fax: 612-625-2568</FONT> </DIV></SPAN>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Pain, T. (Tim)
[mailto:Tim.Pain@e20.akzonobel.com] <BR><B>Sent:</B> Tuesday, April 22, 2003
9:23 AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B>
[AccessD] Reports, temp tables and stored procedures<BR><BR></FONT></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>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.</FONT></P>
<P><FONT face=Arial size=2>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.</FONT></P>
<P><FONT face=Arial size=2>Does anybody have a example they could let me have
to get me started please?</FONT> </P>
<P><FONT face=Arial size=2>Many thanks</FONT> <BR><B><I><FONT
face="Times New Roman" color=#000080>Tim Pain</FONT></I></B> <BR><FONT
face="Times New Roman" color=#000080 size=1>Akzo Nobel Surface Chemistry
Ltd</FONT> <BR><U><FONT face="Times New Roman" color=#000080
size=1><</FONT></U><A href="MailTo:Tim.Pain@AkzoNobel.com"><U></U><U><FONT
face="Times New Roman" color=#0000ff
size=1>MailTo:Tim.Pain@AkzoNobel.com</FONT></U></A><U></U><U></U><U><FONT
face="Times New Roman" color=#000080 size=1>></FONT></U>
</P><BR><BR><BR><BR></BLOCKQUOTE></BODY></HTML>