Databases
StackOverflow2013
Postsdbo
POVBA DoCmd.OutputTo With QueryDef
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA DoCmd.OutputTo With QueryDef
    primarykey
    data
    text
    <p>I've been looking a while now for a solution to export a query with open parameters. I need to export a Query as a Formatted Excel Spreadsheet and can't create additional Tables, Queries, Forms, or Reports to the Database being used. I use DoCmd.OutputTo as it exports a formatted query unlike DoCmd.TransferSpreadsheet however I can't seem to export the query with defined parameters. I need to include the parameters or else the user will be forced to input the start and end date three times a piece as the database for some reason asks for the startDate and endDate twice and in order to keep the excel spreadsheet and the subsequent outlook section consistant i would have to ask the user to input their previous parameters again </p> <pre><code>Sub Main() On Error GoTo Main_Err 'Visually Display Process DoCmd.Hourglass True Dim fpath As String Dim tname As String Dim cname As String Dim tType As AcOutputObjectType Dim tempB As Boolean fpath = CurrentProject.path &amp; "\" 'tType = acOutputTable 'tname = "APPROVED SWPS FOR LOOK AHEAD &amp; BAR CHART" tType = acOutputQuery tname = "ASFLA&amp;BC Query" cname = "Temp BPC Calendar" Dim qdfQry As DAO.QueryDef Dim strStart As String Dim strEnd As String Set qdfQry = CurrentDb().QueryDefs(tname) 'strStart = InputBox("Please enter Start date (mm/dd/yyyy)") 'strEnd = InputBox("Please enter Start date (mm/dd/yyyy)") qdfQry.Parameters("ENTER START DATE") = FormatDateTime("6/30/12", vbShortDate) 'strEnd qdfQry.Parameters("ENTER END DATE") = FormatDateTime("7/1/12", vbShortDate) 'strStart tempB = Backup(fpath, qdfQry, tType) If (Not tempB) Then MsgBox "Excel Conversion Ended Prematurely..." Exit Sub End If ' tempB = sendToOutlook(qdfQry, cname) ' If (Not tempB) Then ' MsgBox "Access Conversion Ended Prematurely..." ' Exit Sub ' End If MsgBox "Procedure Completed Successfully" Main_Exit: DoCmd.Hourglass False Exit Sub Main_Err: DoCmd.Beep MsgBox Error$ Resume Main_Exit End Sub '************************************************************************************ '* '* Excel PORTION '* '************************************************************************************ Public Function Backup(path As String, db As DAO.QueryDef, Optional outputType As AcOutputObjectType) As Boolean On Error GoTo Error_Handler Backup = False Dim outputFileName As String Dim name As String Dim tempB As Boolean 'Set Up All Name Variablesand name = Format(Date, "MM-dd-yy") &amp; ".xls" 'Cleans Directory of Any older files and places them in an archive SearchDirectory path, "??-??-??.xls", name 'See If File Can Now Be Exported. If Already Exists ask to overwrite outputFileName = path &amp; name tempB = OverWriteRequest(outputFileName) If tempB Then 'Formats The Table And Exports Into A Formatted SpreadSheet 'Checks if an output type was added to the parameter if not defualt to table If Not IsMissing(outputType) Then DoCmd.OutputTo outputType, db.name, acFormatXLS, outputFileName, False Else DoCmd.OutputTo acOutputTable, db.name, acFormatXLS, outputFileName, False End If Else Exit Function End If Backup = True Error_Handler_Exit: Exit Function Error_Handler: MsgBox "MS Access has generated the following error" &amp; vbCrLf &amp; vbCrLf &amp; "Error Number: " &amp; _ Err.number &amp; vbCrLf &amp; "Error Source: Main Excel Backup" &amp; vbCrLf &amp; "Error Description: " &amp; _ Err.Description, vbCritical, "An Error has Occured!" Resume Error_Handler_Exit End Function </code></pre> <p>The SQL currently given looks like similar to below with omitted fields for for clarity</p> <pre><code>PARAMETERS [ENTER START DATE] DateTime, [ENTER END DATE] DateTime; SELECT [SWPS].STATION, [SWPS].START_DATE, [SWPS].END_DATE, FROM [SWPS] WHERE ((([SWPS].STATION) Like ("*")) AND (([SWPS].START_DATE)&lt;=[ENTER END DATE]) AND (([SWPS].END_DATE)&gt;=[ENTER START DATE]) AND (([SWPS].SWP_STATUS) In ("A","P","W","T","R"))); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkdgwill
    1. USkdgwill
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVBA DoCmd.OutputTo With QueryDef
      1. UShoopzbarkley
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload