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

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.

--
Bill Mosca, MS Access MVP


"Levans digital" <(E-Mail Removed)> wrote in message
news:CE2EA754-CBF5-4CC2-8D7A-(E-Mail Removed)...
> 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
>

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

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



"Levans digital" <(E-Mail Removed)> wrote in message
news:CE2EA754-CBF5-4CC2-8D7A-(E-Mail Removed)...
> 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
>

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

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


"Levans digital" <(E-Mail Removed)> wrote in message
news:A4FDF7C2-7CD2-4C46-89F9-(E-Mail Removed)...
> 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

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