DELETE query error message

[Guest]

Hi,

I am trying to perform a delete query from a form. I created a button that
invokes the query. When I perform the action I get the message that says
"The table 'Project Master' is already opened exclusively by another user, or
it is already open through the user interface and cannot be manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most of it.
Is there a setting that maybe they turned on that loks up the tables when a
form is open?

Thanks!!!

Heather

 

[Ken Snell [MVP]]

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

 

[Guest]

The command button to delete the entry is on the form called "Project Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a button that
invokes the query. When I perform the action I get the message that says
"The table 'Project Master' is already opened exclusively by another user, or
it is already open through the user interface and cannot be manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most of it.
Is there a setting that maybe they turned on that loks up the tables when a
form is open?

Thanks!!!

Heather

 

[Ken Snell [MVP]]

Yes, the form is bound to the table Project Master. So what you're getting
is an error message telling you that the form already has opened the table
Project Master and the query cannot "lock" the table so that it can delete
the records. You'll need to close the original form first or at least unlock
the record(s) that are being edited on that form, before you run the query.

Is the button for deleting the records being clicked while a record is being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>

The command button to delete the entry is on the form called "Project Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a button that
invokes the query. When I perform the action I get the message that says
"The table 'Project Master' is already opened exclusively by another

user,
or

it is already open through the user interface and cannot be manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most of it.
Is there a setting that maybe they turned on that loks up the tables

when
a

form is open?

Thanks!!!

Heather

 

[Guest]

What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is click on
"Delete" on the main form and have all of the entries on each of the tabs
with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query behind the
button. I just tried deleting from one of the tabs (with the code I wrote
below) and I got the error. After I got the first tab delete code written I
was going to try and create a delete query for all of the other tabs and then
copy all of that code behind one button. What do you think? I don't know
what to do. Thanks!!

Yes, the form is bound to the table Project Master. So what you're getting
is an error message telling you that the form already has opened the table
Project Master and the query cannot "lock" the table so that it can delete
the records. You'll need to close the original form first or at least unlock
the record(s) that are being edited on that form, before you run the query.

Is the button for deleting the records being clicked while a record is being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>

The command button to delete the entry is on the form called "Project Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a button
that
invokes the query. When I perform the action I get the message that says
"The table 'Project Master' is already opened exclusively by another user,
or
it is already open through the user interface and cannot be manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most of it.
Is there a setting that maybe they turned on that loks up the tables when
a
form is open?

Thanks!!!

Heather

 

[Guest]

Hi...

Does anyone have any ideas? I tried to build a separate form where a user
could select a project number from a drop-down box and delete it, but I still
get the same error message. It won't delete from the other table. The
person that created the db set it up as a 1-1 relationship between the
tables. Any ideas?

Thanks!!!!!!!!!

What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is click on
"Delete" on the main form and have all of the entries on each of the tabs
with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query behind the
button. I just tried deleting from one of the tabs (with the code I wrote
below) and I got the error. After I got the first tab delete code written I
was going to try and create a delete query for all of the other tabs and then
copy all of that code behind one button. What do you think? I don't know
what to do. Thanks!!

Yes, the form is bound to the table Project Master. So what you're getting
is an error message telling you that the form already has opened the table
Project Master and the query cannot "lock" the table so that it can delete
the records. You'll need to close the original form first or at least unlock
the record(s) that are being edited on that form, before you run the query.

Is the button for deleting the records being clicked while a record is being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>

The command button to delete the entry is on the form called "Project Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

:

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a button
that
invokes the query. When I perform the action I get the message that says
"The table 'Project Master' is already opened exclusively by another user,
or
it is already open through the user interface and cannot be manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most of it.
Is there a setting that maybe they turned on that loks up the tables when
a
form is open?

Thanks!!!

Heather

 

[Ken Snell [MVP]]

So what you're wanting to do is delete the record in the parent table
(Project Master) and all the related records in the children records?

Assuming that this is so, why use a separate query to delete records when
you already have the data "open" in the form? First, go to Relationships
window and be sure that you have joins (relationships) established from
Project Master to all the children tables, using the Project Number field as
the linking field for all. Be sure that the relationships is set for
Referential Integrity and that Cascade Delete is checked "yes". By doing
this, if you delete the parent record in Project Master, all children
records will be deleted automatically as well.

Then, in your form, instead of running the delete query that you're trying
to run, just delete the record in the main form. The form will handle all
the deletions for you.

If you *must" run a separate query for some reason, then you'll need to
detach the form and subforms from the underlying tables (their record
sources need to be set to an empty string or else the main form needs to be
closed). All in all, if you can do it, I'd use the first approach noted
above.

--

Ken Snell
<MS ACCESS MVP>

What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is click on
"Delete" on the main form and have all of the entries on each of the tabs
with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query behind the
button. I just tried deleting from one of the tabs (with the code I wrote
below) and I got the error. After I got the first tab delete code written I
was going to try and create a delete query for all of the other tabs and then
copy all of that code behind one button. What do you think? I don't know
what to do. Thanks!!

Yes, the form is bound to the table Project Master. So what you're getting
is an error message telling you that the form already has opened the table
Project Master and the query cannot "lock" the table so that it can delete
the records. You'll need to close the original form first or at least unlock
the record(s) that are being edited on that form, before you run the query.

Is the button for deleting the records being clicked while a record is being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>

The command button to delete the entry is on the form called "Project Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

:

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a button
that
invokes the query. When I perform the action I get the message

that
says

"The table 'Project Master' is already opened exclusively by

another
user,

or
it is already open through the user interface and cannot be manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most

of
it.

Is there a setting that maybe they turned on that loks up the

tables
when

a
form is open?

Thanks!!!

Heather

 

[Guest]

Hi Ken,

Thanks so much for your help... I tried to do what you said in the 1st
method -- I went to the relationships and set them all up for referential
integrity and selected to cascade the delete. When I try to delete the
record by using a "Delete" on the main form I still get a message that says
"The table 'xxx' is already opened exclusively by another user, or it is
already open through the user interface and cannot be manipulated
programmatically". Any ideas?

Thanks!
Heather

So what you're wanting to do is delete the record in the parent table
(Project Master) and all the related records in the children records?

Assuming that this is so, why use a separate query to delete records when
you already have the data "open" in the form? First, go to Relationships
window and be sure that you have joins (relationships) established from
Project Master to all the children tables, using the Project Number field as
the linking field for all. Be sure that the relationships is set for
Referential Integrity and that Cascade Delete is checked "yes". By doing
this, if you delete the parent record in Project Master, all children
records will be deleted automatically as well.

Then, in your form, instead of running the delete query that you're trying
to run, just delete the record in the main form. The form will handle all
the deletions for you.

If you *must" run a separate query for some reason, then you'll need to
detach the form and subforms from the underlying tables (their record
sources need to be set to an empty string or else the main form needs to be
closed). All in all, if you can do it, I'd use the first approach noted
above.

--

Ken Snell
<MS ACCESS MVP>

What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is click on
"Delete" on the main form and have all of the entries on each of the tabs
with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query behind the
button. I just tried deleting from one of the tabs (with the code I wrote
below) and I got the error. After I got the first tab delete code written I
was going to try and create a delete query for all of the other tabs and then
copy all of that code behind one button. What do you think? I don't know
what to do. Thanks!!

Yes, the form is bound to the table Project Master. So what you're getting
is an error message telling you that the form already has opened the table
Project Master and the query cannot "lock" the table so that it can delete
the records. You'll need to close the original form first or at least unlock
the record(s) that are being edited on that form, before you run the query.

Is the button for deleting the records being clicked while a record is being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>


The command button to delete the entry is on the form called "Project
Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

:

Is the form that you're using bound to the table Project Master? Or to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a button
that
invokes the query. When I perform the action I get the message that
says
"The table 'Project Master' is already opened exclusively by another
user,
or
it is already open through the user interface and cannot be
manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project
Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most of
it.
Is there a setting that maybe they turned on that loks up the tables
when
a
form is open?

Thanks!!!

Heather

 

[Ken Snell [MVP]]

What is the code running for the "Delete" button when you click it?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks so much for your help... I tried to do what you said in the 1st
method -- I went to the relationships and set them all up for referential
integrity and selected to cascade the delete. When I try to delete the
record by using a "Delete" on the main form I still get a message that says
"The table 'xxx' is already opened exclusively by another user, or it is
already open through the user interface and cannot be manipulated
programmatically". Any ideas?

Thanks!
Heather

So what you're wanting to do is delete the record in the parent table
(Project Master) and all the related records in the children records?

Assuming that this is so, why use a separate query to delete records when
you already have the data "open" in the form? First, go to Relationships
window and be sure that you have joins (relationships) established from
Project Master to all the children tables, using the Project Number field as
the linking field for all. Be sure that the relationships is set for
Referential Integrity and that Cascade Delete is checked "yes". By doing
this, if you delete the parent record in Project Master, all children
records will be deleted automatically as well.

Then, in your form, instead of running the delete query that you're trying
to run, just delete the record in the main form. The form will handle all
the deletions for you.

If you *must" run a separate query for some reason, then you'll need to
detach the form and subforms from the underlying tables (their record
sources need to be set to an empty string or else the main form needs to be
closed). All in all, if you can do it, I'd use the first approach noted
above.

--

Ken Snell
<MS ACCESS MVP>

What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is click on
"Delete" on the main form and have all of the entries on each of the tabs
with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query behind the
button. I just tried deleting from one of the tabs (with the code I wrote
below) and I got the error. After I got the first tab delete code

written
I

was going to try and create a delete query for all of the other tabs

and
then

copy all of that code behind one button. What do you think? I don't know
what to do. Thanks!!

:

Yes, the form is bound to the table Project Master. So what you're getting
is an error message telling you that the form already has opened the table
Project Master and the query cannot "lock" the table so that it can delete
the records. You'll need to close the original form first or at

least
unlock

the record(s) that are being edited on that form, before you run the query.

Is the button for deleting the records being clicked while a record

is
being

edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>


The command button to delete the entry is on the form called "Project
Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

:

Is the form that you're using bound to the table Project Master?

Or
to a

query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created

a
button

that
invokes the query. When I perform the action I get the

message
that

says
"The table 'Project Master' is already opened exclusively by another
user,
or
it is already open through the user interface and cannot be
manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project
Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created

most
of

it.
Is there a setting that maybe they turned on that loks up the tables
when
a
form is open?

Thanks!!!

Heather

 

[Guest]

This is the code running behind the delete button on my main form (parent
form):


Private Sub Command67_Click()
On Error GoTo Err_Command67_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command67_Click:
Exit Sub

Err_Command67_Click:
MsgBox Err.Description
Resume Exit_Command67_Click

End Sub

What is the code running for the "Delete" button when you click it?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks so much for your help... I tried to do what you said in the 1st
method -- I went to the relationships and set them all up for referential
integrity and selected to cascade the delete. When I try to delete the
record by using a "Delete" on the main form I still get a message that says
"The table 'xxx' is already opened exclusively by another user, or it is
already open through the user interface and cannot be manipulated
programmatically". Any ideas?

Thanks!
Heather

So what you're wanting to do is delete the record in the parent table
(Project Master) and all the related records in the children records?

Assuming that this is so, why use a separate query to delete records when
you already have the data "open" in the form? First, go to Relationships
window and be sure that you have joins (relationships) established from
Project Master to all the children tables, using the Project Number field as
the linking field for all. Be sure that the relationships is set for
Referential Integrity and that Cascade Delete is checked "yes". By doing
this, if you delete the parent record in Project Master, all children
records will be deleted automatically as well.

Then, in your form, instead of running the delete query that you're trying
to run, just delete the record in the main form. The form will handle all
the deletions for you.

If you *must" run a separate query for some reason, then you'll need to
detach the form and subforms from the underlying tables (their record
sources need to be set to an empty string or else the main form needs to be
closed). All in all, if you can do it, I'd use the first approach noted
above.

--

Ken Snell
<MS ACCESS MVP>


What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project
Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main
form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is click on
"Delete" on the main form and have all of the entries on each of the tabs
with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query behind the
button. I just tried deleting from one of the tabs (with the code I wrote
below) and I got the error. After I got the first tab delete code written
I
was going to try and create a delete query for all of the other tabs and
then
copy all of that code behind one button. What do you think? I don't know
what to do. Thanks!!

:

Yes, the form is bound to the table Project Master. So what you're
getting
is an error message telling you that the form already has opened the
table
Project Master and the query cannot "lock" the table so that it can
delete
the records. You'll need to close the original form first or at least
unlock
the record(s) that are being edited on that form, before you run the
query.

Is the button for deleting the records being clicked while a record is
being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>


The command button to delete the entry is on the form called "Project
Master
- TEST". The data that is displayed on this form comes from the table
"Project Master". Does that help?

:

Is the form that you're using bound to the table Project Master? Or
to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I created a
button
that
invokes the query. When I perform the action I get the message
that
says
"The table 'Project Master' is already opened exclusively by
another
user,
or
it is already open through the user interface and cannot be
manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project
Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else created most
of
it.
Is there a setting that maybe they turned on that loks up the
tables
when
a
form is open?

Thanks!!!

Heather

 

[Ken Snell [MVP]]

Hmmm.... do you have another form open in the background by chance that is
bound to the Project Master table too?


--

Ken Snell
<MS ACCESS MVP>

This is the code running behind the delete button on my main form (parent
form):


Private Sub Command67_Click()
On Error GoTo Err_Command67_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command67_Click:
Exit Sub

Err_Command67_Click:
MsgBox Err.Description
Resume Exit_Command67_Click

End Sub

What is the code running for the "Delete" button when you click it?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Thanks so much for your help... I tried to do what you said in the 1st
method -- I went to the relationships and set them all up for referential
integrity and selected to cascade the delete. When I try to delete the
record by using a "Delete" on the main form I still get a message that says
"The table 'xxx' is already opened exclusively by another user, or it is
already open through the user interface and cannot be manipulated
programmatically". Any ideas?

Thanks!
Heather

:

So what you're wanting to do is delete the record in the parent table
(Project Master) and all the related records in the children records?

Assuming that this is so, why use a separate query to delete records when
you already have the data "open" in the form? First, go to Relationships
window and be sure that you have joins (relationships) established from
Project Master to all the children tables, using the Project Number field as
the linking field for all. Be sure that the relationships is set for
Referential Integrity and that Cascade Delete is checked "yes". By doing
this, if you delete the parent record in Project Master, all children
records will be deleted automatically as well.

Then, in your form, instead of running the delete query that you're trying
to run, just delete the record in the main form. The form will

handle
all

the deletions for you.

If you *must" run a separate query for some reason, then you'll need to
detach the form and subforms from the underlying tables (their record
sources need to be set to an empty string or else the main form

needs to
be

closed). All in all, if you can do it, I'd use the first approach noted
above.

--

Ken Snell
<MS ACCESS MVP>


What I have is a main form where the user enters a project number,
description, and some other information (into a table called Project
Master).
Then I have 5 sub-forms set up as tabs on the bottom half of the main
form.
Each of the tabs writes to a different table, but they all have "project
Number" as a foreign key (?). What i need to be able to do is

click
on

"Delete" on the main form and have all of the entries on each of

the
tabs

with the same project number deleted out of the database. I was trying to
set up a button to delete and was going to put a delete query

behind
the

button. I just tried deleting from one of the tabs (with the code

I
wrote

below) and I got the error. After I got the first tab delete code written
I
was going to try and create a delete query for all of the other

tabs
and

then
copy all of that code behind one button. What do you think? I

don't
know

what to do. Thanks!!

:

Yes, the form is bound to the table Project Master. So what you're
getting
is an error message telling you that the form already has opened the
table
Project Master and the query cannot "lock" the table so that it can
delete
the records. You'll need to close the original form first or at least
unlock
the record(s) that are being edited on that form, before you run the
query.

Is the button for deleting the records being clicked while a

record
is

being
edited? Can you tell us more about the form's setup?

--

Ken Snell
<MS ACCESS MVP>


The command button to delete the entry is on the form called "Project
Master
- TEST". The data that is displayed on this form comes from

the
table

"Project Master". Does that help?

:

Is the form that you're using bound to the table Project

Master?
Or

to a
query that is using Project Master as a source table?

--

Ken Snell
<MS ACCESS MVP>

Hi,

I am trying to perform a delete query from a form. I

created
a

button
that
invokes the query. When I perform the action I get the message
that
says
"The table 'Project Master' is already opened exclusively by
another
user,
or
it is already open through the user interface and cannot be
manipulated
programmatically." and it won't let me delete.

Here's my SQL for the delete:
DELETE [Project Master].*, [WS-A Scoring-BP].*, [Project
Master].[Project
Number], [WS-A Scoring-BP].[Project Number], *
FROM [Project Master] INNER JOIN [WS-A Scoring-BP] ON [Project
Master].[Project Number] = [WS-A Scoring-BP].[Project Number]
WHERE ((([Project Master].[Project Number])=[Forms]![Project
Master]![Project Number]) AND (([WS-A Scoring-BP].[Project
Number])=[Forms]![Project Master]![Project Number]));

Also, I'm working on this database after someone else

created
most

of
it.
Is there a setting that maybe they turned on that loks up the
tables
when
a
form is open?

Thanks!!!

Heather