Thanks Duane,
Sorry for not explaining what I am doing. ReportFilter is a sub that develops the 'WHERE' clause of the query based on selections made by the user. rptFilter is the the 'WHERE' clause. CopyQueryNew is a function to combine the original query with the where clause.
I was using the recordset as a debug tool to ensure the new query was doing what I wanted. That section of the code will not be in the final subroutine.
I think I understand that I need to create a temp named query that is based on the query from CopyQueryNew. Then I should use that named query's name as the parameter in the docmd. After finishing with the named query I can delete it in order to keep the list of queries from growing too large since each user would be having different selections on this and other tables.
Is my understanding correct or am I still missing something? I will change the Dim statement to be more explicit.
Thanks for you time.
Rod
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> Rod,
>
> You should be explicit with your Dim statements:
> Dim dbs As DAO.Database
> Dim qdf As DAO.QueryDef
> Dim rst As DAO.Recordset
>
> We don't know what ReportFilter, rptFilter, CopyQueryNew are.
>
> Why are you opening a recordset? What's the need for displaying the !lngTPTID?
>
> You would not use the qdfCopy.SQL. The argument should be a query or table name.
>
> Duane Hookom MVP
> MS Access
>
> > From: desertscroller@...
> > Date: Tue, 16 Jul 2013 02:27:34 +0000
> > Subject: Re: [MS_AccessPros] Exporting data to Excel
> >
> > Duane,
> > I hope you can help with the code in trying to export to excel. The code sample was very helpful. But I have run into an issue with the Docmd.transferSpreadsheet. Here is the sub that contains everything:
> >
> > =======
> > Private Sub Cmd_Export2Excel_Click()
> > Dim dbs As Database
> > Dim qdf As QueryDef
> > Dim rst As Recordset
> > Dim intCommand As Integer
> > Dim strOrderBy As String
> > Dim qdfCopy As QueryDef
> > Dim rstCopy As Recordset
> >
> > Set dbs = CurrentDb()
> > Set qdf = dbs.QueryDefs("Qry_Common_Flds4Rpts")
> > Set rst = qdf.OpenRecordset()
> >
> > If ReportFilter Then
> > strOrderBy = rptFilter
> > Set qdfCopy = CopyQueryNew(rst, strOrderBy)
> > Set rstCopy = qdfCopy.OpenRecordset(dbOpenSnapshot, _
> > dbForwardOnly)
> > MsgBox "query = " & qdfCopy.SQL
> > DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdfCopy.SQL, "C:\b_files\City of Gilbert\Research.xlsx", True
> > With rstCopy
> > Do While Not .EOF
> > MsgBox "Return value = " & !lngTPTID
> > .MoveNext
> > Loop
> > .Close
> > End With
> > End If
> >
> > rst.Close
> > dbs.Close
> >
> > End Sub
> > =======
> > An error message on the Docmd is as follows:
> > Run-time error '7871':
> > The table name you entered doesn't follow Microsoft Access object naming rules.
> >
> > I am pretty sure it is referring to the query object. The query string looks OK (not terminated with ';'). If the line is commented out the following steps thru the records and displays the proper info.
> >
> > Any suggestion would be helpful. I am continuing with my troubleshooting. Thanks for your time.
> > Rod
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >>
> >> This is a sample of what I was mentioning http://www.tek-tips.com/faqs.cfm?fid=7433
> >>
> >> Duane Hookom MVP
> >> MS Access
> >>
> >>> To: MS_Access_Professionals@yahoogroups.com
> >>> From: desertscroller@
> >>> Date: Mon, 15 Jul 2013 03:40:13 +0000
> >>> Subject: Re: [MS_AccessPros] Exporting data to Excel
> >>>
> >>> Duane,
> >>> Thanks for the input. As part of the data, I am trying to filter the query slightly based on inputs from the user. (This query is used as part of a report where I can filter it.) Trying to use the query in multiple environments.
> >>>
> >>> If possible could you point me to a small example to using the DAO code. I think I know what you are telling me but I would like to make sure I fully understand.
> >>>
> >>> Rod
> >>>
> >>> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@> wrote:
> >>>>
> >>>> Rod,
> >>>>
> >>>> I think you are working too hard. There shouldn't be any need to open a recordset in order to export the query to Excel.
> >>>>
> >>>> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_Common_Flds4Rpts", "C:\b_files\City of Gilbert\Research.xlsx", True
> >>>>
> >>>> I expect the issue is probably caused by references to controls from forms in the query criteria. I typically use a little DAO code to convert the control references to the values.
> >>>>
> >>>> Duane Hookom MVP
> >>>> MS Access
> >>>>
> >>>> ----------------------------------------
> >>>>> From: desertscroller@
> >>>>>
> >>>>> Hi All,
> >>>>> I am trying to export some data to Excel 2010 from Access 2010. I am getting a error about one of the parameters for DoCmd.TransferSpreadsheet
> >>>>>
> >>>>> Not sure if everything is quite right yet. The code I am using is as follows:
> >>>>> ========================================
> >>>>> Private Sub Cmd_Export2Excel_Click()
> >>>>> Dim dbs As Database, rst As DAO.Recordset, qdf As DAO.QueryDef
> >>>>> Dim rstFiltered As DAO.Recordset
> >>>>> Dim rptFilter As Variant
> >>>>> Dim qStr As String
> >>>>>
> >>>>> Set dbs = CurrentDb()
> >>>>>
> >>>>> 'Get the parameter query
> >>>>> Set qdf = dbs.QueryDefs("Qry_Common_Flds4Rpts")
> >>>>> Set rst = qdf.OpenRecordset()
> >>>>>
> >>>>> If ReportFilter Then
> >>>>> rst.Filter = rptFilter
> >>>>> Set rstFiltered = rst.OpenRecordset
> >>>>> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rstFiltered, "C:\b_files\City of Gilbert\Research.xlsx", True
> >>>>> Else
> >>>>> End If
> >>>>> dbs.Close
> >>>>>
> >>>>> End Sub
> >>>>> ==============================================
> >>>>> ReportFilter is a function used to build the filter based on inputs from the user. The filter looks good and very thing works when used in presenting a report in Access. The user wants to be able to export the data to excel provide his supervisor.
> >>>>>
> >>>>> One of my basic questions is whether I can export the filtered recordset or if I will be to process each record individually. Secondary is if I need to setup an object for excel with the code to process to.
> >>>>>
> >>>>> Thanks for any help.
> >>>>> Rod
> >>>>
> >>>
> >>>
> >>>
> >>>
> >>> ------------------------------------
> >>>
> >>> Yahoo! Groups Links
> >>>
> >>>
> >>>
> >>
> >>
> >> [Non-text portions of this message have been removed]
> >>
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (8) |
Tidak ada komentar:
Posting Komentar