How to Delete selected data in one table by referencing another table info

[Emmanuel Ayivi]

I have an ms access database in which there exit two(2) tables thus tblstudents and tbltrans. The field ID is common to both tables whereas boarder is the criteria and referencing field in tblstudents that I want to use.

Now, I want the system to delete only the selected students in the tbltrans table base on the criteria boarder. Below is the code I used but not working well for me. plse help me

strSQL = "Select * from students where Level BETWEEN " _
& """" & LL & """ and """ & UL & """ and boarder = '';"
DoCmd.RunSQL strSQL

strSQL1 = "DELETE from rstrans (select * from strSQL where " _
& "(((rstrans.trans_code) in ('219')));"
DoCmd.RunSQL strSQL1

[GKJR]

Those queries don't really make sense to me. I don't think you need two different queries either. I'm assuming you have all of your variables defined and all of your table and field names are spelled correctly.

Use the query design grid to make a query that selects the records you need. You can use more than one field to filter the results.

Go to the SQL view of the query and copy the code.

Paste it into you VBA editor where you want it. You can create a string variable to hold the code or you can just execute it as is.

Change the "SELECT" to "DELETE" and use DoCmd.RunSQL similar to what you have above.

[Dlovan]

try it

strSQL = "Select * from students where Level BETWEEN " _
& """" & tablename.LL & """ and """ & tablename.UL & """ and tablename.boarder = '';"