[AccessD] Creating a stored procedure

Cross-posted to SQLServer list.

 

Let me start out by stating that I am totally novice to creating stored
procedures.

I hope someone can help me to create my first stored proc.

 

I have the VBA code complete, but am having trouble converting to the
proc.

The code is below:

 

Set rst = db.OpenRecordset("SELECT * FROM tblClaim ORDER BY ClaimNo",
dbOpenDynaset, dbSeeChanges)

rst.MoveFirst

Do Until rst.EOF = True

      Me.txtStatus = "Processing record " & rst!PatientNo

      Me.Repaint

          

      curPaidAmt = 0

      curCharge = 0

      intClaimNo = rst!ClaimNo

      intReferral = Nz(rst!Referral)

      strProvNo = rst!ProvNo

      Set rstPrint = db.OpenRecordset("SELECT * FROM tblPrintCharges
WHERE ClaimNo = " & intClaimNo & "")

      If rstPrint.EOF = False Then

          rstPrint.MoveFirst

          Do Until rstPrint.EOF = True

              rst.Edit

              rst!PaidAmt = Nz(rst!PaidAmt) + Nz(rstPrint!PaidAmt)

              curPaidAmt = Nz(curPaidAmt) + Nz(rstPrint!PaidAmt)

              rst!TotalClaimAmt = Nz(rst!TotalClaimAmt) +
Nz(rstPrint!Charge)

              curCharge = Nz(curCharge) + Nz(rstPrint!Charge)

              rst.Update

              rstPrint.MoveNext

          Loop

      End If

            

      Set rstPhy = db.OpenRecordset("SELECT * FROM tblCodPhy WHERE
Referral = " & intReferral & " AND ProvNo = '" & strProvNo & "'")

      If rstPhy.EOF = False Then

          rstPhy.MoveFirst

          Do Until rstPhy.EOF = True

              rstPhy.Edit

              rstPhy!ClaimPaid = "CP"

              rstPhy!ClaimPaidDate = Int(Now())

              rstPhy!ClaimPaidAmt = Nz(rstPhy!ClaimPaidAmt) +
Nz(curPaidAmt)

              rstPhy!ClaimChargeAmt = Nz(rstPhy!ClaimChargeAmt) +
Nz(curCharge)

              rstPhy.Update

              rstPhy.MoveNext

          Loop

      End If

            

      curPaidAmt = 0

      curCharge = 0

      curTotalCharges = 0

      'Get total claim charge amounts per referral

      Set rstPhy = db.OpenRecordset("SELECT * FROM tblCodPhy WHERE
Referral = " & intReferral & "")

      If rstPhy.EOF = False Then

          rstPhy.MoveFirst

          Do Until rstPhy.EOF = True

              curTotalCharges = Nz(curTotalCharges) +
Nz(rstPhy!ClaimChargeAmt)

              rstPhy.MoveNext

          Loop

      End If

            

      DoCmd.RunSQL ("UPDATE tblCod SET CaseCost = UBTotal + " &
curTotalCharges & " WHERE Referral = " & intReferral & "")

      rst.MoveNext

  Loop

 

I'm not exactly sure which sections to include in the proc.  Should I
include the whole thing?  

How do I create a Do Loop in the proc?

 

Any help/direction is greatly appreciated.

Thanks,

Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030203/16d3a42e/attachment-0001.html>