Delete Query Error "Can't delete from specified tables

[Maureen227]

My Delete query uses only one table to delete from based on the query
result from another query using a different table. Ihave tried with the
unique records Yes and No.

My first query, get distinct values from an Item list, looks like this
and works great:
SELECT DISTINCT [JobLineItems].[ModelNum]
FROM JobLineItems;

Using that I identify the records in the products list that I want to
delete. This works also.
SELECT ProductsList.MFGName, ProductsList.ModelNum,
ProductsList.Description, ProductsList.ItemCost,
ProductsList.ItemRetail, ProductsList.DEPrice
FROM ProductsList, [A ModelNumQuery]
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

But, then I turn this query into a delete query. I get the error
message Cant not delete from specified tables.
DELETE ProductsList.*, ProductsList.ModelNum
FROM ProductsList
WHERE (((ProductsList.ModelNum)=[A ModelNumQuery].[ModelNum]));

If I put a model number in the query it works. Ex, WHERE
(((ProductsList.ModelNum)=[ ="AMP-100"

The Products list has ModelNum as its primary key and is not related to
any other tables. IT IS USED FOR LOOKUP.
I have tried it with DISTINCTROW and without. No good.

Any help would be appreciated.

 

[Nikos Yannacopoulos]

Maureen,

Try this approach:

DELETE * FROM ProductsList
WHERE ProductList.ModelNum In (SELECT ModelNum FROM JobLineItems)

This should do it.

HTH,
Nikos

 

[maureen227]

Maureen,

Try this approach:

DELETE * FROM ProductsList
WHERE ProductList.ModelNum In (SELECT ModelNum FROM JobLineItems)

This should do it.

HTH,
Nikos

Nikos

Now I get a dialogue box. Enter Parameter Value in title bar and the
text ProductsList.ModelNum over the textbox in the dialogue box.

What does this mean?

Thanks for your help

Maureen

 

[Nikos Yannacopoulos]

Maureen,

Most likely, it means there is a spelling mistake in the
ProductsList.ModelNum reference, so it doesn't get recognized as such.
Check to make sure the table and field name are correct in the query.
Looking at it closer, if you just copied and pasted my SQL statement,
then I'm the guilty party... it's because I skipped an "s" at the end of
the table name in the WHERE clause!

HTH,
Nikos

 

[maureen227]

Maureen,

Try this approach:

DELETE * FROM ProductsList
WHERE ProductList.ModelNum In (SELECT ModelNum FROM JobLineItems)

This should do it.

HTH,
Nikos

You are so totally forgiven for the missing s, I do that all the
time.....

The SQL worked beautifully when we fix the s ;-) Thank you!, thank
you! and thank you!.

Maureen
PS THANK YOU!!!!!!!