CopyFromRecordset Error

[mkaeser]

Hello All,

I have a bit of code that generates the following error:
Run-time error '-2147417856 (80010100)': Method 'CopyFromRecordset' of object 'Range' failed. Here is a piece of the code:

With rsEMP
.MoveFirst
If !EmpID = 1 Then
.MoveNext
End If
Do While Not rsEMP.EOF
If ![Active?] = -1 Then
If DCount("*", "qryTracking_RunReport", "[TaskDate] BETWEEN #" & Me.txtWeek & "# AND #" & Me.txtWeek + 4 & "#" & " AND EmpID=" & !EmpID) > 0 Then
Tech = !Initials
techID = !EmpID

strSQL = "SELECT *"
strSQL = strSQL & "FROM [qryTracking_RunReport] "
strSQL = strSQL & "WHERE [TaskDate] BETWEEN #" & Me.txtWeek & "# AND #" & Me.txtWeek + 4 & "#" & " AND [EmpID]=" & !EmpID & " ORDER BY qryTracking_RunReport.ID;"
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL1 = "SELECT *"
strSQL1 = strSQL1 & "FROM [qryTracking_Tech] "
strSQL1 = strSQL1 & "WHERE [AnalysisEndDate] BETWEEN #" & Me.txtWeek & "# AND #" & Me.txtWeek + 4 & "#" & "AND [AnalyzedBy]=" & techID & ";"
Set rs1 = CurrentDb.OpenRecordset(strSQL1)


Set oBook = oapp.Workbooks.Open(Path)
Set osheet = oBook.Worksheets("TechRef")
osheet.Activate
'*****COLUMNS STOP AT "H" FOR TECH*****
'Add the field names in row 1
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
osheet.Cells(1, i).Value = rs.Fields(i - 1).name
Next
'Add the data starting at cell A2
rs.MoveFirst
osheet.Range("A2").CopyFromRecordset rs ***ERROR**
rs.Close

Before this code starts, rsEMP is pulled from a table that lists current employees. The code will cycle through the list and if there are records associated with that employee, it will create a new workbook and copy those records in. This works for the first 2 employees and the 4th, errors on the 3 and 5. There are not character violations, or null values for these error recordsets. I also use this code to pull data for separate employees, and it works fine. Additionally, if i step through the code, there is no errors. Any thoughts?

 

[Uncle Gizmo]

As it works for one or two records, but then breaks, it would indicate a problem with the records.

a) Try importing EVERYTHING in to a NEW database.

b) I suggest you create a separate sample DB, with some sample data in that you can hack about to see if you can duplicate the problem in your sample DB.