Inserting multiple records...

[Vel.]

Hello,

I have a form where users enter client payments into a payments table based
on charges in an activity table. For the purposes of my question, the
relevant bits of table structure are as follows:

tblActivity
ActivityID, ClientID, DateOf, ClientCharge

tblPayments
PaymentID, ActivityID, ClientPayment, ClientAdjustment

In my form, the user enters a ClientID and a DateOf value to limit the
possible activities to select from for entering a payment into tblPayment.
There is a subform (subBalance) which calculates the ClientBalance based on
ClientCharge-nz(Sum(ClientPayment))+nz(Sum(ClientAdjustment))
the value is stored in the field txtBalance

On the main form are the following fields

txtClientID, txtDateOf, txtActivityID, txtClientPaid, txtClientAdjusted

On the afterUpdate event of txtClientPaid I have some code which checks to
see if the amount paid is less than, equal to, or greater than txtBalance.
If it is less than, the user must determine whether the balance should stay,
or be adjusted off. If it is equal to, no action is taken. If it is greater
than (here's where I need help), there should be two options.

Option A: Adjust the balance onto the client's charge (this part I can do)

Option B: Post the balance to previous dates of service (this part I can't
do)

Essentially, if they choose "Option B", I want to use the remaining balance
to add new records to tblPayment for each ActivityID which currently has an
outstanding balance, starting with the earliest.

For example

John Doe was seen on the 1st, 5th, 9th, and 15th; and had a charge of $25
each time. He made a payment only on the 15th, of $90. The user goes to
post this payment to the ActivityID which corresponds to the 15th, and the
prompt comes up to let them know that by posting $90 to this date they would
create a $75 credit balance and they can choose Option A or Option B.

If they choose Option B, the system would then insert a new payment into
tblPayments for $25.00 with the activityID corresponding to the 1st, a second
corresponding to the 5th, and a $10.00 corresponding to the 9th, leaving the
client with an overall balance of $15, which would be based on the charge
from the 9th.

I know this may seem like an odd way to structure the charges, but it is
necessary for "paper trail" type purposes to have each line have its own
charge, which can subsequently be paid individually. I imagine the code
would need to utilize some sort of DoWhile type loop, but that is a bit of
coding I'm not familiar with utilizing in Access. Any help would be greatly
appreciated.

 

[Vel.]

In case it is of any help with the solution, I have a query, qryBalance,
which calculates the ClientBalance for each ActivityID.

 

[Vel.]

I got it figured out, for anyone who's interested this is the code I used...

Private Sub cmdAutoPost_Click()

On Error GoTo errAutoPost

If ckCanAutoPost = False Then
MsgBox "No more money can be autoposted to this client's account.", ,
"Cannot Autopost"
GoTo exitAutoPost
End If

Dim dblRemainder As Double
Dim dblPostAmount As Double
Dim dblBalance As Double
Dim dblClientCharge As Double
Dim stSQLAdd As String
Dim stEntBy As String
Dim stCriteria As String
Dim stActivityID As String
Dim dtPaidOn As Date

dblClientCharge = Forms!frmPostClient!subDetail!txtBalance
dblRemainder = Forms!frmPostClient!txtPayment - dblClientCharge
stEntBy = Forms!frmLogOn!txtProviderID
stCriteria = Forms!frmPostClient!txtClientID
dtPaidOn = Forms!frmPostClient!txtPaidOn

DoCmd.OpenForm "FrmQryPostClient"
DoCmd.GoToRecord , , acFirst

Do While dblRemainder > 0
dblBalance = Forms!frmQryPostClient!txtBalance
stActivityID = Forms!frmQryPostClient!txtActivityID

If dblBalance > 0 Then
If dblRemainder > dblBalance Then
dblPostAmount = dblBalance
Else
dblPostAmount = dblRemainder
End If

dblRemainder = dblRemainder - dblPostAmount
stSQLAdd = "INSERT INTO tblPayments ( ActivityID, InsuranceID,
EntBy, Payment, PaidOn ) VALUES (" & stActivityID & " , 'NA', '" & stEntBy &
"', " & dblPostAmount & ", " & dtPaidOn & ");"
DoCmd.RunSQL stSQLAdd

End If

DoCmd.GoToRecord , , acNext
Loop
Forms!frmPostClient!txtPayment = dblClientCharge
DoCmd.Close acForm, "frmQryPostClient"
DoCmd.Close acForm, "dlgOverPayClient"
MsgBox Format(Forms!frmPostClient!txtPaid - dblClientCharge -
dblRemainder, "Currency") & " was posted to client's account.", vbOKOnly,
"Posting Successful"

exitAutoPost:


Exit Sub

errAutoPost:

If Err.Number = 2105 Then

If dblRemainder > 0 Then
DoCmd.Close acForm, "frmQryPostClient"
Forms!frmPostClient!txtRemainder = dblRemainder
MsgBox Format(Forms!frmPostClient!txtPayment - dblClientCharge -
dblRemainder, "Currency") & " was posted to client's account.", vbOKOnly,
"Posting Successful"
Forms!frmPostClient!txtPayment = dblClientCharge
Forms!dlgOverPayClient!ckCanAutoPost = False
Forms!dlgOverPayClient!txtDetail.Requery
Resume exitAutoPost
End If

Else

DoCmd.Close acForm, "frmQryPostClient"
MsgBox Err.Number & " " & Err.Description
Resume exitAutoPost
End If

End Sub