Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question VBA Error 3021 - No Current Record (CopyFromRecordset)

    Hi All,



    A bit confounded on this one. I have a module setup which is intended to output a query recordset onto an Excel Workbook.
    The query that I'm trying to pass into it is defined in a separate module using DAO.QueryDef. The SQL string and ODBC Connect String is also defined here.
    The query itself looks fine, as I'm able to validate it after it creates the QueryDef object for it.
    However, once I pass this onto the "Export To Excel Worksheet" module, I keep receiving a "No Current Record" error but I cannot seem to figure out why, as it still copies the data from the recordset and onto the Excel Worksheet as expected.
    For what it's worth - if I tweak the code to have it pass in a pre-existing query from the database (rather than a QueryDef being defined through a separate module), I don't receive that error message.
    Has anyone run into anything similar?

    Code:
    strFileName = "MyFile.xlsx"
    strPath = "c:\users\me\desktop\"
    strSQL = "SELECT * FROM MYTABLE"
    strConnect = "ODBC;DSN=MyDB;UID=abc;PWD=123;DBQ=abc;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"
    
    Set db = CurrentDb
    Set qdef = db.CreateQueryDef(strQuery)
    
    
    With qdef
    
    
     .Connect = strConnect
     .SQL = strSQL
     .ReturnsRecords = True
    
    
    End With
    
    
    Set rs = db.OpenRecordset(strQuery)
    
    
    If Not rs.EOF Then
    
    
      Call Output_Query_To_Excel_Workbook(strQuery, "Pages", strFileName, strPath)
    
    
    End If
    This passes the query to the below function. When it gets to the bolded part, I receive the runtime error 3021 "No Current Record".. yet, it *does* successfully copy/paste the records from the query onto the Excel Worksheet. Thus, I'm a bit confused as to why I keep receiving the "No Current Record" error message.

    Code:
    Public Function Output_Query_To_Excel_Workbook(strQuery As String, strSheetName As String, strFileName As String, strPath As String)
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strQuery)
    
    
    Set XLApp = CreateObject("Excel.Application")
    Set XLBook = XLApp.Workbooks.Add  
    
    
    XLApp.Visible = True
    XLBook.Worksheets("Sheet1").Name = strSheetName
       
    Set XLSheet = XLBook.Worksheets(strSheetName)
       
    XLSheet.Activate
    XLSheet.Range("A1").Select
    
    
     For Each fld In rs.Fields  'Populate field headers on worksheet
            
       XLApp.ActiveCell = fld.Name
       XLApp.ActiveCell.Offset(0, 1).Select
                
    Next
        
     XLSheet.Range("A2").CopyFromRecordset rs   
     XLBook.SaveAs filename:=strPath & strFileName, FileFormat:=xlOpenXMLWorkbook
        
     Set XLBook = Nothing      
     Set XLSheet = Nothing
    
    
     rs.close
     Set rs = Nothing

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've never used this method but you have no replies yet so here goes: your code is either missing information that will raise questions, or you simply don't show it. Why it works but raises an error, I don't know, but wonder if it's because you don't .Open a recordset in the function. Then there is us not knowing if this is ADO or DAO recordset. I suspect the former, in which case .Open could apply; DAO probably not.

    But there are other things as well, like why create a qdf object, give it a sql property, then pass the sql string around when you have already defined it in the qdf? Why not just either pass the sql and open/set a recordset using it, or pass the qdf object. I'm guessing the sql property is more complex and dynamic than what you show, so that is the reason for not using an actual query. I think I'd just be creating (really, altering) the sql property, then opening a recordset on it. However, if the connection is to your own db, then I don't get the use of ADO and connecting to a source that I already have open, unless there is a method something that you don't have available in DAO. After all when you want to 'export' the data, you're using automation, which I would also, which means there's no connection string required there either. I think this
    if I tweak the code to have it pass in a pre-existing query from the database
    supports what I'm saying about passing the query that was just created/edited rather than creating it and passing around the sql, which by the way, is a string and not a query object.
    Last edited by Micron; 04-10-2020 at 10:48 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you try to add qdef.Close after the End With in the first sub and maybe do a check in the second function to see if the recordset is not empty (If Not rs.EOF Then).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ugh - No Current Record 3021
    By AccessPower in forum Programming
    Replies: 4
    Last Post: 12-08-2016, 03:25 PM
  2. Runime Error '3021': - No current record
    By Mohanss82 in forum Programming
    Replies: 2
    Last Post: 07-04-2016, 02:34 AM
  3. sql error . runtime 3021 - no current record
    By princess12 in forum Access
    Replies: 3
    Last Post: 04-10-2015, 09:26 AM
  4. Error 3021 no current record
    By bbrazeau in forum Programming
    Replies: 10
    Last Post: 12-13-2012, 04:22 PM
  5. Replies: 8
    Last Post: 05-16-2011, 06:01 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums