Update SQL Error

[Emma Aumack]

Hello all,

Trying to code a "clear" flag field button on a form using Update SQL
statement. Please see the following code:

Private Sub btn_ClFlag_Click()
On Error GoTo Err_btn_ClFlag_Click

Dim strSQL As String
Dim flgFieldNo As String
Dim txtFlgfield As String

flgFieldNo = InputBox("Enter Flag #:", "Flag Field")
txtFlgfield = InputBox("Enter Flag Criteria to be Cleared. i.e.",
"Clear Flag" & flgFieldNo)

If Me.Dirty Then
Me.Dirty = False
End If

strSQL = "UPDATE Tbl_Contacts SET Tbl_Contacts.Flag" & _
flgFieldNo & "WHERE ((Tbl_Contacts.Flag" & flgFieldNo & ") = " &
txtFlgfield & ");"

DBEngine(0)(0).Execute strSQL, dbFailOnError

Me.Requery

Exit_btn_ClFlag_Click:
Exit Sub

Err_btn_ClFlag_Click:
msgbox Err.Description
Resume Exit_btn_ClFlag_Click

End Sub

Can someone tell me what is wrong with the SQL statement? Maybe quotes in
the wrong place?

 

[Ofer Cohen]

There are few things that I can see
1. I can't see which value you are setting the Tbl_Contacts.Flag
2. Missing spaces for each line
3. If it a string field you need to add single quote

' For number
"UPDATE Tbl_Contacts SET Tbl_Contacts.FileName = " & SomeValue & " WHERE " & _
" Tbl_Contacts.FileName = " & SomeValue

' For text
"UPDATE Tbl_Contacts SET Tbl_Contacts.FileName = '" & SomeValue & "' WHERE "
& _
" Tbl_Contacts.FileName = '" & SomeValue & "'"

If you need more help, I need to know the field type and which value you
want to assign in the update

 

[Emma Aumack]

Thanks Ofer,

Both the flgFieldNo and the txtFlgField are text that are input by the user.
I am doing it this way because I have 9 flag fields and I don't want to have
to program a button for each flag field.

I tried to update the code but am failing miserably. Still getting the
syntax error in UPDATE Statement message.

Here is my updated code:

Private Sub btn_ClFlag_Click()
On Error GoTo Err_btn_ClFlag_Click

Dim strSQL As String
Dim flgFieldNo As String
Dim txtFlgfield As String

flgFieldNo = InputBox("Enter Flag #:", "Flag Field")
txtFlgfield = InputBox("Enter Flag Criteria to be Cleared. i.e.",
"Clear Flag" & flgFieldNo)

If Me.Dirty Then
Me.Dirty = False
End If

strSQL = "UPDATE Tbl_Contacts SET Tbl_Contacts.Flag'" & flgFieldNo & "
="" WHERE ((Tbl_Contacts.Flag'" & flgFieldNo & "'" & ") = '" & txtFlgfield &
"'" & ");"

DBEngine(0)(0).Execute strSQL, dbFailOnError

Me.Requery

Exit_btn_ClFlag_Click:
Exit Sub

Err_btn_ClFlag_Click:
msgbox Err.Description
Resume Exit_btn_ClFlag_Click

End Sub

Can you please help.

 

[Ofer Cohen]

Try

strSQL = "UPDATE Tbl_Contacts SET Tbl_Contacts.Flag" & flgFieldNo & _
" = '' WHERE ((Tbl_Contacts.Flag" & flgFieldNo & ") = '" & txtFlgfield & "')"

Assuming that you want to update the field with empty value

 

[Emma Aumack]

YES!!!!! Success!! Thank you! I'm going to save this and study it thoroughly!