Using variables in a runsql command

[Paul Janofsky]

I am having difficulty with the syntax of a runsql
statement using variables from my procedure. Here's an
excerpt from my procedure:

Private Sub Payment_Schedule_Button_Click()
On Error GoTo Payment_Schedule_Button_Click_Err
Dim mPaymentNumber As Long
Dim mUnpaidBalance As Currency
Dim mPaymentAmt As Currency
Dim mPrinciple As Currency
Dim mInterest As Currency
Dim mNewBalance As Currency

mUnpaidBalance = Me.Principle_Amount
mPaymentAmt = Me.Payment
mPrinciple = PPmt(Me.Rate, 1, Me.Term,
Me.Principle_Amount)
mInterest = IPmt(Me.Rate, 1, Me.Term,
Me.Principle_Amount)
mNewBalance = mUnpaidBalance - mPrinciple

DoCmd.RunSQL "insert into amortization
(UnpaidBalance, PaymentAmt, Principle, Interest,
NewBalance) values(mUnpaidBalance, mPaymentAmt,
mPrinciple, mInterest, mNewBalance);", -1

The fields in the "values" statement are variables, but
I'm not sure of the proper syntax. Access prompts me for
these values at runtime.

Any advice is greatly appreciated.

Paul Janofsky
(e-mail address removed)

 

[Batman]

Try This:
Private Sub Payment_Schedule_Button_Click()
On Error GoTo Payment_Schedule_Button_Click_Err
Dim mPaymentNumber As Long
Dim mUnpaidBalance As Currency
Dim mPaymentAmt As Currency
Dim mPrinciple As Currency
Dim mInterest As Currency
Dim mNewBalance As Currency

mUnpaidBalance = Me.Principle_Amount
mPaymentAmt = Me.Payment
mPrinciple = PPmt(Me.Rate, 1, Me.Term, Me.Principle_Amount)
mInterest = IPmt(Me.Rate, 1, Me.Term, Me.Principle_Amount)
mNewBalance = mUnpaidBalance - mPrinciple

DoCmd.RunSQL "insert into amortization " & _
"(UnpaidBalance, PaymentAmt, Principle, Interest,
NewBalance) " & _
"values(" & mUnpaidBalance & ", " & mPaymentAmt & ", " &
mPrinciple & _
"," & mInterest & "," & mNewBalance & ");", -1

Variables must be outside the text string.

"Paul Janofsky" <[email protected]> píse v diskusním príspevku
| I am having difficulty with the syntax of a runsql
| statement using variables from my procedure. Here's an
| excerpt from my procedure:
|
| Private Sub Payment_Schedule_Button_Click()
| On Error GoTo Payment_Schedule_Button_Click_Err
| Dim mPaymentNumber As Long
| Dim mUnpaidBalance As Currency
| Dim mPaymentAmt As Currency
| Dim mPrinciple As Currency
| Dim mInterest As Currency
| Dim mNewBalance As Currency
|
| mUnpaidBalance = Me.Principle_Amount
| mPaymentAmt = Me.Payment
| mPrinciple = PPmt(Me.Rate, 1, Me.Term,
| Me.Principle_Amount)
| mInterest = IPmt(Me.Rate, 1, Me.Term,
| Me.Principle_Amount)
| mNewBalance = mUnpaidBalance - mPrinciple
|
| DoCmd.RunSQL "insert into amortization
| (UnpaidBalance, PaymentAmt, Principle, Interest,
| NewBalance) values(mUnpaidBalance, mPaymentAmt,
| mPrinciple, mInterest, mNewBalance);", -1
|
| The fields in the "values" statement are variables, but
| I'm not sure of the proper syntax. Access prompts me for
| these values at runtime.
|
| Any advice is greatly appreciated.
|
| Paul Janofsky
| (e-mail address removed)