Results 1 to 3 of 3

Export Filtered Form with associated Subform results to excel

  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    225

    Export Filtered Form with associated Subform results to excel

    I have a form with multiple subforms and search section using unbound text boxes. The user can select 1 or multiple criteria in the search section to filter the results. The results are filtered for the Main Form, and the 3 subforms. Is there a way that I can use a button to export the filtered data from the main form and the 3 subforms together?

    I have attached a photo of the form. The unbound search is the top blue section, the main form data is at the far left and the three subforms are the top middle and top right sections as well as the bottom section.



    Click image for larger version. 

Name:	Capture.JPG 
Views:	9 
Size:	117.1 KB 
ID:	36136

    Thank you for your input

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,948
    youd need to build a query (queries), that use the filter
    qsMainFormFltr

    the subform queries are already in use by the form
    qsSubFrm1
    qsSubFrm2


    vFile = "c:\folder\myFile.xlsx"
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,"qsMainFormFltr", vFile, true,"MasterData"
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,"qsSubFrm1", vFile, true,"SubFrm1"
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,"qsSubFrm1", vFile, true,"SubFrm2"


    or build 1 super query that pulls all master form and subform data together in 1 query, then export that.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,793
    Quote Originally Posted by mindbender View Post
    Is there a way that I can use a button to export the filtered data from the main form and the 3 subforms together?
    What format are you exporting the data into? Excel?

    First, create a new query. Add the (4?) tables involved with the main form and the sub forms. Drag the required fields down to the design grid.
    Execute the query to ensure the query returns data.
    Switch to SQL view and copy the SQL, pasting it into a text variable in the export procedure.
    The last part is to create code to create the WHERE clause, checking the controls in the header of the main form - only add if there is an entry.

    Lets say the SQL variable is sSQL.
    Then you would use
    Code:
    DoCmd.TransferSpreadsheet acExport , acSpreadsheetTypeExcel12, sSQL, xlFileName, True
    (where "xlFileName" is the full path and filename of the excel file)
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Export Filtered Form to Excel
    By wizzz_wizzz in forum Import/Export Data
    Replies: 2
    Last Post: 11-24-2016, 06:21 PM
  2. Can't export data in a filtered subform only
    By mayestom in forum Macros
    Replies: 30
    Last Post: 08-11-2014, 08:23 AM
  3. Exporting filtered results to Excel
    By doublec9 in forum Import/Export Data
    Replies: 2
    Last Post: 06-04-2013, 08:05 PM
  4. Export Filtered Query to Excel Using Combo Box on Form
    By besuchanko in forum Import/Export Data
    Replies: 0
    Last Post: 02-07-2013, 09:10 PM
  5. Export Filtered Results to Excel Tabs
    By opod in forum Programming
    Replies: 1
    Last Post: 08-03-2011, 12:33 PM

Posting Permissions

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