0

i want to execute a delete-query based on the value of a textfield. is there a way to fill that WHERE-Clause in the SQL-statement with the value of a textfield from my form? The Name of the textfield is SID

Private Sub delSYS_Click()
  DoCmd.OpenQuery "qry-delSystem"
End Sub

SQL-Statement from the query:

DELETE [tbl-planung].SID, [tbl-apartner].*
FROM [tbl-planung] INNER JOIN [tbl-apartner] ON [tbl-planung].SID = [tbl-apartner].SID
WHERE ((([tbl-planung].SID)=Me!SID));

When i execute that query, it always asks me for the value of Me!SID. My goal is to delete the System from the table "tbl-planung" including the matching record in the contacts table "tbl-apartner" where the Value of "SID" is identical.

rel0aded0ne
  • 451
  • 2
  • 5
  • 17
  • AFAIK you can't use parameterised queries with DoCmd - but you could use ADODB instead with parameterised queries. N.B. don't be tempted to append the value to the end of your SQL using string concatenation - it will leave you vulnerable to SQL Injection – CHill60 Jun 26 '23 at 08:51

1 Answers1

0

Try this modification:

PARAMETERS ThisId Long;
DELETE [tbl-planung].*, [tbl-apartner].*
FROM [tbl-planung] INNER JOIN [tbl-apartner] ON [tbl-planung].SID = [tbl-apartner].SID
WHERE [tbl-planung].SID = ThisId;

and then execute the query:

Private Sub delSYS_Click()

    Dim Query As DAO.QueryDef

    Set Query = CurrentDb.QueryDefs("qry-delSystem")
    Query.Parameters("ThisId").Value = Me!SID.Value
    Query.Execute
    Query.Close

End Sub
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • When i execute the query i get the message "Cannot delete from specified tables" – rel0aded0ne Jun 26 '23 at 10:23
  • Yes. You may have to run two delete queries. – Gustav Jun 26 '23 at 10:54
  • See this question and answer I provided the other day. Use a function to pass the SID value to your query: https://stackoverflow.com/questions/76537148/ms-access-using-the-value-of-the-field-in-a-given-column-of-the-selected-row-of/76548345#76548345 – BobS Jun 26 '23 at 23:39