SQL Delete Error (1 Viewer)

[kermit5]

I am using the Delete statement to allow the user to delete an item from a list box by clicking a command button. I have the following code in the ON CLICK property

DOCMD.RunSQL ("DELETE FROM tblSelectedFields _
WHERE SelectedField = me.lstSelectedFields, _
MasterProjectID = me.cmbProject")

I get a compile error: Expected:List Separator or ) with the WHERE highlighted.

What is wrong with my syntax?

Scott

 

[Jacob Mathai]

try this :


change the comma (,) to an AND condition in the "where " part of the statement.

 

[kermit5]

I still get the same error.

 

[DataMiner]

Try putting the whole SQL statement on one line instead of using the line-extension character (_). I don't think you can split up a string like this.

So Try this:

DOCMD.RunSQL ("DELETE FROM tblSelectedFields WHERE SelectedField = me.lstSelectedFields and MasterProjectID = me.cmbProject")

OR if you really want to split up the lines do it like this instead:
SQLstring="DELETE FROM tblSelectedFields" & _
" WHERE SelectedField =me.lstSelectedFields" & _
" MasterProjectID = me.cmbProject"
DOCMD.RunSQL (SQLstring)

 

[kermit5]

Thanks!

That did the trick

 

[Pat Hartman]

That may have gotten you past the compile error, but the statement still won't work. You need to separate the variables so that VBA can parse them properly.

DOCMD.RunSQL ("DELETE FROM tblSelectedFields WHERE SelectedField = " & me.lstSelectedFields & " and MasterProjectID = " & me.cmbProject)

If the variables are text, they need to be surrounded with single or double quotes.

DOCMD.RunSQL ("DELETE FROM tblSelectedFields WHERE SelectedField = '" & me.lstSelectedFields & "' and MasterProjectID = '" & me.cmbProject & "'")