SQL Delete Record - Adds a new record!

[BSJWright]

Ok, here is my code:

Private Sub cmdRemove_Click()
On Error GoTo Err_cmdRemove_Click

Dim SQL As String
Dim stLinkCriteria As String
    
SQL = "DELETE * FROM Suppliers WHERE ((Suppliers.[Supplier Name])= '" & txtSupplierName & "') AND (Suppliers.[Supplier Code]='" & txtSupplierCode & "' );"
CurrentProject.Connection.Execute SQL
txtSupplierName = Null
txtSupplierCode = Null

Exit_cmdRemove_Click:
    Exit Sub

Err_cmdRemove_Click:
    MsgBox Err.Description
    Resume Exit_cmdRemove_Click

End Sub

I am trying to delete the supplier from a table called "Suppliers" where the Supplier Name and Code are the same as the text boxes txtSupplierName and txtSupplierCode respectively. I had to make the text box values null, otherwise the supplier was not actually deleted for some reason. The above code now works, but I now get a new "supplier" with blank Supplier Name and Supplier Code fields. Does anyone have any advice on how to stop this from happening please?

 

[Crilen007]

Why are you using text boxes?

Shouldn't you be using Combo boxes?

Not really sure what this is for..

 

[yhgtbfk]

I am trying to delete the supplier from a table called "Suppliers" where the Supplier Name and Code are the same as the text boxes txtSupplierName and txtSupplierCode respectively. I had to make the text box values null, otherwise the supplier was not actually deleted for some reason. The above code now works, but I now get a new "supplier" with blank Supplier Name and Supplier Code fields. Does anyone have any advice on how to stop this from happening please?

The SQL you have supplied is incorrect. You have simply used a select statement, but replaced select with delete.

DoCmd.RunSQL ("DELETE FROM table WHERE field = '" & textbox & "' AND field = '" & textbox & "'")

Try that.

 

[BSJWright]

Why are you using text boxes?

Shouldn't you be using Combo boxes?

Not really sure what this is for..

I use a combo box on another form when I want to look at other supplier details. This form is for adding or deleting suppliers only.

 

[BSJWright]

The SQL you have supplied is incorrect. You have simply used a select statement, but replaced select with delete.

DoCmd.RunSQL ("DELETE FROM table WHERE field = '" & textbox & "' AND field = '" & textbox & "'")

Try that.

Hmm, this hasn't worked. The "test" suppliers are still in the table - there are no new "blank" suppliers, though!

 

[BSJWright]

Hmm, this hasn't worked. The "test" suppliers are still in the table - there are no new "blank" suppliers, though!

Anyone got any other ideas? The code didn't work...

 

[Mile-O]

The SQL you have supplied is incorrect.

The SQL was correct.

 

[Mile-O]

I would suggest changing the SQL to:

SQL = "DELETE * FROM Suppliers WHERE Suppliers.[Supplier Name]= """ & Me.txtSupplierName & """ AND Suppliers.[Supplier Code]= """ & Me.txtSupplierCode & """;"

Also, rather than building SQL in code, just create a query and save it. For the criteria, you can reference the two textboxes on your form like so:

[Forms]![MyFormName]![txtSupplierName]

This will help to reduce database bloat as you will have a stored query definition rather than making and deleting one every time the code is run forcing more frequent compacting.