Fee Calculation with Previous balances

[jalals]

Hi
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

[June7]

The file has an invalid name. I changed it to DBSTUD1.accdb and Access does open it.

Ideally, should not save PriorBalance and BalanceDue. These should be calculated as needed and is best done on reports. The calculation would be the net difference of transactions records for amounts obligated and amounts paid.

Not really understanding what balances are based on. Where is the student's obligation recorded? I see only AmountDeposited (a payment?).

Controls that should not allow data entry/edit should be set as Locked Yes, TabStop No - such as StudentName on the subform and the calculated balance fields.

Should not allow entry of subform records unless a date is entered on the main form. There is no relationship and no referential integrity set between tblFeeDate and tblBusFee. This allows record in tblBusFee without first entering record in tblFeeDate. FeedateID is set as PK in tblFeeDate but the form/subform are linking on FeeDepositDate fields. However, the field in tblBusFee is set as number not date/time type. This is a type mismatch. Either save FeedateID as foreign key in tblBusFee and fix the form/subform links or set the FeeDepositDate field in tblFeeDate as primary key and fix datatype in tblBusFee.

[jalals]

The file has an invalid name. I changed it to DBSTUD1.accdb and Access does open it.

Ideally, should not save PriorBalance and BalanceDue. These should be calculated as needed and is best done on reports. The calculation would be the net difference of transactions records for amounts obligated and amounts paid.

Not really understanding what balances are based on. Where is the student's obligation recorded? I see only AmountDeposited (a payment?).

Controls that should not allow data entry/edit should be set as Locked Yes, TabStop No - such as StudentName on the subform and the calculated balance fields.

Should not allow entry of subform records unless a date is entered on the main form. There is no relationship and no referential integrity set between tblFeeDate and tblBusFee. This allows record in tblBusFee without first entering record in tblFeeDate. FeedateID is set as PK in tblFeeDate but the form/subform are linking on FeeDepositDate fields. However, the field in tblBusFee is set as number not date/time type. This is a type mismatch. Either save FeedateID as foreign key in tblBusFee and fix the form/subform links or set the FeeDepositDate field in tblFeeDate as primary key and fix datatype in tblBusFee.

Sir
Thanks for the suggestions. You are correct BalanceDue is Net derived from (AmountDeposited _ QtrlyFeeDue)
There is now relationship and referential integrity between tblFeeDate and tblBusFee. This has allowed record in tblBusFee entering record in tblFeeDate.
and have fixed the form/subform links by setting the FeeDepositDate field in tblFeeDate.

I have created a table tblBusFeeDailyReceipt1 with the help of qryBusFeeDailyReceipt1 whci has helped to generate the Reports (rpt Month Wise Fee Report) and
rptStudent Wise Fee Report.

But the problem is persists. that is when a student deposits less amount against his due amount (qtrlyFeeDue) a case in report Admission no. 101 deposits less amout. next quarter his qtrlyFee due is same as that of previous quarter. The system is not carring forward the balanceDue amounts.

How will the system ask for previous balances. Next quarter's fee due should be Previous BalanceDue + QtrlyFeeDue. Take the case of Admission No. 1 In June the student must pay 2850+850.

Hope you got me
Plz Help here

I tried to update the qtrlyFueDue field by using the following bSinippt but failed
Dim BalanceDue As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT qtrlyFeeDue FROM qryFeeDeposit WHERE Feeid= StudentID"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
With rst
If rst.RecordCount > 0 Then


.MoveFirst
.Edit
qtrlyFeeDue = BalanceDue
.Update
End If
End With

Hope you got me.

[ssanfu]

Jalals, just so you know, June7 is a lady (woman) and should not be addressed as "Sir".

[June7]

Steve, the incorrect courtesy is tolerable and understandable - hard to come up with a non-gender specific one. Besides, I had an uncle named June (his mother's middle name).

[ssanfu]

June,
I understand the misuse, especially since it appears that English is not the OP's primary language. I was just meant to inform.

I also had an uncle with an unusual first name - Carroll (sounds like Carol, only spelled different - )

[jalals]

Sorry Mam

I apologize for my mistake. I am from India and could not recognize a lady name.

Sorry once again

[June7]

jalals, don't worry over it, I won't.

The procedure is a function called by expression in qryFeeDeposit. The recordset code to set value in the query is not needed and won't work. Remove it. The last line of the function should be:

GetQtrlyBusBs = qtrlyFeeDue.

That will return value to the query.

[jalals]

Hi June7

I was already using GetQtrlyBusBs = qtrlyFeeDue But it did't work as the next time student depositing the amount the system gets qtrlyFeeDue instead of the outstanding amount (BalanceDue). However I had created one more update query On Close Event of SubForm which transfers value of BalanceDue into PriorBalance field. Then add the following lines in the module1
If PriorBalance = 0 Then
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
GetQtrlyBusBs = qtrlyFeeDue
Else


GetQtrlyBusBs = PriorBalance
End If

This also does not work

[jalals]

Hi June7
sorry I saw your statement
currentDb.Execute "UPDATE qryFeeDeposit SET qtrlyFeeDue = " & BalanceDue & " WHERE Feeid=" & StudentID
a little late
However Me.balanceDue is not acceptable to the system
without Me. it give syntax error in UPDATE statement
I tried a lot to correct the syntax error but in vain
Thanks for help

[June7]

Did you delete the duplicate records in tblFeeDate for 4/24/2013?

Since frmFeeDate is opening with all records, suggest:

1. The GoToRecord code should be in Open event, not Current.

2. The FeeDepositDate textbox on frmFeeDate is bound. Either use DefaultValue property set to Date() or change code to:
If IsNull(Me!FeeDepositDate) Then Me!FeeDepositDate = Date()

Customary behavior for financial database is to record transactions of obligations and payments then calculate difference of their totals to derive current balance. In your case you are trying to calculate student's obligation from a lookup table based on the FeeDepositDate. I have never see this approach. Sounds nice at first as the student will not get their parking permit for the period without making a payment. However, by allowing partial payment there is a need to enter subsequent record(s) for payment of remainder. Unfortunately, when entering another payment the program wants to show another fee obligation. Don't think I can or want to work with this.

[jalals]

Hi June7

Again troubling you. All your suggestion stood good on implementation. But the error raised. on query statemwentCurrentDb.Execute "UPDATE qryFeeDeposit SET qtrlyFeeDue = ' & BalanceDue & ' WHERE FeeID = ' & StudentID'"

query can't be updated as qtrlyFeeDue is not Updatable field.

[June7]

Yes, I finally realized that when I reviewed the db. Also, I did say to remove that code from the procedure (post # 8).

I really can't do anything else. I don't think this db a workable design.

[jalals]

Please suggest any other solution or how to develop a db. if any available around

will be thankfull

[June7]

The only recommendation I have has been stated.

Input transactions of obligations and payments. Calculate net balance by subtracting the difference of the sums of the two types of transactions.