Results 1 to 5 of 5

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
    234

    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:	17 
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
    7,149
    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,936
    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.)

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    234
    Quote Originally Posted by ssanfu View Post
    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)

    Sorry have been working on this with no luck. I have the query built that returns the data from each of the forms but I am not sure how to limit it to export just the filtered records the user has limited the data to.

    The filter button on the form uses the following
    Code:
    Private Sub Command36_Click()Dim sWhere As String
    
    
    sWhere = "1=1"
    If Not IsNull(Me.txtCode) Then sWhere = sWhere & " and Left([MAINFR_SER_NO],3)='" & Me.txtCode & "'"
    If Not IsNull(Me.txtCarrier) Then sWhere = sWhere & " and [SUB_LOC_CD]='" & Me.txtCarrier & "'"
    If Not IsNull(Me.txtMCode) Then sWhere = sWhere & " and [MKT_CD] Like '*" & Me.txtMCode & "*'"
    
    
    If sWhere = "1=1" Then
    Me.FilterOn = False
    Else
    Me.Filter = sWhere
    Me.FilterOn = True
    End If
    End Sub
    But I am unsure how to get that filter (if I even should be using this one) into the export results.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,936
    Quote Originally Posted by mindbender View Post
    Sorry have been working on this with no luck. I have the query built that returns the data from each of the forms but I am not sure how to limit it to export just the filtered records the user has limited the data to.
    One method would be to use VBA to modify a saved query. The code would add the WHERE clause to the query you have developed, then export the saved query.
    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