REMOVE YOUR ID AND PASSWORD FROM YOUR POST!!
You have to declare the type of an object instead of using variants
this way more error checking can be done, propperties are availeble and
wrong assignments won't happen.
Something you should now:
_If_a_object_has_a_default_propperty_this_proppert y_is_assigned_to_a_variant_not_the_object_
This should resolve your problem
Code:
--------------------
Option Explicit
Sub UpdateT1()
'Update T7 based on T3
On Error GoTo ADO_err
Dim str, str1, str2 As String
Dim i, x As Double
Dim OldStatus
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Sheets("Update").Activate
Application.ScreenUpdating = False
OldStatus = Application.DisplayStatusBar
Application.StatusBar = ActiveSheet.Name & " is running, please wait..."
x = Range("A65536").End(xlUp).Row
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
str = "Provider=SQLOLEDB;User ID=XXX;Password=XXXX;Data Source=SUNSYSTEM"
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
cn.Open str
'str1 = "Update SALFLDG112 SET ANAL_T7=''"
'cn.Execute (str1)
str2 = "select * from SALFLDG112 where ACCNT_CODE like '22003CIT%'"
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open str2, cn
If rs.EOF = False Then
Do Until rs.EOF = True
For i = 2 To x
If Trim(rs("ACCNT_CODE")) = Trim(Cells(i, 1)) _
And Trim(rs("PERIOD")) = Trim(Cells(i, 2)) _
And Trim(rs("JRNAL_NO")) = Trim(Cells(i, 3)) _
And Trim(rs("JRNAL_LINE")) = Trim(Cells(i, 4)) _
And Trim(rs("TREFERENCE")) = Trim(Cells(i, 7)) Then
rs("ANAL_T1") = Trim(Cells(i, 10))
rs.Update
End If
Next i
rs.MoveNext
Loop
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
ADO_exit:
Application.ScreenUpdating = True
Application.StatusBar = False
Application.DisplayStatusBar = OldStatus
Set rs = Nothing
Set cn = Nothing
Exit Sub
ADO_err:
MsgBox Err.Description
Resume ADO_exit
End Sub
--------------------
--
Dnereb
------------------------------------------------------------------------
Dnereb's Profile:
http://www.excelforum.com/member.php...o&userid=26182
View this thread:
http://www.excelforum.com/showthread...hreadid=399711