CopyFromRecordset Returns Blank

[Chris2404]

Hi,

This is an issue that has only appeared this week and I cant figure out why. Below is a snippet of the code that I use, the user is promoters for a week, this has always worked and still does for any number less than 245. I dont know if 246 will work yet as I dont have that data.

Is there anything that would cause an integer with value 245 not to work? The query does still return values when I run it manually with value 245 and the strings for the query are all correct, hence why it works perfectly until you try and run it for week 245.

I appreciate the help on this, its driving me mad!

'  Excel Objects:
  Dim objXL As Excel.Application
  Dim objWBK As Excel.Workbook
  Dim objWS As Excel.Worksheet
  Dim objRNG As Excel.Range
 
  '  DAO objects:
  Dim objDB As DAO.Database
  Dim objQDF As DAO.QueryDef
  Dim objRS As DAO.Recordset
  Set objDB = CurrentDb()
  Set objXL = New Excel.Application
  Set objWBK = objXL.Workbooks.Open(strcXLPath)
 
  'Display Excel Now Copying Is Complete
  objXL.Visible = True
 
  Set objQDF = objDB.QueryDefs(queryWeek)
  With objDB.QueryDefs(queryWeek)
  .Parameters("Enter Week") = 245
  End With
 
  Set objRS = objQDF.OpenRecordset
  Set objWS = objWBK.Worksheets("Sheet1")
  Set objRNG = objWS.Range("B3")
  objRNG.CopyFromRecordset objRS

 

[Chris2404]

Ok just to give a bit more info on this...

I altered the weeks to lower numbers so week 245 became week 12 and it still wont copy over the results of this query (its blank) but its perfectly fine on all weeks before?

There is more data in the query results for this week however its still less that some of the other results that copy over successfully.

 

[Chris2404]

Anyone got any ideas?

 

[Cronk]

Try it on a blank work sheet.

 

[Chris2404]

Tried it on a brand new sheet and tried different cell ranges, same issue. Query still runs perfect manually and on other weeks...?

 

[Chris2404]

The only thing I can think of is that there is a limitation when copying into excel, does anyone know how to get around this?

 

[Chris2404]

Found the issue finally! Within my query one of the fields returned #error due to a division by zero however it never caused issue until now! Once I fixed that it works perfectly again.

 

[pbaldy]

I'd test the recordset for EOF to make sure it has data. QueryWeek is a variable?