Run-time error 3265

E

Eric @ BP-EVV

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 !
 
J

Jean-Yves

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 said:
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 !
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproject Run-time Error 13 5
Run Time Error '1004' 1
error correction on vba macro 1
Run Time Error 1004 in Loop 0
Finding Duplicate Entries - Saving Worksheet if none found 3
Run Time Error 13: Type Mismatch 1
run time error 1004 only on web run 4
Run Time Error 1004 2

Top