How to get Prior Balance

[jalals]

Hi
Want Help Please

I am preparing a Students Bus Fee Module in Access 2010 on the following instructions:
1. As the school remains off during winter session No bus fee is to be charged.
2. In March the student has to pay the Bus fee (in advance) for the months of March, April and May. Accordingly I have named the field as QuarterlyFeeDue. Suppose the monthly Bus fee (Field name as FeeBase) is Rs. 100/= the student has to pay Rs. 300/=. I have Calculated it as QuarterlyFeeDue = FeeBase * 3
3. The student has been given the liberty to pay the QuarterlyFeeDue in instalments. Suppose student deposits (field Name as BusFeePaid) X amount during the quarter March, April, May. The system should depict the BalanceDue as QuarterlyFeeDue – BusFeePaid. BalanceDue is easily derived in query.
4. However, the student has to pay the fee for Sept, Oct, Nov, Dec in September in that case the QuarterlyFeeDue will be FeeBase *4
I have created three tables as under
A. tblStudents with fields as
1. StudentID – pk - Autonumber,
2. StudentName - Text
B. tblBusFee with fields as
1. FeeID – pk - Autonumber,
2. StudentFeeID – fk text
3. FeeBase – number


4. PriorBalance – number,
5. FeeDepositDate as date, (to populate)
6. FeeDepositedOn as Date,
7. AmountDeposited as number and
8. ReceiptNo.
C. tblFeeDate with fields as
1. FeeDateID – pk,
2. FeeDepositDate as Date (system date)
I have created a query
A. as qryFeeDeposit with the following fields included in it:
From table A I choose StudentId, StudentName
From table B I include FeeId, StudentFeeID, FeeBase, BusFeePaid, PriorBalance
Calculation :
1. BalanceDue: [QuarterlyFeeDue]-[AmountDeposited]
2. QuarterlyFeeDue: (GetQtrlyBusBs([AmountDeposited],[FeeBase]))+[PriorBalance]
3. iif(BusFeePaid >0,True,False)
4. FeeBase: IIf([StopCode]="A",460,IIf([StopCode]="B",750,IIf([StopCode]="C",950,IIf([StopCode]="D",1200,1500))))
I have created two forms
A. frmFeedate with two controls from the tblFeeDate
1. FeeDateId
2. FeeDepositDate
B. frmFeeDepositSubForm with the following controls from qryFeeDeposit
1. StudentsFeeID
2. StuddentName
3. FeeBase
4. QuarterlyFeeDue
5. AmountDeposited
6. PriorBalance
7. BalanceDue
8. ReceiptNo
9. FeeDepositDate (hidden)
10. StudentID (hidden)
11. FeeID (hidden)
I have combined the two form with frmFeeDate as master form and frmFeeDepositSubForm as Child.
The link master and child fields is FeeDepositdate.
The Problem:
When I input value in the AmountDeposited field the students BalanceDue field gets correct value. There is a Macro in the ReceiptsNo control i.e. OnGotFocus event which opens qryUpdatePriorBalance and the value of BalanceDue is transferred into PriorBalance field.
The students who deposits less fee against QuarterlyFeeDue. the system displays balance and after ReceiptNo gets focus PriorBalance and QuarterlyFeeDue gets updated. Suppose the students comes again to deposit rest amount i.e PriorBalance the system should ignor the QuarterlyFeeDue and should show only Prior Balance which he intends to deposit and the balance against his name should be Zero till next quarter comenses.
Here I got stuck
Please Help
Thanks
Jalal
Functions are as under:
I Have created a Module where I calculate the quarterly fee as under
On Main Forms frmFeeDate Current Event:
Private Sub FeeDepositDate_GotFocus()
Me.FeeDepositDate = Date
End Sub
Private Sub Form_Current()
DoCmd.GoToRecord , , acNewRec
End Sub
On Current event of SubForm frmFeeDepositSubForm
Private Sub Form_Current()
If Me.NewRecord Then
Me.ReceiptNo.DefaultValue = Nz(DMax("ReceiptNo", "tblBusFee"), 0) + 1
End If
End SUB
Module1 is as under
Public Function GetQtrlyBusBs(AmountDeposited As Integer, QterBusBs As Double)
Dim qtrlyFeeDue As Variant
If Month(Date) = 1 Or Month(Date) = 2 Then
qtrlyFeeDue = 0
' Fee for the month of january and Feberuary is not charagable
Else
If Month(Date) = 9 Or Month(Date) = 10 Or Month(Date) = 11 Or Month(Date) = 12 Then
qtrlyFeeDue = QterBusBs * 4
' 'fee for Sept.,oct,Nov,and Dec. is charged in the month of September.
Else
qtrlyFeeDue = QterBusBs * 3
End If
End If
GetQtrlyBusBs = qtrlyFeeDue
End Function

[DepricatedZero]

Would you mind clarifying this?

The students who deposits less fee against QuarterlyFeeDue. the system displays balance and after ReceiptNo gets focus PriorBalance and QuarterlyFeeDue gets updated. Suppose the students comes again to deposit rest amount i.e PriorBalance the system should ignor the QuarterlyFeeDue and should show only Prior Balance which he intends to deposit and the balance against his name should be Zero till next quarter comenses.

It sounds like you just need to subtract PriorBalance(Y) from QuarterlyFeeDue(X) when showing the data, and then set it to display 0 if X-Y < 0