Update SQL Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
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
 
Back
Top