+ Reply to Thread
Results 1 to 3 of 3

run time error '3265': item cannot be found in the collection corresponding to the reques

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-01-2014
    Location
    Mysore
    MS-Off Ver
    Excel 2007
    Posts
    379

    run time error '3265': item cannot be found in the collection corresponding to the reques

    here i am trying displaying actual columnname(header) please help me out

    1st columname is wds_id but second colmn name displaying as Fields(actual column name is Company Name)..etc


       Dim cnt As ADODB.Connection
        Dim rst1 As ADODB.Recordset, rst2 As ADODB.Recordset
        Dim stDB As String, stSQL1 As String, stSQL2 As String
        Dim strConn As String
        Dim wbBook As Workbook
        Dim Sheet1 As Worksheet
        Dim i
        Dim lnField As Long, lnCount As Long
        Dim dataStr As String
         'Instantiate the ADO-objects.
        Set cnt = New ADODB.Connection
        Set rst1 = New ADODB.Recordset
        Set rst2 = New ADODB.Recordset
    
        Set wbBook = ThisWorkbook
        Set Sheet1 = wbBook.Worksheets(1)
    
         'Path to the database.
        stDB = "mysql32"
    
         'Create the connectionstring.
        strConn = "Driver=MySQL ODBC 5.2 Unicode Driver;" _
        & "Data Source=" & stDB & ";"
    
         'The 1st raw SQL-statement to be executed.
         ' stSQL1 = "SELECT * FROM tblbatch_headers where idBatch " & batchID & "order by col_seq asc"
    
         'The 2nd raw SQL-statement to be executed.
         stSQL2 = "SELECT * FROM  " & InputBox("Enter the Batch Name") & "  where FQR_User_Code='" & Application.username & _
             "' and line_status in('QP') order by line_status" '
         'Clear the worksheet.
         Sheet1.Range("A1:FA1").CurrentRegion.Clear
    
    
    
        With cnt
            .Open (strConn)   'Open the connection.
            .CursorLocation = adUseClient 'Necessary to disconnect the recordset.
        End With
    
         With rst1
    '       .Open stSQL1, cnt    'Create the recordset.
           Set .ActiveConnection = Nothing 'Disconnect the recordset.
         End With
    
        With rst2
            .Open stSQL2, cnt 'Create the recordset.
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
        End With
    
        With Sheet1
    '        .Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset.
         For i = 1 To rst2.Fields.count
                .Cells(2, i).Value = rst2.Fields(i - 1).Name
             Next i
            .Cells(3, 1).CopyFromRecordset rst2 'Copy the 2nd recordset.
       End With
       
       rst2.MoveFirst
          i = 0
       Do While rst2.EOF = False
           Dim rng As Range
              i = i + 1
                ActiveSheet.Cells(1, i) = rst2("Comments")
                ActiveSheet.Cells(2, i) = rst2("ActualColName")
                ActiveSheet.Cells(2, i).ID = rst2("tblColName")
                   rst2.MoveNext
               Loop
                    ActiveSheet.Cells(2, i + 1).ID = prdTblName
             rst2.Close
          '  dbconn.Close
    
         'Release objects from the memory.
    '    rst1.Close
       '  Set rst1 = Nothing
        rst2.Close
        Set rst2 = Nothing
        cnt.Close
        Set cnt = Nothing

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: run time error '3265': item cannot be found in the collection corresponding to the re

    the error means your field names don't match the recordset
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646
    Why are you looping through rst2?

    You've just used CopyFromRecordset to put the data from rst2 onto a worksheet.
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Run-time error 3265, but fields do exist!
    By jik_ff in forum Access Tables & Databases
    Replies: 2
    Last Post: 01-14-2014, 12:23 AM
  2. [SOLVED] Run Time Error 3265 - Item not found in this collection
    By Sc0tt1e in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-23-2013, 07:19 AM
  3. Replies: 1
    Last Post: 11-17-2012, 11:10 PM
  4. runtime error 3265 error in a VBA Query to pull info from ERP Database
    By NBVC in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-12-2010, 03:59 PM
  5. [SOLVED] Run time error 13 with new Collection
    By Tomkat743 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2006, 07:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1