0

I receive “Syntax Error in FROM clause”

Any help/ideas are greatly appreciated, I’m a beginner if you can’t tell!

CODE is as follows:

Private Sub cmdDelete_click()
Dim sql As String, rCount As Integer
If me.dirty then
Me.dirty = False
End if
Set dbs = currentdb
SQL = “DELETE Item FROM item = ‘“ & me.txtItem & “‘“ &                  “WHERE ID=“ & me.txtID2

Dbs.Execute sql, dbFailOnError
rCount = dbs.RecordsAffected
If rCount >0 then
Msgbox “The item List has been updated”
List40.Requery
Clear
End if
End sub
Improve this question
6
  • 3
    Please don't build up SQL queries via string concatenation. That leads to SQL Injection. Commented Nov 21, 2018 at 9:15
  • What is the name of the table you are trying to delete from? How would you identify the row(s) to delete (column name and type, value)? Commented Nov 21, 2018 at 9:40
  • The table is "Item" and the .field is Item the field consists of auto parts Commented Nov 21, 2018 at 9:40
  • After double click "listbox" info is placed in textbox "txtItem" and Id is placed in txtID2 Commented Nov 21, 2018 at 9:41
  • sorry yes ms-access Commented Nov 21, 2018 at 9:43

3 Answers 3

Reset to default
1

FROM must refer to a table (whether a physical table you've built, a stored query, or the result of an embedded SQL string).
FROM item = ‘“ & me.txtItem & “‘“ in your code has no meaning to SQL.

This avoids the string concatenation to build up your SQL - just pass the value as a parameter and then execute the query:

Private Sub cmdDelete_Click()

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.CreateQueryDef("", _
        "PARAMETERS Identifier TEXT (255); " & _
        "DELETE * FROM Table1 WHERE ID = Identifier")

    With qdf
        .Parameters("Identifier") = Me.txtID2
        .Execute
    End With

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

When I use this i received the following: Runtime Error (3464) Datatype Mismatch in criteria expression.
Sorry, misread the SQL in your question. ID is a numeric field so replace TEXT (255) with Long.
1

You can simply use DELETE in SQL like this.

DELETE FROM Item WHERE ID='" + txtID2.Text + "'

Where you can delete an item base on the items ID.

Improve this answer

7 Comments

You can assign the value of you ID to a parameter like this. SqlCmd.Parameters.AddWithValue("@ID", txtID2.Text) then the query will call the value of the ID.
When I change the sql statement as both of you suggested, it still doesn’t function.
@skooter "doesn't function" does not say much. Do you get any errors? Please specify.
@Unknown Be careful with AddWithValue
Looks like the OP is currently using Database.Execute in Access. Makes parameterization more difficult. Don't think it has named parameters and I don't think this method has a means of passing parameters.
|
0

I have Read your Question the Error in the Your SQL QUERY Try This:

 SqlCommand cmd = new SqlCommand();
 cmd.CommandType = CommandType.Text;
 cmd.CommandText ="DELETE FROM Item WHERE  ID='"+txtyoutextbox.Text.Trim()+"';
 cmd.ExecuteNonQuery();
Improve this answer

7 Comments

I'm trying to create a userform with ability to delete from unbound textbox with ID textbox associated. What is best way?
you can get textboxbox value and store into int variable int id=Convert.ToInt32(txtyourtextboxname.text); THen Fire the Query Like DELETE FROM Item WHERE ID='"+id+" OR DELETE FROM Item WHERE ID='"+Convert.ToInt32(txtyourtextboxname.text.Trim())+";
I have Updated My answer try this in your application replace txtyoutextbox to your textbox name...
+txtyoutextbox.Text.Trim()+ doesn't look like VBA syntax.
But the tag was c# so used c# syntax
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.