Hi Guy's WGM and Micron kindly helped me with this, it's frustrating because i can't where i am going wrong here as i have moved the code to a combo instead of a button
I have put 3 Records from row 6 on the excel file xlSht1, it is adding the 3 correctly plus another 265 blank records, so tried rsDel to delete records where the name is null (265 Blank Records)
Just tried adding 6 records from row6 and still adding 265 blank records!!!
Code:
Set xl = CreateObject("Excel.Application") Set xlWrkBk = GetObject(srcPath & srcFile)
Set xlsht = xlWrkBk.Worksheets(1)
Set xlsht2 = xlWrkBk.Worksheets(2)
Set xlsht3 = xlWrkBk.Worksheets(3)
myCust = Forms!frmMainMenu!frmIndex1!cboCustomer
cAdd1 = DLookup("Add1", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
cAdd2 = DLookup("Add2", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
cTown = DLookup("Town", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
cPC = DLookup("Postcode", "tblCustomers", "[Name] Like ""*" & myCust & "*""")
LastRow = xlsht.UsedRange.Rows.Count
myRow = "6"
For i = myRow To LastRow
c1 = xlsht.Cells(i, 2).Value ' Del TO
c2 = xlsht.Cells(i, 3).Value 'Town
c3 = xlsht.Cells(i, 4).Value 'PostCode
c4 = xlsht.Cells(i, 5).Value 'ItemNo
c5 = xlsht.Cells(i, 6).Value 'SL
myDealer = c1
myPC = c3
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblEdit WHERE 1 = 0") 'you don't use these values so load an empty recordset on the table
With rs
.AddNew
!Customer = myCust
!CustAdd1 = cAdd1
!CustAdd2 = cAdd2
!CustTown = cTown
!CustPostCode = cPC
!ShipmentDate = Null
!PONumber = "PO-"
!SONumber = c5
!itemType = "Item"
!ItemNo = c4
!TotalBox = "0"
!TotalRail = "0"
!DelTo = c1
!Town = DLookup("Town", "tblDealers", "[PostCode] = '" & c3 & "'")
!Add1 = DLookup("Add1", "tblDealers", "[PostCode] = '" & c3 & "'")
!Add2 = DLookup("Add2", "tblDealers", "[PostCode] = '" & c3 & "'")
!PostCode = myPC
!Added = "New"
!Status = "Planning"
!DueDay = ""
!Source = "Acc"
!Pasted = True
.Update
End With
Next i
rs.Close
Set rs = Nothing
Set rsDel = CurrentDb.OpenRecordset("Select * From tblEdit WHERE DelTo Is Null")
With rdels
Do Until rsDel.EOF
rsDel.MoveFirst
rsDel.Delete
rsDel.MoveNext
Loop
End With
rsDel.Close
Set rsDel = Nothing
Me.frmIndex1DS.Visible = False
Me.frmIndex1DSPaste.Visible = True