I have a public function...
Code:
Public Function cmdcalculate(txtFrom, txtTo)
Dim dbs As Database
Dim rst As Recordset
Dim recStartDate As Date
Dim recEndDate As Date
Dim recIntRate As Double
Dim intDays As Integer, testInt As Double, dblInterest As Double, aaa As Date
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TblInterest", DB_OPEN_DYNASET)
aaa = txtFrom
If txtFrom > #12/31/1997# Then
aaa = DateSerial(Year(txtFrom) + 1, 4, 15)
End If
If txtFrom < #12/1/1997# Then
aaa = DateSerial(Year(txtFrom), Month(txtFrom) + 1, 15)
End If
If txtFrom >= #12/1/1997# And txtFrom <= #12/31/1997# Then
aaa = #1/15/1998#
End If
txtFrom = aaa
rst.MoveFirst
While Not rst.EOF
recStartDate = rst!intbdate
recEndDate = rst!intedate
recIntRate = rst!Rate / 100
If txtFrom < recStartDate Then
If txtTo >= recStartDate Then
If txtTo > recEndDate Then
intDays = DateDiff("d", recStartDate, recEndDate)
testInt = (recIntRate * (intDays / 365.25))
dblInterest = dblInterest + testInt
Else
intDays = DateDiff("d", recStartDate, txtTo)
testInt = (recIntRate * (intDays / 365.25))
dblInterest = dblInterest + testInt
End If
End If
ElseIf txtFrom >= recStartDate Then
If txtFrom <= recEndDate Then
If txtTo > recEndDate Then
intDays = DateDiff("d", txtFrom, recEndDate)
testInt = (recIntRate * (intDays / 365.25))
dblInterest = dblInterest + testInt
Else
intDays = DateDiff("d", txtFrom, txtTo)
testInt = (recIntRate * (intDays / 365.25))
dblInterest = dblInterest + testInt
End If
End If
End If
rst.MoveNext
Wend
cmdcalculate = dblInterest
End Function
...to calculate interest. However, I have not referenced public functions before and so I need some help. What I would like to accomplish is for the form to calculate the proper interest when the record in the form is selected. Possibly by using the "After Update" even procedure on the form?