Copy data from one table to another

[sdel_nevo]

I Guys I'm having problems trying to get this working

what I as trying to do is this



I have a table that holds imported customer details called tblExcelImport I am trying to copy this data into the table tblCustomers when a button is clicked

the code I am trying to get working is this

DoCmd.SetWarnings False

   'Delete existing data in table
   DoCmd.RunSQL "DELETE * FROM tblExcelImport"
   DoCmd.TransferSpreadsheet acImport, 8, "tblExcelImport", selectFile(), True 'select file is the file dialog screen
   
   'now we append these importred customers into the correct table - tblCustomers
   
   Dim db As DAO.Database
   Dim rsTbl1 As DAO.Recordset, rsTbl2 As DAO.Recordset
   Dim strFullName As String, strStreet As String, strTown As String
   Dim strCounty As String, strPostCode As String, strPhoneNumber As String, strMobile As String
   Dim strEMailAddress As String, strWebSite As String
   Dim fld As Field
  
   Set db = CurrentDb()
   Set rsTbl1 = db.OpenRecordset("tblExcelImport", dbOpenDynaset)
   Set rsTbl2 = db.OpenRecordset("tblCustomers", dbOpenDynaset)
   rsTbl1.MoveFirst
   Do Until rsTbl1.EOF
      For Each fld In rsTbl1.Fields
      
         strFullName = fld.Value
         strStreet = fld.Value
         strTown = fld.Value
         strCounty = fld.Value
         strPostCode = fld.Value
         strPhoneNumber = fld.Value
         strMobile = fld.Value
         strEMailAddress = fld.Value
         strWebSite = fld.Value
         
         
         With rsTbl2
           
            !FullName = strFullName
            !Street = strStreet
            !Town = strTown
            !County = strCounty
            !PostCode = strPostCode
            !PhoneNumber = strPhoneNumber
            !Mobile = strMobile
            !EMailAddress = strEMailAddress
            !WebSite = strWebSite
         End With
      Next fld
      rsTbl1.MoveNext
   Loop

   rsTbl1.Close
   rsTbl2.Close
   Set rsTbl1 = Nothing
   Set rsTbl2 = Nothing
   Set db = Nothing
   
ImportCustomersButton_Click_Error:
   MsgBox "Error Has Been Found " & Err.Number & " (" & Err.Description & ") In Procedure (ImportCustomersButton_Click) Of (Sub Form_frmCompanyDetails)"

when this runs I get this error

"Error Has been Found 3020 (Update or CancelUpdate without addnew or edit)

so I amended my vode to this

With rsTbl2
           .Edit
            !FullName = strFullName
            !Street = strStreet
            !Town = strTown
            !County = strCounty
            !PostCode = strPostCode
            !PhoneNumber = strPhoneNumber
            !Mobile = strMobile
            !EMailAddress = strEMailAddress
            !WebSite = strWebSite
            .Update
         End With
      Next fld
      rsTbl1.MoveNext
   Loop

now when this runs I get "Error Has Been Found 94(invalid use of null)

how can I change the code so that data held in tblExcelImport is copied into tblCustomers?


Many thanks for your help

Steve

[ranman256]

This is a horrible way to copy data. You don't copy 1 record at a time,
you run an append query to do it in bulk.
No code needed.

[sdel_nevo]

Hi Ranman256

OMG, how the hell did I miss that lol,
many thanks

Steve