I'm getting a "Run-time error '3265': Application-defined or object-defined
error" when I run the following code:

Private Sub CommandButton1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim bstartdate
Dim benddate
Dim cstartdate
Dim cenddate
Dim bsd
Dim bed
Dim csd
Dim ced
Dim basesql
Dim curentsql
Dim rowx
Dim wksheet

cn.Open "Data Source=COGNOSETL"
rs.ActiveConnection = cn

bstartdate = Worksheets("Input Form").Range("C4")
benddate = Worksheets("Input Form").Range("C5")
cstartdate = Worksheets("Input Form").Range("C9")
cenddate = Worksheets("Input Form").Range("C10")

bsd = Year(bstartdate) & IIf(Len(Month(bstartdate)) = 1, "0" &
Month(bstartdate), Month(bstartdate)) & IIf(Len(Day(bstartdate)) = 1, "0" &
Day(bstartdate), Day(bstartdate))
bed = Year(benddate) & IIf(Len(Month(benddate)) = 1, "0" & Month(benddate),
Month(benddate)) & IIf(Len(Day(benddate)) = 1, "0" & Day(benddate),
Day(benddate))
csd = Year(cstartdate) & IIf(Len(Month(cstartdate)) = 1, "0" &
Month(cstartdate), Month(cstartdate)) & IIf(Len(Day(cstartdate)) = 1, "0" &
Day(cstartdate), Day(cstartdate))
ced = Year(cenddate) & IIf(Len(Month(cenddate)) = 1, "0" & Month(cenddate),
Month(cenddate)) & IIf(Len(Day(cenddate)) = 1, "0" & Day(cenddate),
Day(cenddate))

basesql = "SELECT C.NEW_ITEM_GROUP, A.MATERIAL_SID, SUM(A.QUANTITY) AS
QUANTITY, SUM(A.EXTENDED_PRICE_AMT) AS EXTENDED_PRICE_AMT,
CAST(ROUND(SUM(A.EXTENDED_PRICE_AMT)/SUM(A.QUANTITY),4) AS NUMERIC(15,4)) AS
PRICE FROM BILLING A LEFT JOIN MATERIAL B ON B.MATERIAL_SID = A.MATERIAL_SID
LEFT JOIN BERRY_DSR_ITEM_GROUP_XREF C ON C.ITEM_GROUP = B.MATERIAL_CAT11_CD
WHERE A.DOCUMENT_TYPE_CD < 'CI' AND TIME_SID BETWEEN " & bsd & " AND " & bed
& " GROUP BY C.NEW_ITEM_GROUP, A.MATERIAL_SID ORDER BY C.NEW_ITEM_GROUP,
A.MATERIAL_SID"
currentsql = "SELECT C.NEW_ITEM_GROUP, A.MATERIAL_SID, SUM(A.QUANTITY) AS
QUANTITY, SUM(A.EXTENDED_PRICE_AMT) AS EXTENDED_PRICE_AMT,
CAST(ROUND(SUM(A.EXTENDED_PRICE_AMT)/SUM(A.QUANTITY),4) AS NUMERIC(15,4)) AS
PRICE FROM BILLING A LEFT JOIN MATERIAL B ON B.MATERIAL_SID = A.MATERIAL_SID
LEFT JOIN BERRY_DSR_ITEM_GROUP_XREF C ON C.ITEM_GROUP = B.MATERIAL_CAT11_CD
WHERE A.DOCUMENT_TYPE_CD < 'CI' AND TIME_SID BETWEEN " & csd & " AND " & ced
& " GROUP BY C.NEW_ITEM_GROUP, A.MATERIAL_SID ORDER BY C.NEW_ITEM_GROUP,
A.MATERIAL_SID"

rs.Source = basesql

rs.Open
rs.MoveFirst
rowx = 2

While Not rs.EOF
Worksheets("Raw Data - Base").Range("A" & rowx) =
rs.Fields([NEW_ITEM_GROUP])
Worksheets("Raw Data - Base").Range("B" & rowx) =
rs.Fields([MATERIAL_SID])
Worksheets("Raw Data - Base").Range("C" & rowx) = rs.Fields([quantity])
Worksheets("Raw Data - Base").Range("D" & rowx) =
rs.Fields([EXTENDED_PRICE_AMT])
Worksheets("Raw Data - Base").Range("E" & rowx) = rs.Fields([PRICE])
Worksheets("Raw Data - Base").Range("F" & rowx) =
rs.Fields([NEW_ITEM_GROUP]) & rs.Fields([MATERIAL_SID])
rowx = rowx + 1
rs.MoveNext
Wend

End Sub

I can't seem to figure out why....any suggestions ??

Thanks !

Hello,

On which line ?
I also set that you do not assign correctly objects

Before :
cn.Open "Data Source=COGNOSETL"
rs.ActiveConnection = cn
You need :
Set con = New ADODB.Connection
Set rs = New ADODB.Recordset
--
Regards

Jean-Yves Tfelt
Europe


"Eric @ BP-EVV" wrote:

I'm getting a "Run-time error '3265': Application-defined or object-defined
error" when I run the following code:

Private Sub CommandButton1_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim bstartdate
Dim benddate
Dim cstartdate
Dim cenddate
Dim bsd
Dim bed
Dim csd
Dim ced
Dim basesql
Dim curentsql
Dim rowx
Dim wksheet

cn.Open "Data Source=COGNOSETL"
rs.ActiveConnection = cn

bstartdate = Worksheets("Input Form").Range("C4")
benddate = Worksheets("Input Form").Range("C5")
cstartdate = Worksheets("Input Form").Range("C9")
cenddate = Worksheets("Input Form").Range("C10")

bsd = Year(bstartdate) & IIf(Len(Month(bstartdate)) = 1, "0" &
Month(bstartdate), Month(bstartdate)) & IIf(Len(Day(bstartdate)) = 1, "0" &
Day(bstartdate), Day(bstartdate))
bed = Year(benddate) & IIf(Len(Month(benddate)) = 1, "0" & Month(benddate),
Month(benddate)) & IIf(Len(Day(benddate)) = 1, "0" & Day(benddate),
Day(benddate))
csd = Year(cstartdate) & IIf(Len(Month(cstartdate)) = 1, "0" &
Month(cstartdate), Month(cstartdate)) & IIf(Len(Day(cstartdate)) = 1, "0" &
Day(cstartdate), Day(cstartdate))
ced = Year(cenddate) & IIf(Len(Month(cenddate)) = 1, "0" & Month(cenddate),
Month(cenddate)) & IIf(Len(Day(cenddate)) = 1, "0" & Day(cenddate),
Day(cenddate))

basesql = "SELECT C.NEW_ITEM_GROUP, A.MATERIAL_SID, SUM(A.QUANTITY) AS
QUANTITY, SUM(A.EXTENDED_PRICE_AMT) AS EXTENDED_PRICE_AMT,
CAST(ROUND(SUM(A.EXTENDED_PRICE_AMT)/SUM(A.QUANTITY),4) AS NUMERIC(15,4)) AS
PRICE FROM BILLING A LEFT JOIN MATERIAL B ON B.MATERIAL_SID = A.MATERIAL_SID
LEFT JOIN BERRY_DSR_ITEM_GROUP_XREF C ON C.ITEM_GROUP = B.MATERIAL_CAT11_CD
WHERE A.DOCUMENT_TYPE_CD < 'CI' AND TIME_SID BETWEEN " & bsd & " AND " & bed
& " GROUP BY C.NEW_ITEM_GROUP, A.MATERIAL_SID ORDER BY C.NEW_ITEM_GROUP,
A.MATERIAL_SID"
currentsql = "SELECT C.NEW_ITEM_GROUP, A.MATERIAL_SID, SUM(A.QUANTITY) AS
QUANTITY, SUM(A.EXTENDED_PRICE_AMT) AS EXTENDED_PRICE_AMT,
CAST(ROUND(SUM(A.EXTENDED_PRICE_AMT)/SUM(A.QUANTITY),4) AS NUMERIC(15,4)) AS
PRICE FROM BILLING A LEFT JOIN MATERIAL B ON B.MATERIAL_SID = A.MATERIAL_SID
LEFT JOIN BERRY_DSR_ITEM_GROUP_XREF C ON C.ITEM_GROUP = B.MATERIAL_CAT11_CD
WHERE A.DOCUMENT_TYPE_CD < 'CI' AND TIME_SID BETWEEN " & csd & " AND " & ced
& " GROUP BY C.NEW_ITEM_GROUP, A.MATERIAL_SID ORDER BY C.NEW_ITEM_GROUP,
A.MATERIAL_SID"

rs.Source = basesql

rs.Open
rs.MoveFirst
rowx = 2

While Not rs.EOF
Worksheets("Raw Data - Base").Range("A" & rowx) =
rs.Fields([NEW_ITEM_GROUP])
Worksheets("Raw Data - Base").Range("B" & rowx) =
rs.Fields([MATERIAL_SID])
Worksheets("Raw Data - Base").Range("C" & rowx) = rs.Fields([quantity])
Worksheets("Raw Data - Base").Range("D" & rowx) =
rs.Fields([EXTENDED_PRICE_AMT])
Worksheets("Raw Data - Base").Range("E" & rowx) = rs.Fields([PRICE])
Worksheets("Raw Data - Base").Range("F" & rowx) =
rs.Fields([NEW_ITEM_GROUP]) & rs.Fields([MATERIAL_SID])
rowx = rowx + 1
rs.MoveNext
Wend

End Sub

I can't seem to figure out why....any suggestions ??

Thanks !