Deleting a row from a recordset that joins 2 tables

[mscertified]

I'm confused about what happens when I do a delete of a row in a recordset
that joins 2 tables. Does this attempt to delete rows from both tables or
only one table? I'm getting an error indicating it is trying to delete a row
from a table I dont want it to. Here is my code:

rs2.Open "SELECT TD.DTID, TD.DTValue, DT.DataType FROM tblTicketData As TD"
& _
" INNER JOIN tblTicketDataType As DT" & _
" ON TD.DTID = DT.ID WHERE TD.TicketID = " & Me!TicketNo, _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

When I do rs2.delete I want to remove the row from tblTicketData not from
tblTicketDataType. I'm getting an error complaining that it cannot delete
from tblTicketDataType due to a relationship with another table (which is
correct).

 

[John Smith]

How is Access to know which table you want to delete from if there are two in
the query?

If you only want to delete from tblTicketData I would suggest:

SQL = "DELETE FROM tblTicketData WHERE dtid IN" _
& " (SELECT id FROM tblTicketDataType WHERE TicketID = " & Me!TicketNo & ")"

Currentdb.Execute SQL, dbFailOnError

John
##################################
Don't Print - Save trees