How to copy data of same row to another table using INSERT INTO?

[jazee007]

Hi friend,

I have a table named Hire Purchase details and another one named ScheduleT. In the table Hire Purchase Details I have a field named "HDCapital" and in the "ScheduleT" I have field named capital. A schedule of payment will be created and then payment will be received afterwards.

I have a button to record New payment in the table "ScheudleT". On click the following code is executed "Private Sub Command156_Click()".

It works fine. But I need to copy the "Capital" on the same row that payment is received from the table "ScheduleT" to the table "Hire Purchase details" field "HDCapital"

Private Sub AmountPaid_AfterUpdate()
 
   If AmountPaid > AmountDue Then
        RegularPayment = AmountDue
        ExtraPayment = AmountPaid - AmountDue
    Else
        RegularPayment = AmountPaid
        ExtraPayment = 0
    End If
 
 
    DoRecalc
 
End Sub

Private Sub Command11_Click()
 
    If MsgBox("Are you SURE? This will erase all payment data and create a new schedule?", vbYesNoCancel) <> vbYes Then Exit Sub
 
    If IsNull(Forms![Customers Hire Purchase Add]!AmountDue) Then
        MsgBox "Credit amount should not be null"
        Exit Sub
    End If
 
    DoCmd.SetWarnings False
    '/////////////////// This will insert Rs 0 In Hire Purchase Details
    Dim S As String
    Dim P As Currency
    Dim D As Date
 
 
 
    S = InputBox("Monthly Instalment", "Payment", 0)
 
    If S = "" Then Exit Sub
    P = CCur(S)
 
    S = Nz(InputBox("Date", "Date", Date), 0)
    If S = "" Then Exit Sub
    D = CDate(S)
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO [Hire Purchase Details] (CreditInvoiceNo,Amount,InstalmentDate,PaymentType,Cashier) " & _
    "VALUES (" & Forms![Customers Hire Purchase Add]!CreditInvoiceNum & "," & P & ", Date(),'Cash',[TempVars]![CurrentUserID])"
 
    DoCmd.SetWarnings True
 
    '//////////////////// end
 
 
    DoCmd.RunSQL ("DELETE * FROM ScheduleT WHERE LoanID=" & Forms![Customers Hire Purchase Add]!CreditInvoiceNum)
    Me.Requery
    DoCmd.GoToControl "MonthSN"
 
    Dim BBal As Currency
    Dim Counter As Long
    Dim CurDate As Date
    Dim X As Integer
    Dim TotalPrin As Currency, Correction As Currency
 
    BBal = Forms![Customers Hire Purchase Add]!ChargeablePrice
    Counter = 1
    CurDate = Forms![Customers Hire Purchase Add]![Date of Sales]
 
    While BBal > 0
        DoCmd.GoToRecord , , acNewRec
        MonthSN = Counter
        Counter = Counter + 1
        DueDate = CurDate
        CurDate = DateAdd("m", 1, CurDate)
        AmountPaid = 0
        RegularPayment = 0
        ExtraPayment = 0
        OpeningBalance = BBal
        AmountDue = Forms![Customers Hire Purchase Add]!MonthlyInstalment
        Charges = OpeningBalance * (Forms![Customers Hire Purchase Add]!InterestRate / 12)
        Capital = AmountDue - Charges
        If BBal < Forms![Customers Hire Purchase Add]!MonthlyInstalment Then
            ClosingBalance = 0
            TotalPrin = DSum("Capital", "ScheduleT", "LoanID=" & Forms![Customers Hire Purchase Add]!CreditInvoiceNum) + Capital
            Correction = Forms![Customers Hire Purchase Add]!ChargeablePrice - TotalPrin
            AmountDue = AmountDue + Correction
            Capital = Capital + Correction
        Else
            ClosingBalance = OpeningBalance - Capital
        End If
        BBal = ClosingBalance
    Wend
 
   Me.Requery
 
    DoCmd.SetWarnings True
 
End Sub

Private Sub Command19_Click()
 
    DoRecalc
 
End Sub
 
Public Sub DoRecalc()
 
    On Error Resume Next
    Dim BBal As Currency, TotalPrin As Currency, TotalExtra As Currency
    BBal = Forms![Customers Hire Purchase View and Edit]!ChargeablePrice
 
 
    ' clear existing calculations
    Me.Requery
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE ScheduleT SET Capital=0 WHERE LoanID=" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum
 
    DoCmd.GoToControl "MonthSN"
    DoCmd.GoToRecord , , acFirst
    While Not IsNull(MonthSN)
        OpeningBalance = BBal
        If OpeningBalance = 0 Then
            AmountDue = 0
            Capital = 0
            Charges = 0
            ClosingBalance = 0
        Else
            AmountDue = Forms![Customers Hire Purchase View and Edit]!MonthlyInstalment
            Charges = OpeningBalance * (Forms![Customers Hire Purchase View and Edit]!InterestRate / 12)
            Capital = AmountDue - Charges
            If BBal < Forms![Customers Hire Purchase View and Edit]!MonthlyInstalment Then
                ClosingBalance = 0
                TotalPrin = DSum("Capital", "ScheduleT", "LoanID=" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum) + Capital
                TotalExtra = DSum("ExtraPayment", "ScheduleT", "LoanID=" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum)
                Correction = Forms![Customers Hire Purchase View and Edit]!ChargeablePrice - TotalPrin - TotalExtra
                AmountDue = AmountDue + Correction
                Capital = Capital + Correction
 
            Else
                ClosingBalance = OpeningBalance - Capital - ExtraPayment
            End If
            BBal = ClosingBalance
        End If
 
        DoCmd.GoToRecord , , acNext
 
    Wend
 
    Me.Requery
    DoCmd.SetWarnings True
 
 
End Sub
 

Private Sub Command156_Click()
DoRecalc
End Sub
 

Private Sub Command32_Click()
 
    Dim S As String
    Dim P As Currency
    Dim D As Date
    Dim K As Currency
    Dim L As Currency
 
    S = InputBox("The Monthly Instalment is ROUNDED to as a Whole. E.g. Rs 1423.15 will be Rounded to as Rs 1424.", "Payment", -Int(-Forms![Customers Hire Purchase View and Edit]!MonthlyInstalment), 0)
 
    If S = "" Then Exit Sub
    P = CCur(S)
 
    S = Nz(InputBox("Date", "Date", Date), 0)
    If S = "" Then Exit Sub
    D = CDate(S)
 
 
    ' Add to Hire Purchase Details
    DoCmd.SetWarnings False
 
    DoCmd.RunSQL "INSERT INTO [Hire Purchase Details] (CreditInvoiceNo,Amount,InstalmentDate,PaymentType,Cashier) " & _
    "VALUES (" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum & "," & P & ", Date(),'Cash',[TempVars]![CurrentUserID])"
 
    DoCmd.SetWarnings True
    Me.Requery
 
    ' Find first unpaid item in Schedule of Payment
    DoCmd.GoToControl "MonthSN"
    DoCmd.GoToRecord , , acFirst
    While RegularPayment >= AmountDue
        DoCmd.GoToRecord , , acNext
    Wend
 
    If IsNull(MonthSN) Then
        MsgBox "No payment to add to"
        Exit Sub
    End If
 
    AmountPaid = AmountPaid + P
    AmountPaid_AfterUpdate
 
    K = DSum("AmountDue", "ScheduleT", "LoanID=" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum)
    L = DSum("RegularPayment", "ScheduleT", "LoanID=" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum)
 
   If (K - L) <= 0 Then
   DoCmd.RunSQL "Update ([Hire Purchase Customers]) set AccStatus = 'Account Settled'" & _
   "Where ([Hire Purchase Customers].[AccStatus])='Instalment' and ([Hire Purchase Customers].CreditInvoiceNum)= " & Me.LoanID & ""
 
    MsgBox "Account Settled"
 
 
    End If
 
 
End Sub
 

I'm sorry for my english.

Please can you help. Thanks

[Seth Schrock]

The query would follow the form of

INSERT INTO [Hire Purchase Details] (HDCapital)
SELECT [Field Name]
FROM ScheduleT
WHERE Your Criteria here

[jazee007]

I tried to write the code but can't figure it out.

    ' Add to Hire Purchase Details
    DoCmd.SetWarnings False
 
    DoCmd.RunSQL "INSERT INTO [Hire Purchase Details] (CreditInvoiceNo,Amount,InstalmentDate,PaymentType,Cashier) " & _
    "VALUES (" & Forms![Customers Hire Purchase View and Edit]!CreditInvoiceNum & "," & P & ", Date(),'Cash',[TempVars]![CurrentUserID])"
 
 
 
   DoCmd.RunSQL "INSERT INTO [Hire Purchase Details] (HDCapital)" & _
    "Values(Capital)" & _
    "FROM ScheduleT" & _
    "Where ([AmountPaid])=" & Me.AmountPaid & ""
 
    DoCmd.SetWarnings True
    Me.Requery

Can you write it for me?