Syntax Error Need Help

J

Joe Cilinceon

I've been messing with this for hours now and can't seem to locate the
problem. Please help

Private Sub PostThePayment_Click()
On Error GoTo Err_PostThePayment_Click

Dim tmpCreditEarned As Integer
Dim tmpCreditReason As String
Dim tmpBalanceDue As Integer
Dim strSQL As String

If PaymentAmount > 0 Then
If Me.PaymentAmount > Me.TOTALOWED Then
tmpCreditEarned = Me.PaymentAmount - Me.TOTALOWED
tmpCreditReason = "Over Paid"
ElseIf Me.PaymentAmount < Me.TOTALOWED Then
tmpBalanceDue = Me.TOTALOWED - Me.PaymentAmount
Else
tmpCreditEarned = 0
tmpBalanceDue = 0
tmpCreditReason = Null
End If
strSQL = "UPDATE tblTenantLedger SET tblTenantLedger.CustNo = " &
Me!CustNo
strSQL = strSQL & ", tblTenantLedger.Unit = " & Me!Unit
strSQL = strSQL & ", tblTenantLedger.RentRate = " & Me!txtRentRate
strSQL = strSQL & ", tblTenantLedger.[Payment Date] = #" & Date &
"#"
strSQL = strSQL & ", tblTenantLedger.[Payment Amount] = " &
Me!PaymentAmount
strSQL = strSQL & ", tblTenantLedger.PaymentMethod = '" &
Me!cboPaymentMethod & "'"
strSQL = strSQL & ", tblTenantLedger.Tracking = '" &
Me!TrackingNumber & "'"
strSQL = strSQL & ", tblTenantLedger.PaidFrom = #" & Me!NewPaidFrom
& "#"
strSQL = strSQL & ", tblTenantLedger.PaidThru = #" & Me!txtPaidThru
& "#"
strSQL = strSQL & ", tblTenantLedger.Rent = " & Me!TOTALRENTOWED
strSQL = strSQL & ", tblTenantLedger.Administrationfee = " &
Me!AdmFee
strSQL = strSQL & ", tblTenantLedger.Lock = " & Me!PurLock
strSQL = strSQL & ", tblTenantLedger.LateFees = " & Me!txtLateFees
strSQL = strSQL & ", tblTenantLedger.NSFCheckFee = " & Me!PayNSFFee
strSQL = strSQL & ", tblTenantLedger.LockCutFee = " & Me!txtLockCut
strSQL = strSQL & ", tblTenantLedger.AuctionFee = " & Me!AuctionFee
strSQL = strSQL & ", tblTenantLedger.MiscChg = " & Me!MiscChgs
strSQL = strSQL & ", tblTenantLedger.MiscChgDesc = '" & Me!Combo116
& "'"
strSQL = strSQL & ", tblTenantLedger.Waved = " & Me!WavedAmt
strSQL = strSQL & ", tblTenantLedger.RentAllowance = " &
Me!RENTALLOWENCE
strSQL = strSQL & ", tblTenantLedger.RentAllReason = '" &
Me!Combo118 & "'"
strSQL = strSQL & ", tblTenantLedger.CreditApplied = " &
Me!CreditsApp
strSQL = strSQL & ", tblTenantLedger.CreditEarned = " &
tmpCreditEarned
strSQL = strSQL & ", tblTenantLedger.CreditReason = '" &
tmpCreditReason & "'"
strSQL = strSQL & ", tblTenantLedger.PreviousBalDue = " & Me!PrevBal
strSQL = strSQL & ", tblTenantLedger.BalanceDue = " & tmpBalanceDue
strSQL = strSQL & ", tblTenantLedger.Notes = '" & Me!Notes & "';"

' MsgBox strSQL

' DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
' DoCmd.SetWarnings True
End If

Exit_PostThePayment_Click:
Exit Sub

Err_PostThePayment_Click:
MsgBox Err.Description
Resume Exit_PostThePayment_Click

End Sub
 
G

Graham R Seach

Joe,

Have you tried using F8 to step through the code to locate the problem? You
don't say what the problem is (syntax error is a little vague), and I can't
find any syntax errors; even your SQL statement is OK.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Joe Cilinceon said:
I've been messing with this for hours now and can't seem to locate the
problem. Please help

Private Sub PostThePayment_Click()
On Error GoTo Err_PostThePayment_Click

Dim tmpCreditEarned As Integer
Dim tmpCreditReason As String
Dim tmpBalanceDue As Integer
Dim strSQL As String

If PaymentAmount > 0 Then
If Me.PaymentAmount > Me.TOTALOWED Then
tmpCreditEarned = Me.PaymentAmount - Me.TOTALOWED
tmpCreditReason = "Over Paid"
ElseIf Me.PaymentAmount < Me.TOTALOWED Then
tmpBalanceDue = Me.TOTALOWED - Me.PaymentAmount
Else
tmpCreditEarned = 0
tmpBalanceDue = 0
tmpCreditReason = Null
End If
strSQL = "UPDATE tblTenantLedger SET tblTenantLedger.CustNo = " &
Me!CustNo
strSQL = strSQL & ", tblTenantLedger.Unit = " & Me!Unit
strSQL = strSQL & ", tblTenantLedger.RentRate = " & Me!txtRentRate
strSQL = strSQL & ", tblTenantLedger.[Payment Date] = #" & Date &
"#"
strSQL = strSQL & ", tblTenantLedger.[Payment Amount] = " &
Me!PaymentAmount
strSQL = strSQL & ", tblTenantLedger.PaymentMethod = '" &
Me!cboPaymentMethod & "'"
strSQL = strSQL & ", tblTenantLedger.Tracking = '" &
Me!TrackingNumber & "'"
strSQL = strSQL & ", tblTenantLedger.PaidFrom = #" & Me!NewPaidFrom
& "#"
strSQL = strSQL & ", tblTenantLedger.PaidThru = #" & Me!txtPaidThru
& "#"
strSQL = strSQL & ", tblTenantLedger.Rent = " & Me!TOTALRENTOWED
strSQL = strSQL & ", tblTenantLedger.Administrationfee = " &
Me!AdmFee
strSQL = strSQL & ", tblTenantLedger.Lock = " & Me!PurLock
strSQL = strSQL & ", tblTenantLedger.LateFees = " & Me!txtLateFees
strSQL = strSQL & ", tblTenantLedger.NSFCheckFee = " & Me!PayNSFFee
strSQL = strSQL & ", tblTenantLedger.LockCutFee = " & Me!txtLockCut
strSQL = strSQL & ", tblTenantLedger.AuctionFee = " & Me!AuctionFee
strSQL = strSQL & ", tblTenantLedger.MiscChg = " & Me!MiscChgs
strSQL = strSQL & ", tblTenantLedger.MiscChgDesc = '" & Me!Combo116
& "'"
strSQL = strSQL & ", tblTenantLedger.Waved = " & Me!WavedAmt
strSQL = strSQL & ", tblTenantLedger.RentAllowance = " &
Me!RENTALLOWENCE
strSQL = strSQL & ", tblTenantLedger.RentAllReason = '" &
Me!Combo118 & "'"
strSQL = strSQL & ", tblTenantLedger.CreditApplied = " &
Me!CreditsApp
strSQL = strSQL & ", tblTenantLedger.CreditEarned = " &
tmpCreditEarned
strSQL = strSQL & ", tblTenantLedger.CreditReason = '" &
tmpCreditReason & "'"
strSQL = strSQL & ", tblTenantLedger.PreviousBalDue = " &
Me!PrevBal
strSQL = strSQL & ", tblTenantLedger.BalanceDue = " & tmpBalanceDue
strSQL = strSQL & ", tblTenantLedger.Notes = '" & Me!Notes & "';"

' MsgBox strSQL

' DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
' DoCmd.SetWarnings True
End If

Exit_PostThePayment_Click:
Exit Sub

Err_PostThePayment_Click:
MsgBox Err.Description
Resume Exit_PostThePayment_Click

End Sub
Click to expand...
 
J

John Vinson

I've been messing with this for hours now and can't seem to locate the
problem. Please help
Click to expand...

Well, I can't reliably debug 85 lines of VBA code without a little
more information. What error message are you getting? If you click
Debug... Compile <your database> does it highlight one line of the
code?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA code issue...Would love expert help. 5
Referencing Forms 5
Error 2465; Can't Find Field 8
What's wrong w/ this SQL stmt? 1
Syntax (Missing Operator) in Query Expr .... 2
Error in multiple select in a list box code 4
VBA works in orignal but fails in copy of database 1
Code will work on one table and form but not on another? 19

Top