Record Deleted Recordset Using SQL Query

[DDTiff]

Hi,

Can someone help me with this question? I thank you for your help in advance.

I have a DB in MS Access with two tables: Task and Delete
The Task and Delete tables have the same number and types of fieldnames, and the primary key of the Task table is called TP_NO.

I also created a Task Form in MS Access which consists of all the fields of the Task table.
My goal is to be able to delete the task information from the Task table and paste the task information to the Delete table through a command button that composed of two SQL queries.

I put the queries into two calling procedures and they are as follow:

============================================================
Public sub RecordDeleteTask()

Dim RecDelTask as string
RecDelTask="INSERT INTO [DELETE](TP_NO, Taskname, OrgName) SELECT(Task.TP_NO, Task.Taskname, Task.OrgName)
FROM Task; "

Docmd.RunSQL RecDelTask
End Sub
================================================================


Public Sub DeleteTask()
Dim DelTask as string
DelTask="DELETE * FROM Task"

Docmd.RunSQL DelTask
End Sub
==================================================================


Private Sub cmdDelete_Click()

Call RecordDeleteTask 'record the deleted task from Task table to Delete table then
Call DeleteTask 'delete the task from Task table

End Sub
========================================================


The Delete command button did run, but it tends to delete all the record in the Task table, and my goal
is to only delete the selected task that is shown on the form.

The following example is what I would like to see happening:

If the TP_No on the Task Form is equal to "2". Once I click the Delete button
the two calling procedures in the Delete button would run, then Task information in the Task
table where the TP_NO is equal to "2" would be deleted.

I think I need a WHERE statement somewhere in the above queries that equal to the TP_NO variable of the Task Form.

Please let me know if you still need more information this function. Again, I appreciate your help and time.



Diem

 

[tlbroadbent]

Add the Where clause in the following manner.

DelTask="DELETE * FROM Task Where TP_No=" & CStr(Me.TP_No)

You should post Access questions in a Microsoft Access forum. See forum701 and fourm702.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[DDTiff]

Thank you very much for your help Terry. The codes worked really well. I will make sure I post the question to right forum next time. Once again, your help is appreciate. Look forward to learn more from you in the near future.

V/R,

Diem