Append multiple records

[Rats]

I Have a situation where a client will want to pay some lump sum amounts of money off a loan and it may be something like $100 per month for 10 months. I have created a table for lump sums payments into which this info would be entered but I need to create an individual payment for the period of time designated ie 10 individual records of $100 each starting in a month and incrementing the month.

I see it being a append query but do not know how to make the append query create 10 instances of the one record based on a field value. I also do not know how to make the Month increment by one for each record ie Mar05,Apr05,May05 etc.

If this is possible any suggestions would be appreciated.

 

[Malcy]

Hi Peter
If you have to set the payment records in stone then the way to do it is to identify the date ofthe first payment, the amount of each payment, the frequency of payments and then either the total amount to be paid or else the date ofthe final payment. Probably use a form and then pull values from the textboxes.
You then append the record for the first payment amount to a table that holds payee, payment amount and payment date as well as other stuff. Now set up a loop to keep adding payments at the set frequency until either the final date is reached(while dtmPayDate < FinalPayment) or total amount is reached (may be able to use DSum). Then stop
Does this help?
It is fiddly and you need to do lots of trialing to make sure it works but once you get all the settings right then it works a dream.
Good luck

 

[Rich]

Why would you want to append a payment, why not enter into the correct table in the first place?

 

[Rats]

Thanks Malcy and Rich for responding.
Malcy I will give the loop thingy a go, although I haven't set one up before I'm sure I'll find heaps on this site to help with that.

Rich I need to append them because the program is designed to generate a two year debt reduction plan for clients and part of the requirements is to make provision for proposed future miscellaneous income or expenditure. That is, we will set up a budget that will apply for the 2 year period but the client may know that in six months time they will be getting a shift bonus payment each month for nine months and then it will cut out. So I need to enter the amount of the bonus, when it starts and how long for and I only really want to do that once. These are not ordinary loan payments but additional lump sums that may be paid in at a later date to reduce the loan earlier. Hope that explains it adequately.

 

[Rats]

Had a go at looping without much luck so I thought I would try to set up a command button to create a duplicate of a payment. I could then set up multiple payments with the corresponding clicks of the button. I resurrected some code I had used a while back to create a record duplicate. However, after modification in the new project I am getting "user defined type not defined" errors. I believe the names I have used are correct but not being a VB expert I am not sure where the error is. Any comments welcomed.

Private Sub Command18_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.Recordset.Clone

On Error GoTo Err_Command18_Click

' Tag property to be used later by the append query.
Me.Tag = Me![ClientID]
' Add new record to end of Recordset object.
With Rst
   .AddNew
      !CapMoveTypeID = Me!CapMoveTypeID
      !LoanApplied = Me!LoanApplied
      !Description = Me!Description
      !Date = Me!Date
      !Amount = Me!Amount
      !Frequency = Me!Frequency
      !EndDate = Me!EndDate
   .Update                     ' Save changes.
   .Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the Duplication Details append queries which selects all
' detail records that have the ClientNo stored in the form's
' Tag property and appends them back to the detail table with
' the ClientNo of the duplicated main form record.

'DoCmd.SetWarnings False
DoCmd.OpenQuery "CapitalMovementtbl Query" 'Name of append query
'DoCmd.SetWarnings True

'Requery the subforms to display the newly appended records.
Me![CapitalMovementQry].Requery 'Name of input form for records

Exit Sub

Err_Command18_Click:
MsgBox Error$
Resume Exit_Command18_Click:

End Sub

 

[Rats]

DAO database

I read another thread where a similar problem was related to the program being a DAO database?? I am using Access 2k and have used the above code (modified)in another application and it works.

Is there something I need to do with it now?

 

[Rich]

You need to re-name your field Date, it's a reserved word in Access and will cause problems