Solved: How to edit field values in ADO recordset ?

[sagotianitin]

Hi guys,

Please anyone can help on the below issue.

Issue: I need to update the fields value to "Blank" wherever field in recordset has NULL.

Error: I am getting Automation Error for the below code.

[vba]
Do While Not .EOF
For Each ObjField In .Fields
If IsNull(ObjField.Value) Then
rst.Fields(ObjField.Name).Value = "Blank"
End If
Next ObjField
rst.MoveNext
Loop
[/vba]

Kindly let me know where I am wrong! or more information is needed.

Thanks

[Aflatoon]

Which line actually causes the error? Are you sure the null field is a text field?

Also, why use
[vba]rst.Fields(ObjField.Name).Value = "Blank" [/vba]
rather than
[vba]ObjField.Value = "Blank" [/vba]

[mohanvijay]

you can do it in another way by passing update query by using ADODB.Connection object

[sagotianitin]

Hi Aflatoon,

I tried with below code, still got the same automation error. And error is on the same line.

[VBA]ObjField.Value = "Blank" [/VBA]


Any other suggestion ??

[Aflatoon]

Is that field a text field?

[sagotianitin]

Thanks everyone,this query has been resolved.

[Aussiebear]

How was it resolved?

[sagotianitin]

Hello,

I didn't got the code for that. I changed my logic and used excel formulas. I changed the values to "Blank" wherever it was Spaces\NULL.

Thanks