Page 1 of 2 12 LastLast
Results 1 to 15 of 18

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
    244

    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:	44 
Size:	117.1 KB 
ID:	36136

    Thank you for your input

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,519
    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
    8,140
    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
    244
    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
    8,140
    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.)

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

    Hi there,

    Sorry for the long delay. Got pulled onto a different project for the last little while.

    I have a query written that returns all of the records from the database. On the form there are 2 unbound text boxes where the user can enter filter criteria; [Forms]![USED_Form_Main]![txtCarrier], [Forms]![USED_Form_Main]![txtMCode], [Forms]![USED_Form_Main]![txtCode].

    I have tried adding these to the fields in the query as ORs but I get nothing returned. I attached an imageClick image for larger version. 

Name:	ORS.PNG 
Views:	25 
Size:	7.2 KB 
ID:	37309.

    I am unsure how to get the Where Clause to be created from the unbound text fields.

    Any insight would be awesome

  7. #7
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    244
    This is the sql I have running from the export button. I am getting a runtime 3131 error Syntax error in FROM clause and I cant figure it out. One weird thing I noticed is when I hover over strWhere is shows strWhere = WHERE 1=1 and [SUB_LOC_CD] = 'C001'. The C001 makes sense because that is what I put in one of the filter search text boxes....the 1=1 I am not sure about.

    Code:
    Private Sub export_used_Click()    Dim strWhere As String
        Dim strFile As String
        Const strcStub = "SELECT [USED-AECS_BASE_SO_NO].MAINFR_SER_NO as SERNO, [USED-ALL_ACCESSORIES].PROD_CD1 as PROD, [USED-PROD_UNION].CUST_NAME as CUST," _
        & "[USED-AECS_BASE_SO_NO].SUB_LOC_CD as CARRIER, [USED-AECS_BASE_SO_NO].MKT_CD as MKTCODE " _
        & "FROM ([USED-AECS_BASE_SO_NO] LEFT JOIN [USED-ALL_ACCESSORIES] ON [USED-AECS_BASE_SO_NO].SO_NO = [USED-ALL_ACCESSORIES].SO_NO)" _
        & "LEFT JOIN [USED-PROD_UNION] ON [USED-AECS_BASE_SO_NO].MAINFR_SER_NO = [USED-PROD_UNION].PROD_SER_NO)"
        Const strcExportQuery = "qry_used_export"
    
    
       If Me.FilterOn Then
            strWhere = "WHERE " & Me.Filter & vbCrLf
        End If
        CurrentDb.QueryDefs(strcExportQuery).SQL = strcStub & strWhere
    
    
        strFile = "C:\Executive_Summary\MyExport.xlsx"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strcExportQuery, strFile
    End Sub

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,140
    Yes, it has been a while.......

    OK,
    Quote Originally Posted by mindbender View Post
    This is the sql I have running from the export button. I am getting a runtime 3131 error Syntax error in FROM clause and I cant figure it out.
    Looking at the "FROM" clause in the query in the code in the previous post, there is 1 opening parenthesis and 2 closing parenthesizes.
    Code:
        & "FROM ([USED-AECS_BASE_SO_NO] LEFT JOIN [USED-ALL_ACCESSORIES] ON [USED-AECS_BASE_SO_NO].SO_NO = [USED-ALL_ACCESSORIES].SO_NO)" _
        & "LEFT JOIN [USED-PROD_UNION] ON [USED-AECS_BASE_SO_NO].MAINFR_SER_NO = [USED-PROD_UNION].PROD_SER_NO)" '<< delete this one
    I would delete the last closing parenthesis (and add a semi-colon), then test the SQL to ensure it works.



    Quote Originally Posted by mindbender View Post
    One weird thing I noticed is when I hover over strWhere is shows strWhere = WHERE 1=1 and [SUB_LOC_CD] = 'C001'. The C001 makes sense because that is what I put in one of the filter search text boxes....the 1=1 I am not sure about.
    The "1=1" is from the filter button (code in Post #4).



    Maybe you could try this code.....
    Code:
    Private Sub export_used_Click()
        Dim strWhere As String
        Dim sSQL As String
        Dim strFile As String
    
        sSQL = "SELECT [USED-AECS_BASE_SO_NO].MAINFR_SER_NO as SERNO, [USED-ALL_ACCESSORIES].PROD_CD1 as PROD, [USED-PROD_UNION].CUST_NAME as CUST,"
        sSQL = sSQL & " [USED-AECS_BASE_SO_NO].SUB_LOC_CD as CARRIER, [USED-AECS_BASE_SO_NO].MKT_CD as MKTCODE"
        sSQL = sSQL & " FROM ([USED-AECS_BASE_SO_NO] LEFT JOIN [USED-ALL_ACCESSORIES] ON [USED-AECS_BASE_SO_NO].SO_NO = [USED-ALL_ACCESSORIES].SO_NO)"
        sSQL = sSQL & " LEFT JOIN [USED-PROD_UNION] ON [USED-AECS_BASE_SO_NO].MAINFR_SER_NO = [USED-PROD_UNION].PROD_SER_NO"
        Debug.Print sSQL
    
        'create the filter (where clause)
        strWhere = " 1=1"
        If Not IsNull(Me.txtCode) Then
            strWhere = strWhere & " and Left([MAINFR_SER_NO],3)='" & Me.txtCode & "'"
        End If
        If Not IsNull(Me.txtCarrier) Then
            strWhere = strWhere & " and [SUB_LOC_CD]='" & Me.txtCarrier & "'"
        End If
        If Not IsNull(Me.txtMCode) Then
            strWhere = strWhere & " and [MKT_CD] Like '*" & Me.txtMCode & "*'"
        End If
        Debug.Print strWhere
    
        sSQL = sSQL & strWhere
        '    Debug.Print sSQL
        strFile = "C:\Executive_Summary\MyExport.xlsx"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sSQL, strFile, True  '<<-- (TRUE adds column names)
    
    End Sub
    Look at the immediate window to see if the two lines are formed correctly. Then create a new query, switch to SQL view and paste in the two lines and execute the query.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    244
    Thank you for seeing that paren...I looked at that for an hour and missed it everytime.

    So the export now works on 1 of the 3 text boxes. txtCarrier WORKS PERFECTLY!

    txtCode returns the following error when populated - Runtime error 3079 - The specified field [MAINFR_SER_NO] could refer to more than one table listed in the FROM clause of your SQL statement
    txtMCode returns the same thing - Runtime error 3079 - The specified field [MKT_CD] could refer to more than one table listed in the FROM clause of your SQL statement


    When I tried to run the new code you added I got a weird message about the table name not being right and the immediate window showed the same query 3 times but it did capture the data input into the txtCarrier Field correctly. I attached a screenshot of it

    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	34.2 KB 
ID:	37318

  10. #10
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    244
    I tried modifying the filter button code as follows, by adding the table as well as the field but although the export button does execute, the export spreadsheet is blank for both txtCode and txtMCode. Still only txtCarrier works properly.

    Code:
    sWhere = "1=1"If Not IsNull(Me.txtCode) Then sWhere = sWhere & " and Left([USED-AECS_BASE_SO_NO].[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 [USED-AECS_BASE_SO_NO].[MKT_CD] Like '*" & Me.txtMCode & "*'"

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,140
    Maybe you could copy the SQL string from the Immediate window, then paste into a reply - the string is truncated in the immediate window image.
    Would you post an image of the relationship window or post your dB? Change any sensitive data....

    Have you single stepped through the code to see where the error occurs?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    244
    Quote Originally Posted by ssanfu View Post
    Maybe you could copy the SQL string from the Immediate window, then paste into a reply - the string is truncated in the immediate window image.
    Would you post an image of the relationship window or post your dB? Change any sensitive data....

    Have you single stepped through the code to see where the error occurs?

    I attached a copy of the database. I removed everything not directly related to this process. The 3 filter fields are at the top and are executed by clicking the filter button. Then the Export button is to the right and shoudl just export what is currently in the filter. The user can enter 1, 2 or all 3 of the fields for filtering in any order.New_EA_TOOLS - Copy.zip

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,140
    It is looking like you have a data problem. (and possible table structure problems)

    I am getting an error because it looks like you deleted customer fields, instead of changing or deleting the customer data. No customer number field so there is an error. The spreadsheet was created and the Prod and Cust fields were blank. I know why the cust field is blank, so why is the PROD field blank?

    I used both "A2M" and "9HB" - both no data for the PROD field.

    I made two queries - 1 for "USED-AECS_BASE_SO_NO" with fields "MAINFR_SER_NO" and "SO_NO".
    The other for "USED-ALL_ACCESSORIES" with fields "SO_NO" and "PROD_CD1" (PROD).

    This is the export query
    Click image for larger version. 

Name:	Query1.png 
Views:	15 
Size:	100.4 KB 
ID:	37433


    Looking at the data in the two tables
    Click image for larger version. 

Name:	Query2.png 
Views:	14 
Size:	145.5 KB 
ID:	37434

    Since there are no matching SO_NO values, the PROD column in the spreadsheet is blank.
    If I change any SO_NO number in "USED-ALL_ACCESSORIES" to match a value in "USED-AECS_BASE_SO_NO", then a value is returned for PROD.

    Does that help??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    244
    Quote Originally Posted by ssanfu View Post
    It is looking like you have a data problem. (and possible table structure problems)

    I am getting an error because it looks like you deleted customer fields, instead of changing or deleting the customer data. No customer number field so there is an error. The spreadsheet was created and the Prod and Cust fields were blank. I know why the cust field is blank, so why is the PROD field blank?

    I used both "A2M" and "9HB" - both no data for the PROD field.

    I made two queries - 1 for "USED-AECS_BASE_SO_NO" with fields "MAINFR_SER_NO" and "SO_NO".
    The other for "USED-ALL_ACCESSORIES" with fields "SO_NO" and "PROD_CD1" (PROD).

    This is the export query
    Click image for larger version. 

Name:	Query1.png 
Views:	15 
Size:	100.4 KB 
ID:	37433


    Looking at the data in the two tables
    Click image for larger version. 

Name:	Query2.png 
Views:	14 
Size:	145.5 KB 
ID:	37434

    Since there are no matching SO_NO values, the PROD column in the spreadsheet is blank.
    If I change any SO_NO number in "USED-ALL_ACCESSORIES" to match a value in "USED-AECS_BASE_SO_NO", then a value is returned for PROD.

    Does that help??
    I added a database that should work without error. I changed some of the data but it shoudnt affect anything. I will take a look at the above query as well.

    Thank you
    Attached Files Attached Files

  15. #15
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    244
    Quote Originally Posted by mindbender View Post
    I added a database that should work without error. I changed some of the data but it shoudnt affect anything. I will take a look at the above query as well.

    Thank you
    Another thing that may be confusing is the field names.....all three filter fields are on the main form; Prod Code is the first three digits of the Serial number, Carrier # is the Carrier field and Market Code is the Market Code Field

    Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	43.6 KB 
ID:	37440

Page 1 of 2 12 LastLast
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