<html>
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 10 (filtered)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{font-family:Tahoma;
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>Cross-posted to SQLServer list.</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>Let me start out by stating that I am totally novice to
creating stored procedures.</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>I hope someone can help me to create my first stored proc.</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>I have the VBA code complete, but am having trouble
converting to the proc.</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>The code is below:</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>Set rst = db.OpenRecordset("SELECT * FROM tblClaim
ORDER BY ClaimNo", dbOpenDynaset, dbSeeChanges)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>rst.MoveFirst</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>Do Until rst.EOF = True</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Me.txtStatus =
"Processing record " & rst!PatientNo</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Me.Repaint</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> curPaidAmt = 0</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> curCharge = 0</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> intClaimNo = rst!ClaimNo</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> intReferral =
Nz(rst!Referral)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> strProvNo = rst!ProvNo</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Set rstPrint =
db.OpenRecordset("SELECT * FROM tblPrintCharges WHERE ClaimNo = "
& intClaimNo & "")</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> If rstPrint.EOF = False Then</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPrint.MoveFirst</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Do
Until rstPrint.EOF = True</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rst.Edit</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rst!PaidAmt = Nz(rst!PaidAmt) + Nz(rstPrint!PaidAmt)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
curPaidAmt = Nz(curPaidAmt) + Nz(rstPrint!PaidAmt)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rst!TotalClaimAmt = Nz(rst!TotalClaimAmt) + Nz(rstPrint!Charge)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
curCharge = Nz(curCharge) + Nz(rstPrint!Charge)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rst.Update</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPrint.MoveNext</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> </span></font><font
size=1 face=Tahoma><span style='font-size:8.0pt;font-family:Tahoma'>Loop</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> End If</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Set rstPhy = db.OpenRecordset("SELECT
* FROM tblCodPhy WHERE Referral = " & intReferral & " AND
ProvNo = '" & strProvNo & "'")</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> If rstPhy.EOF = False Then</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy.MoveFirst</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Do
Until rstPhy.EOF = True</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy.Edit</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy!ClaimPaid = "CP"</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy!ClaimPaidDate = Int(Now())</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy!ClaimPaidAmt = Nz(rstPhy!ClaimPaidAmt) + Nz(curPaidAmt)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy!ClaimChargeAmt = Nz(rstPhy!ClaimChargeAmt) + Nz(curCharge)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy.Update</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy.MoveNext</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> </span></font><font
size=1 face=Tahoma><span style='font-size:8.0pt;font-family:Tahoma'>Loop</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> End If</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> curPaidAmt = 0</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> curCharge = 0</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> curTotalCharges = 0</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> 'Get total claim charge
amounts per referral</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Set rstPhy =
db.OpenRecordset("SELECT * FROM tblCodPhy WHERE Referral = " &
intReferral & "")</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> If rstPhy.EOF = False Then</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy.MoveFirst</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> Do
Until rstPhy.EOF = True</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
curTotalCharges = Nz(curTotalCharges) + Nz(rstPhy!ClaimChargeAmt)</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
rstPhy.MoveNext</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> </span></font><font
size=1 face=Tahoma><span style='font-size:8.0pt;font-family:Tahoma'>Loop</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> End If</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'>
</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> DoCmd.RunSQL ("UPDATE
tblCod SET CaseCost = UBTotal + " & curTotalCharges & " WHERE
Referral = " & intReferral & "")</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> rst.MoveNext</span></font></p>
<p class=MsoNormal><font size=1 face=Tahoma><span style='font-size:8.0pt;
font-family:Tahoma'> </span></font><font size=1 face=Tahoma><span
style='font-size:8.0pt;font-family:Tahoma'>Loop</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>I’m not exactly sure which sections to include in the
proc. Should I include the whole thing? </span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>How do I create a Do Loop in the proc?</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>Any help/direction is greatly appreciated.</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>Thanks,</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>Mark Boyd</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>Sr. Systems Analyst</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'>McBee Associates, Inc</span></font></p>
<p class=MsoNormal><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma'> </span></font></p>
</div>
</body>
</html>