VBA Insert Into

[fenhow]

Hi, I currently have a routine that creates a table and generates a schedule. It works great.

What I am trying to do is after the routine runs, insert a new record at the end of the newly created table with a value from the parent form called "Residual"

I am confident it can be done but I just don't know how. The code is below.

Any assistance much appreciated.
Fen

Private Sub Command11_Click()
'Ok its all in here. This is where we need to add the Residual as a single line entry at the end of the schedule.

If MsgBox("Are you SURE? This will erase all payment data and create a new schedule?", vbYesNoCancel) <> vbYes Then Exit Sub

If IsNull(Forms!LoanF!PaymentAmount) Then
MsgBox "You need to calculate the payment amount first"
Exit Sub
End If

DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM ScheduleT WHERE LoanID=" & Forms!LoanF!LoanID)
Me.Requery
DoCmd.GoToControl "PaymentNumber"

Dim BBal As Currency
Dim Counter As Long
Dim CurDate As Date
Dim X As Integer
Dim TotalPrin As Currency, Correction As Currency
Dim Residual As Currency


BBal = Forms!LoanF!LoanAmount
Counter = 1
CurDate = Forms!LoanF!StartDate

While BBal > 0
DoCmd.GoToRecord , , acNewRec
PaymentNumber = Counter
Counter = Counter + 1
DueDate = CurDate
CurDate = DateAdd("m", 1, CurDate)
AmountPaid = 0
RegularPayment = 0
ExtraPayment = 0
BeginningBalance = BBal
AmountDue = Forms!LoanF!PaymentAmount
Interest = Round(BeginningBalance * (Forms!LoanF!InterestRate / 12), 2)
Principal = AmountDue - Interest


If BBal < Forms!LoanF!PaymentAmount Then
EndingBalance = 0
TotalPrin = DSum("Principal", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID) + Principal
Correction = Forms!LoanF!LoanAmount - TotalPrin
AmountDue = AmountDue + Correction
Principal = Principal + Correction

' If BBal < Forms!LoanF!PaymentAmount Then
' EndingBalance = 0
' TotalPrin = DSum("Principal", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID) + Principal
' Correction = Forms!LoanF!LoanAmount - TotalPrin
' AmountDue = AmountDue + Correction
'Principal = Principal + Correction
Else
EndingBalance = BeginningBalance - Principal
'EndingBalance = Residual
End If
'BBal = Residual
BBal = EndingBalance
Wend

'Do I insert the new record here?

Forms!LoanF.Requery

DoCmd.SetWarnings True

End Sub

 

[Ranman256]

docmd.openquery "qaInsertNewRecord"

'you design the qaInsertNEwRecord qry

 

[fenhow]

Thank you, I am sure that will work but unfortunately is too vague for my skillset.
Can you elaborate, or can I just do it with a docmd insert into statement in the code I provided?
I appreciate the help.
Fen

 

[fenhow]

Ok, so I created an append query and it is getting closer however in my Schedule Table there are currently 36 records, all I want is to add 1 new record with the value "Residual" only one time, the append query is adding it 36 times..
Thanks.
Fen