This topic is locked

Copy multiple records from a table after record added

7/2/2009 4:59:27 AM
ASPRunnerPro General questions
B
Bryan author

Hi there,
I have 3 tables:
Requests

IT_Tasks

ToDo
All tables have a field called WorkType
Request Table

Fields: ID, JobID, WorkType, Required by

Data: 1, 1, Move Desk, 15/07/09
IT_Table

Fields: ID, WorkType, Description

Data: 1, Move Desk, Check Data Cabling

Data: 2. New Start, Create AD account

Data: 3, New Start, Order PC

Data: 4, Leaver, Disable AD account

Data: 5, Move Desk, Transfer telephone
ToDo table

Fields: Id, JobID, WorkType, Description, Complete
When I add a Request record, I need to take a copy of all the records from IT_Tasks to ToDo where the Worktype matches. So if I were to add a Request with a JobID of 1 and a worktype of "Move Desk", all records from IT_Tasks with "Move Desk" would copy to ToDo with the matching Request JobID assigned.
Adding a 2nd Request "Move Desk" record would create a duplicate set of data in ToDo but with a different JobID.
Can this be easily achieved using After Record Added events on the Request table?
Many thanks,

Bryan

J
Jane 7/2/2009

Bryan,
yes, you need to use After record added event for this purpose.

Here is just a sample:

str = "select WorkType, Description from IT_Table where WorkType='" & values("WorkType") & "'"

Set rstmp = server.CreateObject("ADODB.Recordset")

rstmp.open str,dbConnection
while not rstmp.eof

dbConnection.Execute "insert into ToDo (JobID, WorkType, Description) values ("&values("JobID")&",'"&rstmp("WorkType")&"','"&values("Description")&"')"

dbConnection.Execute "delete from IT_Table where WorkType='" & values("WorkType") & "'"

rstmp.movenext

wend
rstmp.close

set rstmp=nothing

B
Bryan author 7/2/2009

Works perfectly.
Thank you