Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / October 2006

Tip: Looking for answers? Try searching our database.

Export Subform results to EXCEL

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
G - 27 Feb 2005 19:57 GMT
I have a form and subform. When the user selects criteria on the form, the
subform reflects the criteria.
I need to export the results to excel, but I must be doing it wrong, because
it exports all the records, not just the selected results.

I am using the following...

   'export the results to Excel
   DoCmd.OutputTo acOutputForm, "subfrmData", acFormatXLS, "1.xls", True

Thank you,
G
True
Signature

Thank you in advance for your assistance. It is greatly appreciated.

Dirk Goldgar - 27 Feb 2005 20:19 GMT
> I have a form and subform. When the user selects criteria on the
> form, the subform reflects the criteria.
[quoted text clipped - 6 lines]
>     DoCmd.OutputTo acOutputForm, "subfrmData", acFormatXLS, "1.xls",
> True

The OutputTo method is going to export the subform's recordsource as it
is defined in the form's Design View, not reflecting the changes you've
made to it on the fly.  It won't export the recordset currently
displayed on the open subform.

There may be a better way, but the only way to do this that I can think
of offhand is to create a stored query to represent the form's current
recordsource query, then export that.  Maybe you could use logic like
this:

   Dim db As DAO.Database
   Dim qdf As DAO.QueryDef

   Set db = CurrentDb

   ' Get the existing export query, or create it if
   ' it doesn't exist.
   On Error Resume Next
   Set qdf = db.QueryDefs("qryDataExport")
   If qdf Is Nothing Then
       Set qdf = db.CreateQueryDef("qryDataExport")
   End If
   If qdf Is Nothing Then
       MsgBox Err.Description, vbExclamation, "Error " & Err.Number
       Exit Sub
   End If
   On Error GoTo 0   ' or your error-handler

   ' Here we have got a reference to the export query.
   ' Set its SQL property to the query we want to export.
   qdf.SQL = Me!subfrmDataFiltered.Form.RecordSource

   Set qdf = Nothing  ' we're done manipulating it.

   ' Now export the query.
   DoCmd.OutputTo _
           acOutputQuery, "qryDataExport", _
           acFormatXLS, "1.xls", True

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

G - 27 Feb 2005 22:25 GMT
Dirk,

That worked. but I have a question on it.
the line
Set qdf = Nothing  ' we're done manipulating it.
Why is that set to nothing before exporting? Seems like that would make the
dataset null (which it didn't).

G

> > I have a form and subform. When the user selects criteria on the
> > form, the subform reflects the criteria.
[quoted text clipped - 45 lines]
>             acOutputQuery, "qryDataExport", _
>             acFormatXLS, "1.xls", True
Dirk Goldgar - 27 Feb 2005 22:49 GMT
> Dirk,
>
[quoted text clipped - 3 lines]
> Why is that set to nothing before exporting? Seems like that would
> make the dataset null (which it didn't).

A QueryDef object is a code structure that represents a query that is
stored in an Access database.  When the query is actually created in the
database -- whether by way of the Acess user interface or by code such
as the "db.CreateQueryDef" line in the code I posted -- there is
something physically stored in the database's internal works that
defines the query.  A QueryDef object, as I said, is a code
representation of that physically stored query, and it includes
properties and methods that allow us to manipulate the query in code,
but it is not the same thing as the stored query.

The code I posted creates a QueryDef object to represent the query named
"qryDataExport".  If the query doesn't already exist it first creates
it, but either way we end up with the object variable qdf pointing to a
QueryDef object (code structure) in memory, which is a representation of
the actual query as it is stored in the database.  We then set its SQL
property, which (because that's the way the QueryDef object is defined
to work) physically changes the stored query.  Then we destroy the
QueryDef object, but that doesn't destroy the stored query;  it only
destroys this code representation of the query.  The query itself is
still there, stored in the database, until we delete it one way or
another.  I decided not to automatically deleted it after exporting the
query to Excel, because I figure you're going to use it again, just with
different SQL.

Does that clarify the matter?

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

harrykp - 24 Oct 2006 10:23 GMT
In the last part of your code i set mine as:
qdf.SQL = Me.frm_edit_records_datasheet_subform.Form.RecordSource

I however keep getting this error:
"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'. (Error 3129)"

And I am using this search code to display the search results in the subform:

Private Sub Search_Click()
   Const cInvalidDateError As String = "You have entered an invalid date."
   Dim strWhere As String
   Dim strError As String
   
   strWhere = "1=1"

   ' If status
   If Nz(Me.status) <> "" Then
       'Add it to the predicate - exact match
       strWhere = strWhere & " AND " & "writing_project.status = '" & Me.
status & "'"
   End If
   

   ' If class
   If Nz(Me.class) <> "" Then
       'Add it to the predicate - exact match
       strWhere = strWhere & " AND " & "writing_project.class = '" & Me.
class & "'"
   End If

           
               
   If strError <> "" Then
       MsgBox strError
   Else
       'DoCmd.OpenForm "frm_edit_records_datasheet_subform", acFormDS, ,
strWhere, acFormEdit, acWindowNormal
       If Not Me.FormFooter.Visible Then
           Me.FormFooter.Visible = True
           DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
       End If
       Me.frm_edit_records_datasheet_subform.Form.Filter = strWhere
       Me.frm_edit_records_datasheet_subform.Form.FilterOn = True
   End If
End Sub
Any ideas will be very much appreciated. Thanks

>> Dirk,
>>
[quoted text clipped - 27 lines]
>
>Does that clarify the matter?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.