By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,278 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

Updating table data with SQL statement

P: 10
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnUpdate_Click()
  2.     Dim sql As String
  3.     Dim A As Database
  4.     Dim rCount As Integer
  5.     Set A = CurrentDb()
  6.     sql = "UPDATE tblTest SET SRoll = " & Me!txtSRoll & ", SName = " & Me!txtSName & " WHERE ID = " & Me!txtID & ""
  7.     A.Execute sql, dbFailOnError
  8.     rCount = A.RecordsAffected
  9.     If rCount > 0 Then
  10.         MsgBox "Contact updated"
  11.     End If
  12. 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.
Jan 3 '18 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1.     sql = "UPDATE tblTest " & _
  2.         "SET SRoll = '" & Me!txtSRoll & _
  3.         "', SName = '" & Me!txtSName & "' " & _
  4.         "WHERE ID = " & Me!txtID
All should work well after this.

Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,131
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.
Jan 3 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 3,131
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:
Expand|Select|Wrap|Line Numbers
  1.     sql = "UPDATE tblTest " & _
  2.         "SET SRoll = '" & Me!txtSRoll & _
  3.         "', SName = '" & Me!txtSName & "' " & _
  4.         "WHERE ID = " & Me!txtID
All should work well after this.
Jan 3 '18 #3

P: 10
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!!
Jan 4 '18 #4

twinnyfo
Expert Mod 2.5K+
P: 3,131
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.
Jan 4 '18 #5

Post your reply

Sign in to post your reply or Sign up for a free account.