SQL Delete query in VBA

[isaacski]

Alright. I give...

I have combed through these forums and tried a very simple delete query in VBA a couple different ways. I can't seem to get it. I have an unbound combobox on my form whose source is a two column form name and form ID (form id is the bound column). I have a button that I would like to run a delete query that deletes the Form with the ID that matches what is in the drop down (a unique ID).

Currently I have,

CurrentDb.Execute _
"DELETE FormID FROM reftblform" & _
"WHERE (reftblform.ScoreID=" & Forms!frmformmgt.cbofrmfilter & ")"

however, I have tried the DoCmd.RunSQL (DoCmd.RunSQL ("DELETE * FROM reftblform WHERE reftblform.FormID =" & Me.cbofrmfilter; "), and I have tried defining a string and running the string

each way has given me an error of some sort. Most commonly, and in the one above, it says there is a syntax error in my FROM clause. I tried putting it down below with &_ to the same error...

can someone please tell me why this incredibly simple SQL statement is having troubles?

:banghead:
K

 

[pbaldy]

You can use this technique to debug the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, check out the spaces between lines.

 

[CJ_London]

Because you have the syntax wrong

try

CurrentDb.Execute ("DELETE FormID FROM reftblform " & _
"WHERE (reftblform.ScoreID=" & Forms!frmformmgt.cbofrmfilter & ")")

You were

1. missing an opening bracket after Execute
2. missing a space between reftblform and WHERE
3. missing a closing bracket at the end

 

[pbaldy]

Because you have the syntax wrong

try

CurrentDb.Execute ("DELETE FormID FROM reftblform " & _
"WHERE (reftblform.ScoreID=" & Forms!frmformmgt.cbofrmfilter & ")")

You were

1. missing an opening bracket after Execute
2. missing a space between reftblform and WHERE
3. missing a closing bracket at the end

FYI, 1 and 3 are not required in my experience. I never include parentheses there.

 

[CJ_London]

Stand corrected - agree not necessary

 

[isaacski]

I was able to work it out using the following code. Thanks for both of your suggestions!

Dim strSQL As String
strSQL = "DELETE * FROM reftblform " & _
"WHERE (reftblform.FormID=" & Forms!frmformmgt.cbofrmfilter & ")"

DoCmd.RunSQL strSQL
Me.subfrmformmgt.Requery
End If

 

[pbaldy]

No problemo!

 

[CJ_London]

Happy to help