Delete Queries

[macrobater]

First, I'm not a VBA programmer. I have basically created a program with
macros, forms, queries, reports, etc. I've searched all over this site for
an answer specific to my problem and even for areas where non-programmers can
ask their silly little questions. Please forgive me if I've trespassed.

I'm having trouble with a delete query. My intent is to delete records in
two tables with a one to many relationship. The record selected is based on
a form's text box entry. I have first created a select query and the correct
records are shown in both tables when I click on the View icon in the
toolbar. Then, using the toolbar, I change the select query to a delete
query. When I click on the view toolbar, the correct records from both
tables are shown.

The problem is when I click on the "Run" icon or try to open the query from
a macro, Access responds with the following message: "Specify the table
containing the records you want to delete." I've tried a number of different
angles, to no avail. I just don't understand why this message comes up when
the form that is opened and the query both point to the tables that contain
the records I want to delete.

 

[fredg]

First, I'm not a VBA programmer. I have basically created a program with
macros, forms, queries, reports, etc. I've searched all over this site for
an answer specific to my problem and even for areas where non-programmers can
ask their silly little questions. Please forgive me if I've trespassed.

I'm having trouble with a delete query. My intent is to delete records in
two tables with a one to many relationship. The record selected is based on
a form's text box entry. I have first created a select query and the correct
records are shown in both tables when I click on the View icon in the
toolbar. Then, using the toolbar, I change the select query to a delete
query. When I click on the view toolbar, the correct records from both
tables are shown.

The problem is when I click on the "Run" icon or try to open the query from
a macro, Access responds with the following message: "Specify the table
containing the records you want to delete." I've tried a number of different
angles, to no avail. I just don't understand why this message comes up when
the form that is opened and the query both point to the tables that contain
the records I want to delete.

I think you'll need to post the 2 query SQL's (copy and paste), and
the order in which the delete queries being run, so we can see what it
is you are doing.
When deleting records in a 1 to many relationship, you must delete the
many side records first, then the one side records.

 

[Dale Fye]

This particular message implies that your query is returning fields from
multiple tables, and that you have not indicated which records you want to
delete in your query (from table 1 or table 2). I don't know of any way to
delete records from multiple tables with a single query, unless you have
defined the relationships between the tables and have set Referential
Integrity and Cascading Deletes set to true. The other way to accomplish
this would be to run two queries, one to delete the records from the many
side of the relationship and another to delete the particular record from the
one side of the relationship.

Can you post the SQL of the query, so we can see what you are trying to do.

Dale

 

[macrobater]

After wasting two days and getting desperate enough to post here, I have come
across the solution myself. In case anyone cares, you can't run a delete
query like this and delete records from two tables simultaneously. I had to
create two delete seperate delete queries, one for each table. But you guys
already knew this.

 

[macrobater]

You and fredg are absolutely right. I discovered this as you guys were
spending your precious time responding to me. I am thankful and I do hope a
macrobator such as myself is welcome to continue posting my dilemnas as I
come across them. Thanks again.