update query not working

[TioEd`65]

wrote update SQL last column ok when .runSQL then when breaks out of sub last
value dropped back to original. code below

Private Sub Command8_Click()
On Error GoTo Err_Command8_Click

'declare variables
Dim LName As String 'last name
Dim FName As String 'first name
Dim CkAmt As Currency 'Bad Check Amt
Dim CkFee As Currency 'bad ck fee
Dim AmtPd As Currency 'Amt reimbursed
Dim Status As String 'What state is pmnt due in
Dim mySQL As String 'SQL stmt

'assign values
LName = Me!Combo0
FName = Me!Combo2
CkAmt = Me!Combo4
AmtPd = Me!Text6
CkFee = Me!CkFee


'What is the status
Select Case AmtPd
'Payment >= amt owed
Case Is >= CkAmt + CkFee
Status = "P"
'payment < amt owed
Case Is < CkAmt + CkFee
Status = "L"
'End Select
End Select

'store sql in mySQL
'mySQL = "INSERT INTO Roach (RoachLastName, RoachFirstName, AmtPaid, Status,
RoachEntryDate) " & _
' "VALUES ('" & LName & "','" & FName & "','" & AmtPd & "','" &
Status & "','" & Date & "')"
mySQL = "UPDATE Roach SET Roach.AmtPaid ='" & AmtPd & "', Roach.Status = '"
& Status & "' WHERE roach.roachlastname = '" & LName & "' "
'run SQL to insert record
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL, 0

'DoCmd.OpenQuery ("Query3")



Exit_Command8_Click:


Exit Sub

 

[Ofer Cohen]

Few things about the SQL string

1. When adding a string variant to the SQL it needs single quote before and
after the value
2. When adding a Date variant to the SQL it needs # before and after the value
3. When adding a Numeric variant to the SQL it doesn't need anything.

So, in your Update SQL the AmtPd variant which is Numeric (currency) doesn't
need a single quote before and after the string

Try:
mySQL = "UPDATE Roach SET Roach.AmtPaid =" & AmtPd & ", Roach.Status = '"
& Status & "' WHERE roach.roachlastname = '" & LName & "'"

**********
Also, it's important to set the warnnings back on aftre you run the SQL

DoCmd.SetWarnings True

Or, run the SQL using
CurrentDb.Execute(mySQL), dbFailOnError

Without setting the warnnings On and Off