Advance Billing

[Guest]

Hey all,
I am trying to set up a billing template. Like for example what a Bank would
use.
Scenario: lets say a Loan Payment is $450 per month due the 15th of each
month.
I would like the table to be setup like this:

Fiellds: Due Date Amout Due Pay Date Amount Paid
record1 10/15/2006 $450 10/14/2006 $450
record2 11/15/2006 $450

What I want to accomplish: What can I do to have the Next Due Date and the
Amount Due be automatically entered into the table as in Record 2.

Thanks Very Much

 

[Bill Mosca, MS Access MVP]

You did not state your level of experience with Access so I don't know how
much help this will be, but you can use the AfterInsert or AfterUpdate event
to insert the next record based on the balance and payment schedule.

 

[Guest]

Hey Thanks for the reply. I understand fully well However what if an actual
payment was made 10/15/2006 and none was made for 11/15/2006 and for
12/15/2006 as like the exampe below.

Real world example...
Lets say its 03/15/2007
When I do a query for payments up to that date I would like to see

Due Date Amount Due Pay Date Amount Paid
10/15/2006 $450 10/14/2006 $450
11/15/2006 $450
12/15/2006 $450
01/15/2007 $450
02/15/2007 $450
03/15/2007 $450

Keep in Mind that the last user intervention was 10/14/2006. I need the
remaing 5 fields to be created automatically as the dates comes and goes.

I hope you understand me.

Thanks

 

[JK]

Assuming that you have a table that holds the parameter of the loan say
"tblLoans" Linked to tblSchedule on Loan_ID (one-To-Many)

You can (or really should !) create the repayment schedule when the loan is
created.
something Like this

Here we go:


Private Sub CreateScheduleButton_Click( )

'Output: Create a loan repayments Monthly schedule

Dim idLoan as Long, _
Dim tmpDate As Date, _
numPayments As Long, _
perAmount As Double, _
PayNum As Long, _
rs As DAO.Recordset

'*** Requires DAO Object library reference

'variables:
'-----------
'idLoan: Loan_ID
'tmpDate: Scheduled Paymenet date
'numPayments: Number repayments
'perAmount: Periodical (repayment) Amount
'PayNum: Payment number (secquential number)


'Read Master Loan Master Table ----
idLoan=Nz(Me.Loan_ID,0) ' or However you get the Loan_ID

tmpDate = Nz(DLookup("[First Repayment]", _
"tblLoans", "[Loan_ID]=" & idLoan), Date)

numPayments = Nz(DLookup("[Num Repayments]", _
"tblLoans", "[Loan_ID]=" & idLoan), 0)

perAmount = Nz(DLookup("[Period Amount]", _
"tblLoans", "[Loan_ID]=" & idLoan), 0)

If idLoan = 0 Or _
numPayments = 0 Or _
perAmount = 0 Then
Exit Sub
End If


'Open the recordset
Set rs = CurrentDb.OpenRecordset("tblSchedule")


'Append the schedule
For PayNum = 1 To numPayments

With rs
.AddNew
![Loan_ID] = idLoan
![Repayment No] = PayNum
![Due Date] = tmpDate
![Amount Due] = perAmount
.Update

End With

'Move to next month
tmpDate = DateAdd("m", 1, tmpDate)

Next PayNum

' Close the recordset ---
rs.Close
Set rs = Nothing

'--- tell the user it is done
MsgBox "Schedule created with " & numPayments & " Repayments"

End Sub


++++++++
Disable the Button once the shcedule was created

Regards/JK

 

[Guest]

Thanks very much. Your solution worked in another scenario I was working on
very advance good!. Can I ask another question. I have a date such as
10/15/2006 entered in field "startpaymentdate" on the form "frmschedule"
which is the main form.

On subform I have "subfrmschedule" I have fields "duedate" and "dueamount"

How can I generate a new record in the subform on the 15th of every
proceeding month lets say on 11/15/2006.....

My idea was using the below code
DoCmd.GoToRecord acDataForm, "subfrmschedule", acNewRec

But including some reference to "startpaymentdate" on the form "frmschedule"
and some other syntax to ceate a new record every 1 month or every 30 days of
that date. What do you think

 

[JK]

I wouldn't test when you enter the record, or at least will not rely on it
because if no one open a record which due for update, you subform will
remain
out of date. I will test when I open Access.

Create a query with a calculated field that have the next scheduled payment
for each loan,something like:

SELECT DISTINCT tblSchedule.Loan_ID, _
DateAdd("m",1,DMax("[Due Date]","tblSchedule","[Loan_ID]=" _
& [Loan_ID])) AS [Next Due] FROM tblSchedule _
WHERE DateAdd("m",1,DMax("[Due Date]","tblSchedule","[Loan_ID]=" _
& [Loan_ID]))<=Date();

(Hopefully I got it right

Run the a count on the query when you start Access. if the count >0
append the relevant records by running an Append query based on the query
above
or through a modified version of the routine I gave you before (One .AddNew
for each record in the query)

Does that make sense?

Regards/JK

 

[Guest]

Mein your result makes alot of sense let me work on it to see.
Will Post the result
Thanks