Access Form - Syntax error (missing operator) in query expression

[jagu147]

hi,

i am geeting Syntax error (missing operator) in query expression when i run the below code.

conwatt.Execute "update " & StrUserWinID & " set Auditors_comments= '" & Form_auditor.Text87 & "' where ACAPS like '" & WindowsUserID & "'"

i get the error only when Form_auditor.Text87 value contain any special charcters like ' .Please advice. Thank you!

Regards,
jagu147

 

[TerryHogarth21]

hi,

i am geeting Syntax error (missing operator) in query expression when i run the below code.

conwatt.Execute "update " & StrUserWinID & " set Auditors_comments= '" & Form_auditor.Text87 & "' where ACAPS like '" & WindowsUserID & "'"

i get the error only when Form_auditor.Text87 value contain any special charcters like ' .Please advice. Thank you!

Regards,
jagu147

When you are in the immediate window (Control + G) what does the whole string look like when you print it?

The form field should look something like this -> Forms![FormName]![TextName]

Maybe try

 conwatt.Execute "update " & StrUserWinID & " set  Auditors_comments= '" & Forms![Form_auditor].[Text87] & "' where ACAPS  like '" & WindowsUserID & "'"

 

[jagu147]

hi,

Thank you for the reply. But i am getting the same error again.
string valu= Forms![auditor].[Text87] =morgans's gate 2521. please advice.

regards,
jagu147

 

[TerryHogarth21]

hi,

Thank you for the reply. But i am getting the same error again.
string valu= Forms![auditor].[Text87] =morgans's gate 2521. please advice.

regards,
jagu147

The field in the table that is being updated based on the form value (I think it's Auditors_comments) - is that set as numbers or text? It should be as text if you are allowing to put text in that form's text box.

Make sure your naming convention is spelled correct as well. You might as well go into design mode and put a name for that text box for the value to make things easier on yourself.

If you could post the full code it would make it easier as well.

 

[jagu147]

the table is set as text and it works without any error if there is no special charecters like '

Public Sub submit_data1()
Dim conwatt As ADODB.Connection
Dim Recsetwatt As ADODB.Recordset
Dim strSQL As String
Dim intRoleID As Integer
Dim strRole As String

On Error GoTo ErrorHandler
Set conwatt = New ADODB.Connection
Set Recsetwatt = New ADODB.Recordset
strSQL = ""
'Exit if the user name is not selected from the list.
If Form_UserMgt.cmbProcessor.ListIndex = 0 Then Exit Sub

WindowsUserID = Form_auditor.Combo80.Value
Form_auditor.Text40.Value = WindowsUserID
'Call Clear_UserMgt_Fields
' Call Set_Public_variables
conwatt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDatabase_Path & "jay.mdb" & ";"


strSQLCheckUserExists = "Select * FROM " & StrUserWinID & " where ACAPS = '" & WindowsUserID & "'"

Recsetwatt.Open strSQLCheckUserExists, conwatt, adOpenDynamic, adLockOptimistic
With Recsetwatt
'Text87
conwatt.Execute "update " & StrUserWinID & " set Auditors_comments= '" & Forms![auditor].[Text87] & "' where ACAPS like '" & WindowsUserID & "'"
'conwatt.Execute "update " & StrUserWinID & " set Auditors_comments= '" & Form_auditor.Text87 & "' where ACAPS like '" & WindowsUserID & "'"

End With
Recsetwatt.Close
conwatt.Close

Set Recsetwatt = Nothing
Set conwatt = Nothing
Call populate_picks1
Exit Sub
End Sub

 

[TerryHogarth21]

You are going to have to use one of the available options with handle apostrophe's in writing your query.

Microsoft Access tips: Quotes within quotes

I prefer the double quotation method that the above website mentions.

So perhaps try this for the line causing the issue where an apostrophe can be in the text field.

conwatt.Execute "update " & StrUserWinID & " set   Auditors_comments= "" & Forms![auditor].[Text87] & """ where  ACAPS  like '" & WindowsUserID & "'"