Export Data from data query in a subform

C

chrisjames25

Registered User.
Local time
Today, 16:43
Joined
Dec 1, 2014
Messages
404
Hi

I have a mainform and in the header of that form their is a command button to export data to excel. Unfortunately the button does nothing as i have no clue how to execute what i am trying to achieve.

WHat i have in the form is the header of the main form, then a subform within the detail of the form. THe subform is a continuous form made to look like a posh datasheet which is able to be filtered and sorted via drop downs or a search box.

Ideally i want to export the data that has been filtered to excel rather than all the data that i have filtered out.

I have no clue where to start on this one so any help would be great.
 

Attachments

  • Image of Form.jpg
    Image of Form.jpg
    95.4 KB · Views: 204
use the query in the subform to export:

vFile = "c:\folder\myFile.xlsx"
vQry = me.subform.sourceobject

docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetName
 
To get the filter from the form you will likely have to do something like.

Code: 
Private Sub cmdExport_Click()
  Dim strSql As String
  Dim TempQdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  
  strSql = "Select * from QualQ1"
  If Me.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltered", strSql)
  DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name
End Sub
 
Ranman256, I think you want the subform's recordsource not the subform control sourceobject.

chrisjames25 - depending on how exactly your subform is "filtered" you could get the results you want or not. If the subform's recordsource is a query that is limited (with a Where clause) to the values in the combos and search textbox your export will reflect that. If the combos and search box are applying actual filters you won't; you will have to dynamically build a new query in VBA starting with the original record source to which you add a Where clause to replicate the existing filter (which you can get using Me.sfrmControlName.Form.Filter).

If you can upload a stripped down sample of your db we could have a look and give some more pertinent answers.

Cheers,
Vlad
 
Since it is a subform my code should read

Code: 
If Me.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Filter

To
Code: 
If Me.YourSubformCtrlName.Form.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.YourSubformCtrlName.Form.Filter

And if not clear, my code does exactly what bastanu describes, I assume did not see my post.
 
Sorry, I was typing as you posted it, yes that is exactly what I was suggesting.

Cheers,
Vlad
 
cheers for all above. Will give it a try when back in office and come back with feedback.

thank again for help
 
Couple of things from reading ur code im unsure on

Code: 
  strSql = "Select * from QualQ1" 'WHAT IS QUALQ1???
  If Me.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltered", strSql) 'Exportfiltered where did that name come from
  DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name
End Sub

Cheers
 
"Select * from QualQ1" 'WHAT IS QUALQ1???
Click to expand...
That is going to be the same unfiltered recordsource of your subform.
What is your subforms recordsource? If it is built on a query, like "qryPlantVarieties" your line would be "Select * from qryPlantVarieties"
'Exportfiltered where did that name come from
Click to expand...
It is a made up name. Can be whatever you want. You are creating a query definition. All the queries you see in the navigation pane are query definitions.
 
You guys are great. Code worked perfectly.

THank you so much for your contribution.
 
HI Guys. I have noticed a snag in the code.

When i export data and i have filtered data so by selecting it to only show me Fruit the export works great.

I then reset my data in the form or filter it antoher way and export again but it just exports the fruit data again and not the new data.

Code i have is
Code: 
  Dim strSql As String
  Dim TempQdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  
  strSql = "Select * from Qry_Main_VarietyForm"
  If Me.Frm_Subform.Form.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Frm_Subform.Form.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltereds", strSql)
  DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name

I assumed last line clears all querydefs so confused why remember old querydef
 
Is it that or the old filter didn't clear properly? Suggest you loop though the code to confirm.
 
You might find my show/hide and export to excel code useful. It's on my website here:- Show/Hide & Export to Excel it's listed at $6.99, however you can currently get it for free by signing up to my newsletter. Press the big green button for details!
 
For debug purposes try doing something like this so we can see what is being built
Code: 
Dim strSql As String
  Dim TempQdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb
  
  strSql = "Select * from Qry_Main_VarietyForm"
  If Me.Frm_Subform.Form.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Frm_Subform.Form.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltereds", strSql)
  'this will print the sql  see what is happening and post back
  'do a few and show what it returns and what you actually selected
  debug.print Tempqdf.sql
  'comment out below so you do not have to do a lot of exports while testing
  'DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name
 
chrisjames25 said:
HI Guys. I have noticed a snag in the code.

When i export data and i have filtered data so by selecting it to only show me Fruit the export works great.

I then reset my data in the form or filter it antoher way and export again but it just exports the fruit data again and not the new data.

Code i have is
Code: 
  Dim strSql As String
  Dim TempQdf As QueryDef
  Dim db As DAO.Database
  Set db = CurrentDb

  strSql = "Select * from Qry_Main_VarietyForm"
  If Me.Frm_Subform.Form.Filter <> "" Then
   strSql = strSql & " WHERE " & Me.Frm_Subform.Form.Filter
  End If
  Set TempQdf = db.CreateQueryDef("ExportFiltereds", strSql)
  DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX
  db.QueryDefs.Delete TempQdf.Name

I assumed last line clears all querydefs so confused why remember old querydef
Click to expand...
The last line does clear the old querydef. The issue you are experiencing lies with the FilterOn Property being either true or false.
When you remove or reset the filter, access only sets the FilterOn property to false, so when adding in the filter you need to check whether it is currently on or off.
Like this:
Dim strFilter as String
If Me.Frm_Subform.Form.FilterOn = True Then
strFilter = " WHERE " & Me.Frm_Subform.Form.Filter
Else
strFilter = ""
End If

Below is the whole procedure including the filter handling, and solving some other issues as well:
1) If the procedure errors out for some reason, like the destination excel file was open, the query def will still exist and consequently error out the next time the procedure is run.
2) In my case, I also wanted the excel file to be ordered/sorted as it appears on screen.

In my procedure below, i have the file exporting to a temp directory on my c: drive, and opening the excel file when done. The user wanted to see it, then be able to send the file from within excel to whomever they want

Dim strSql As String
Dim TempQdf As QueryDef
Dim db As DAO.Database
Set db = CurrentDb
Dim strFilter As String
Dim strOrderBy As String

'
' handle subform Filter
'
If Me.Frm_Subform.Form.FilterOn = True Then
strFilter = " WHERE " & Me.Frm_Subform.Form.Filter
Else
strFilter = ""
End If

'
' handle subform Order By -- comment out if not needed
'
If Me.Frm_Subform.Form.OrderByOn = True Then
strOrderBy = " Order By " & Me.Frm_Subform.Form.OrderBy
Else
strOrderBy = ""
End If


Dim qdf As DAO.QueryDef

'
' Build final SQL String
'
strSql = "Select * from Qry_Main_VarietyForm" & strFilter & strOrderBy

'for testing sql string issues, uncomment line below
'Debug.Print strSql

'
' Delete Temporary query if it exists already
'
For Each qdf In CurrentDb.QueryDefs
If qdf.Name = "ExportFiltereds" Then
CurrentDb.QueryDefs.Delete "ExportFiltereds"
Exit For
End If
Next

'
' Make Temporary query
'
Set TempQdf = db.CreateQueryDef("ExportFiltereds", strSql)

'
' Export to excel and then open Temporary query
'
DoCmd.OutputTo acOutputQuery, TempQdf.Name, acFormatXLSX, "C:\TEMP\FilteredOutput.XLSX", True

'
' Clean up / Delete Temporary query
'
db.QueryDefs.Delete TempQdf.Name
 
Last edited:
You must log in or register to reply here.

Similar threads

J
Export data in Work to different folders
Replies
2
Views
348
JPR
J
Y
Import-export permit tracking system
Replies
13
Views
885
mike60smart
mike60smart
M
Solved Subform. Format property. Show header and footer. No difference in putting YES putting NO
2
Replies
35
Views
1,258
theDBguy
theDBguy
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
743
Faoineag
F
Uncle Gizmo
Does Microsoft Access 2024 (Professional Plus 2024) Export Forms as Text for Re-Import?
Replies
14
Views
841
Josef P.
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom