Error during retrieval of ADODBrecordset
 

hi all,

a client is receiving the error:

Error 1004: Application-defined or object-defined error.

when inserting data into a worksheet from an adodb recordset. the
error occurs when trying to retreive the 7th field in the first row of
data (a string field with value "hour"). The previous fields are all
strings or dates.

example of code with additional comment on the line with the problem is
below.

thanks in advance,

L.


....
Dim rs As ADODB.Recordset
Dim f As ADODB.Field
Dim i As Integer
Dim j As Integer

... set query, retrieve query result into rs


' set headings
If Not rs.EOF Then
i = 0
For Each f In rs.Fields
shtAccrual.Range("A12").Offset(0, i).Value = f.Name
i = i + 1
Next
End If

' set values
j = 0
Do Until rs.EOF
i = 0
For Each f In rs.Fields
' error occurs here for j = 0, i = 6, f.value = "hour"
shtAccrual.Range("A13").Offset(currow, i).Value = f.Value
Next f
rs.MoveNext
j = j + 1
Loop
rs.Close
....

Error during retrieval of ADODBrecordset
 

Hi Luke,
Few points/ideas :
Did you test if the value was null ? if ISNuill(f.value)
Tried to format : shtAccrual.Range("A12").Offset(0, i).Value = format
(f.Name,"hh:mm")
Use copyfromRecordset (and thus avoid to loop) :
shtAccrual.Range("A12").copyfromfrecordset rs
ps : don't communicate your correct email addressed if you don't want to get
spam
eg lukeDOTgoodfellowATgmail.com

Regards
JY

wrote in message
ups.com...
hi all,

a client is receiving the error:

Error 1004: Application-defined or object-defined error.

when inserting data into a worksheet from an adodb recordset. the
error occurs when trying to retreive the 7th field in the first row of
data (a string field with value "hour"). The previous fields are all
strings or dates.

example of code with additional comment on the line with the problem is
below.

thanks in advance,

L.


...
Dim rs As ADODB.Recordset
Dim f As ADODB.Field
Dim i As Integer
Dim j As Integer

... set query, retrieve query result into rs


' set headings
If Not rs.EOF Then
i = 0
For Each f In rs.Fields
shtAccrual.Range("A12").Offset(0, i).Value = f.Name
i = i + 1
Next
End If

' set values
j = 0
Do Until rs.EOF
i = 0
For Each f In rs.Fields
' error occurs here for j = 0, i = 6, f.value = "hour"
shtAccrual.Range("A13").Offset(currow, i).Value = f.Value
Next f
rs.MoveNext
j = j + 1
Loop
rs.Close
...

Error during retrieval of ADODBrecordset
 

Hi
You don't increment i in the second loops, but that shouldn't give an
error.
You could try rs.movefirst above the second loop(s) to move the cursor
to the first record?
regards
Paul

wrote:
hi all,

a client is receiving the error:

Error 1004: Application-defined or object-defined error.

when inserting data into a worksheet from an adodb recordset. the
error occurs when trying to retreive the 7th field in the first row of
data (a string field with value "hour"). The previous fields are all
strings or dates.

example of code with additional comment on the line with the problem is
below.

thanks in advance,

L.


...
Dim rs As ADODB.Recordset
Dim f As ADODB.Field
Dim i As Integer
Dim j As Integer

... set query, retrieve query result into rs


' set headings
If Not rs.EOF Then
i = 0
For Each f In rs.Fields
shtAccrual.Range("A12").Offset(0, i).Value = f.Name
i = i + 1
Next
End If

' set values
j = 0
Do Until rs.EOF
i = 0
For Each f In rs.Fields
' error occurs here for j = 0, i = 6, f.value = "hour"
shtAccrual.Range("A13").Offset(currow, i).Value = f.Value
Next f
rs.MoveNext
j = j + 1
Loop
rs.Close
...