URGENT !!! Delete query

[swathin]

Hi,

Can some one please tell me how I can use a delete query. I have the following code, but it does not work. Please let me know where I am going wrong
---------------------------------
Dim StrSQL As String
StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & PLANT_CODE_txt & " ' "
DoCmd.RunSQL StrSQL
----------------------------------
In the above and below code PLANT_CODE_txt is the form field that I am taking values from to delete from the table

(or)

----------------------------------------
Dim StrSQL As String
dim pt_code as string
pt_code = me.PLANT_CODE_txt
StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & pt_code & " ' "
DoCmd.RunSQL StrSQL
--------------------------------------------------------------


And if we are using multiple delete queries should we have the DoCmd.RunSQL <variable> after every delete statement or is it after all the statements


Thanks

 

[Sergeant]

Dim StrSQL As String
StrSQL = "Delete PLANT_CODE FROM PLANT WHERE PLANT_CODE = ' " & PLANT_CODE_txt & " ' "
DoCmd.RunSQL StrSQL

 

[neileg]

Sargeant is correct if there is only one field [PLANT_CODE] in the table. If there is more than that, use
Delete * FROM PLANT etc

 

[allan57]

Try removing the space from in between the apostrophe & double quotation

i.e.

DELETE Table_Name.*, Table_Name.Field_Criteria
FROM Table_Name
WHERE Table_Name.Field_Criteria='" & String_Criteria & "';

 

[Sergeant]

Sargeant is correct if there is only one field [PLANT_CODE] in the table. If there is more than that, use
Delete * FROM PLANT etc

Actually, it works both ways no matter how many fields there are...but I like your method better anyway.

 

[neileg]

Actually, it works both ways no matter how many fields there are...but I like your method better anyway.

Does it? You learn something new every day!

 

[swathin]

Hi Guys,

Thanks for the reply. It worked.Now coming to my next question

And if we are using multiple delete queries should we have the DoCmd.RunSQL <variable> after every delete statement or is it after all the statements

I need this because, I am trying to delete all records in the child tables that have the PLANT_CODE = "XYZ" , when I select this particular record in the parent table.



Please let me know

 

[Sergeant]

Perhaps referential integrity could solve the problem?

If you do use multiple sql statements, you have to run them one at a time, as far as I know.