copy one recordset to another

[ajking]

Probably a basic question but I importing 3 excel sheets(Cust, tech, survey) into access tables "Customer", "TempTechnical", "TempSurvey". The excel workbook comes in from multiple locations and, once imported,it is deleted.
I have sorted out the import using VBA. this works fine.
The Access table "Customer" has an autonumber field [cust_ID]which then becomes the main record. My aim is to link the main record with the other two records
I am using DLookup to get the newly created Customer Record.
I have looked at a few scenarios and I keep coming back to creating two permanent tables "Technical" and "Survey", each with identical fields to their 'Temp' counterparts but with an extra [ID] field. I would then populate these [ID] fields with the value from the Dlookup. The final steps would be to copy over the data from the "TempTechnical" and "TempSurvey" tables and then delete the data from the 'Temp' tables ready for the next Import. But I am having a problem working out how to do this. Does anyone have suggestions how to do this? or perhaps a more elegant solution. TIA

'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980

 

[Remou]

What is the DlookUp (code)? Have you some sample data for the three tables?

 

[ajking]

I have had a rethink and I what I have come up with is that all three worksheets are now imported into their equivalent Temp table.
The Main Technical record does not have an autonumber field but the Technical_ID is generated by using Dlast to obtain the last record no. this number is incremented by 1 and then written to each record as:

Private Sub UpDate_Click()
Dim db As DAO.Database
Dim TB As DAO.Recordset
Dim LastRecNo As Integer

LastRecNo = DLast("[Technical_ID]", "tbTechnical")
LastRecNo = LastRecNo + 1

Set db = CurrentDb
'Open tbTechnical Table
Set TB = db.OpenRecordset("tbTechnical", dbOpenDynaset)
'prepare table for new record to be added
TB.AddNew
TB!Technical_ID = LastRecNo
TB.UpDate
TB.Close
'Open tbemail Table
Set TB = db.OpenRecordset("tbEmail", dbOpenDynaset)
'prepare table for new record to be added
TB.AddNew
TB!Email_ID = LastRecNo
TB.UpDate
TB.Close
'Open tbScan Table
Set TB = db.OpenRecordset("tbScan", dbOpenDynaset)
'prepare table for new record to be added
TB.AddNew
TB!Scan_ID = LastRecNo
TB.UpDate
TB.Close

End Sub

What I need to to do now is to copy the data from each temp table and append it to the newly created record. thare are about 20 fields for each table, finally the data is cleared from each temp table, using a macro, ready for the next import. So my question now is how do it append the data to the new record?


'Life is what happens to you while you are busy making other plans' John W. Lennon 1940-1980