Further Help Please : SQL

[Mike Wilson]

Help please

I am trying to work out the event procedure for deleting a selected
record from a table.

The converted query gives me the following sql

DoCmd.RunSql "DELETE OrderD2.ProductId, OrderD2.ProductName,
OrderD2.[Item Weight], OrderD2.Qty, OrderD2.Lwt, OrderD2.OrderNo,
OrderD2.LineNo, OrderD2.OrderDate" _
& " FROM OrderD2 INNER JOIN DefaultPrd ON OrderD2.ProductId =
DefaultPrd.ProductId" _
& " WHERE (([OrderD2]![ProductId]=[DefaultPrd]![ProductId]) AND
((OrderD2.OrderNo)=[Forms]![Header2]![ONo]));"

The error I get both in the query and in the event procedure is

"Specify the Table from which you want to delete the record"

I have tried various rephrasings from other postings / references but I
still end up with an error message.

Any help gratefully received


Thanks

Mike

 

[PC Datasheet]

Mike,

How about some other approach?

1. If you display the record on a form, put the following code in the double
click event of any field in the record:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

2. Create a query that only returns the record you want to delete. Click on the
query icon on the toolbar at the top of the screen and change the query to a
Delete query. Put the following code in the click event of a button on your
form:
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfYourQuery"
DoCmd.SetWarnings True

 

[Mike Wilson]

Thank you for the idea, but my problem with that approach
is that the product record I want to remove is one of many
with the same order number detail.
The process is for an Order Entry database that allows a
selection of 60 products.
The order is confirmed and the records are transferred
with a default product.
There is a routine that allows for the cloning of product
details to a new order but if I clone the order I also
clone the default product, so if I can reference the
table containing the default product I can then use SQL to
delete the product item record and prevent duplication
There is another reason, I want to use the knowledge I am
gaining from the newsgroups to put an app together that
doesn't show any queries or macros - all done with code

Well thats what I hope to learn.. sorry for the
lengthy explanation

Mike

Mike,

How about some other approach?

1. If you display the record on a form, put the following code in the double
click event of any field in the record:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

2. Create a query that only returns the record you want to delete. Click on the
query icon on the toolbar at the top of the screen and change the query to a
Delete query. Put the following code in the click event of a button on your
form:
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfYourQuery"
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Help please

I am trying to work out the event procedure for deleting a selected
record from a table.

The converted query gives me the following sql

DoCmd.RunSql "DELETE OrderD2.ProductId, OrderD2.ProductName,
OrderD2.[Item Weight], OrderD2.Qty, OrderD2.Lwt, OrderD2.OrderNo,
OrderD2.LineNo, OrderD2.OrderDate" _
& " FROM OrderD2 INNER JOIN DefaultPrd ON OrderD2.ProductId =
DefaultPrd.ProductId" _
& " WHERE (([OrderD2]![ProductId]=[DefaultPrd]![ProductId]) AND
((OrderD2.OrderNo)=[Forms]![Header2]![ONo]));"

The error I get both in the query and in the event procedure is

"Specify the Table from which you want to delete the record"

I have tried various rephrasings from other postings / references but I
still end up with an error message.

Any help gratefully received

 

[Pieter Wijnen]

Generally I don't like the non SQL standard behaviour of MS access
''accessing 2 tables & deleting from 1"
therefore:
DELETE FROM TABLEA A WHERE EXISTS (SELECT 'X' FROM TABLEB B WHERE B.KEY =
A.KEY)

Pieter

Thank you for the idea, but my problem with that approach
is that the product record I want to remove is one of many
with the same order number detail.
The process is for an Order Entry database that allows a
selection of 60 products.
The order is confirmed and the records are transferred
with a default product.
There is a routine that allows for the cloning of product
details to a new order but if I clone the order I also
clone the default product, so if I can reference the
table containing the default product I can then use SQL to
delete the product item record and prevent duplication
There is another reason, I want to use the knowledge I am
gaining from the newsgroups to put an app together that
doesn't show any queries or macros - all done with code

Well thats what I hope to learn.. sorry for the
lengthy explanation

Mike

Mike,

How about some other approach?

1. If you display the record on a form, put the following code in the double
click event of any field in the record:
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

2. Create a query that only returns the record you want to delete. Click on the
query icon on the toolbar at the top of the screen and change the query to a
Delete query. Put the following code in the click event of a button on your
form:
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfYourQuery"
DoCmd.SetWarnings True

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Help please

I am trying to work out the event procedure for deleting a selected
record from a table.

The converted query gives me the following sql

DoCmd.RunSql "DELETE OrderD2.ProductId, OrderD2.ProductName,
OrderD2.[Item Weight], OrderD2.Qty, OrderD2.Lwt, OrderD2.OrderNo,
OrderD2.LineNo, OrderD2.OrderDate" _
& " FROM OrderD2 INNER JOIN DefaultPrd ON OrderD2.ProductId =
DefaultPrd.ProductId" _
& " WHERE (([OrderD2]![ProductId]=[DefaultPrd]![ProductId]) AND
((OrderD2.OrderNo)=[Forms]![Header2]![ONo]));"

The error I get both in the query and in the event procedure is

"Specify the Table from which you want to delete the record"

I have tried various rephrasings from other postings / references but I
still end up with an error message.

Any help gratefully received