problem to Update a record?

[bunty]

Hi all,
I am trying to update some record of my table.The code I have written is not working.Its giving syntax error.I dont know where is the problem?Please tell me what need to change in that code.


Private Sub cmdUpdate_Click()
Dim rs As DAO.Recordset, db As DAO.Database
Dim res As Boolean
Dim query As String
Dim temp
Set db = CurrentDb
query = " Update User set Last_Name='" & txtUserId & "',First_Name= '" & txtFirstName& "',Email_Id= '" & txtEmailId & "',User_Type= '"cmbUserType & "'from User where User_Id='" & txtUserId & "'"
db.Execute query
'If txtUserId = "" Then
'MsgBox "Please select Record first"
If Err.Number = 0 Then
MsgBox "Record Updated successfully." & db.RecordsAffected
Else
MsgBox "Record not Updated The error is: " & Err.DESCRIPTION
End If
End Sub


Thanks.....

[geekgirlau]

You need to test the query.

Before db.Execute query add

Debug.Print query

You can then copy the query string, and see if that query will actually work.

[bunty]

Hi,
I am getting syntex error in update command called expected end of statement.The way I am written is wrong.Please correct it.I am accepting a value through the form.

thanks

[geekgirlau]

Yes, but it's attempting to update based on the query string. If the query string is wrong, you'll get an error. You need to see what the query string translates to with all your variables in it (by using Debug.Print) and then test to see if that query will work on your data.

[bunty]

hi..
I chked and i am getting this values :in query
Update User SET Last_Name=Test,First_Name= Tes,User_Type= Admin from User where User_ID = 123456;
According to me these value should update in a table.But error is coming.If u have any alternate way so please give me the code.
thanks

[geekgirlau]

There has to be quotation marks around any text values, and your code appears to have the quotation marks. Please copy EXACTLY what you see when you use Debug.Print.

[bunty]

hi..
I am getting these values when i have pointed the cursor on

Debug.Print query .This is the value which i enter through form.
Update User SET Last_Name=Test,First_Name= Tes,User_Type= Admin, where User_ID = 123456;

thanks

[geekgirlau]

I chked and i am getting this values :in query
Update User SET Last_Name=Test,First_Name= Tes,User_Type= Admin from User where User_ID = 123456;
According to me these value should update in a table.

You need to check the Immediate window - either press [Ctrl-G] or select View, Immediate Window. When you use [F8] to step through the code, Debug.Print will put the value in this window.

Based on your code, there is NO way that what you have entered above is what your code will produce. Your code has quotation marks, and I'm not seeing any of those in what you posted.