Updating table data with SQL statement

[rawled]

I am trying to update data in a table named tblTest using an unbound form and SQL statement. I will type the data in unbound text boxes on the form. Then I will use those data in SQL statement and finally that SQL will update the data in the table. All fields in the table are of Short Text format except the ID field which is Autonumber.

Private Sub btnUpdate_Click()
    Dim sql As String
    Dim A As Database
    Dim rCount As Integer
    Set A = CurrentDb()
    sql = "UPDATE tblTest SET SRoll = " & Me!txtSRoll & ", SName = " & Me!txtSName & " WHERE ID = " & Me!txtID & ""
    A.Execute sql, dbFailOnError
    rCount = A.RecordsAffected
    If rCount > 0 Then
        MsgBox "Contact updated"
    End If
End Sub

SRoll and SName are two fields on the table.
txtSRoll, txtSName, txtID are text boxes and btnUpdate is a command button on the form.
The above code gives me error. Am I missing something??
Note: Using bound forms is a crime in this project.

[twinnyfo]

rawled,

What is the error that you receive upon execution? And, where does the error occur (what line)? That might help us direct our help.

[twinnyfo]

rawled,

I did get a chance to try your code, and it looks like you have forgotten the quotes around your text variables.

Line 6 should look like this:

    sql = "UPDATE tblTest " & _
        "SET SRoll = '" & Me!txtSRoll & _
        "', SName = '" & Me!txtSName & "' " & _
        "WHERE ID = " & Me!txtID

All should work well after this.

[rawled]

Thanks twinnyfo.
Looks like I messed up with the quotes. It works fine now. And there will be two double quotes at the end of the sql statement... Cheers!!

[twinnyfo]

Glad I could hepp! I was able to have success with this SQL statement without the double quotes at the end. In terms of strings, the empty double quotes adds nothing to the string, so I do not believe it is required.