UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Export To Excel Stops Working After Three Exports, Access 2016    
 
   
tobyhanna
post Aug 1 2019, 08:57 AM
Post#1



Posts: 156
Joined: 7-February 06



Hello,

I have a form with many criteria selections which dictate the data displayed in a datasheet subform. To export the datasheet subform information, I have a button with the following code:

CODE
Private Sub cmdExport_Click()
On Error GoTo Proc_Err

    Dim sFileName As String
    Dim sFolder As String
    Dim sQuery As String
    
    'check for data in subform
    If Me.frmSubscriptionFilterSubform.Form.RecordsetClone.RecordCount = 0 Then Exit Sub
    
    sFileName = "SubscriptionFilterList_" & Format(CDate(DateValue(Now())), "yyyy-mm-dd") & ".xlsx"
    sFolder = GetFolderName(FolderEnding(Nz(DLookup("FolderExportFiles", "usys_global_defaults"), CurrentProject.Path)))
    sQuery = Me.frmSubscriptionFilterSubform.Form.RecordSource
    
    'populate tempFilterSubscription with current filter settings
    CurrentDb.Execute "DELETE * FROM tempFilterSubscription", dbFailOnError
    CurrentDb.Execute "INSERT INTO tempFilterSubscription " & sQuery, dbFailOnError
    
    'transfer tempFilterSubscription to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tempFilterSubscription", sFolder & sFileName  ', True
    
    MsgBox "File exported to:" & vbCrLf & sFolder & sFileName, vbOKOnly + vbInformation, "Export Complete"

Proc_Exit:
    Exit Sub      ' or function

Proc_Err:
    PostErrorMsg Err.Number, Err.Description, Nz(Me.Module, ""), Nz(Me.RecordSource, ""), GetWinUser()
    Resume Proc_Exit

End Sub

This runs fine for the first three exports and then the Excel file won't update according to the data in the subform. Any ideas why it stops updating after three exports?

Thanks,
Liesl
Go to the top of the page
 
ipisors12
post Aug 2 2019, 03:23 PM
Post#2



Posts: 9
Joined: 14-March 19



have you stepped through the code debugging line by line to test for the values of all of the various variables and conditions defined in your code? that is where I would start.

also, are you experiencing any errors?

last, can you upload a zipped copy of a sanitized version of dummy data including code? maybe with slight alterations to remove any organization/environment-specific functions like postmessage etc.
Go to the top of the page
 
tobyhanna
post Aug 29 2019, 01:10 PM
Post#3



Posts: 156
Joined: 7-February 06



Thank you for your response, my apologies for the late reply.

I have stepped through the code many times. I remove all records from a temp table and then repopulate the temp table using the RecordSource from a subform that lists records according to selected criteria. I've stepped through this process and printed out the value of the subform's RecordSource and made a working query from the resulting SQL - with all iterations of the criteria. This is why it's strange, no matter the order of criteria I select, it runs three exports and then stops updating the Excel file.

I do not experience any errors.

I will work on the sanitized version asap.

Thanks,
Liesl
Go to the top of the page
 
tobyhanna
post Oct 1 2019, 04:32 PM
Post#4



Posts: 156
Joined: 7-February 06



I was using the same file name for each export. I had a string and date concatenated for the file name so it wouldn't overwrite until the next day. After adding time to the file name, it now creates a new file each time with the correct data.
Go to the top of the page
 
DanielPineault
post Oct 1 2019, 07:18 PM
Post#5


UtterAccess VIP
Posts: 6,900
Joined: 30-June 11



Glad you got it sorted out. We've all been there and done that!

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 08:15 PM