?
Solved

DoCmd.OutputTo acOutputTable WHERE

Posted on 2008-11-18
9
Medium Priority
?
1,857 Views
Last Modified: 2012-05-05
The following code works just fine:
DoCmd.OutputTo acOutputTable, "header", acFormatXLS, "C:\my.xls", True

However I want to filter out only selected records before it is Outputted to the XLS file.

Any ideas?
0
Comment
Question by:AviationAce
  • 5
  • 4
9 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 22987882
Create a query that only select the records you want and then it would be:

DoCmd.OutputTo acOutputQuery, "header_Query", acFormatXLS, "C:\my.xls", True
0
 

Author Comment

by:AviationAce
ID: 22988176
I think my problem still remains.  I will need to change the WHERE statement before the query is ran.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22988253
In your query just put on the criteria line something like [Enter the company id to export] (with the brackets) and then when the query runs it will pop-up a box to enter your stuff.
Q-23915388.jpg
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:AviationAce
ID: 22993988
I guess I need to explain what I am doing better.  On a form there is a list box.  The user will select a value and then click a button to export just one record an XLS file.  I am good at building an SQL statement.  I just don't see a way to use the DoCmd.OutputTo command to make this happen.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22994188
In that case in the criteria of the query you would put Forms!MyFormName!MyListBoxName. When the user clicks to export, it will only select that record.
0
 

Author Comment

by:AviationAce
ID: 22995262
I understand that.  But can you give me an example of using as SQL SELECT WHERE statement in a DoCmd.OutputTo command?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 22995527
You can't do it directly. You can do it something like this.
Public Function BuildQueryForOutput()
 
Dim DB As Database
Dim QDF As QueryDef
Dim QryName As String
Dim SQL As String
 
Set DB = CurrentDb()                'Use the current database
 
    SQL = "SELECT * " & _
        "FROM MyTable  " & _
        "WHERE MyField = '" & Forms!MyFormName!MyTxtBox & "'; "
    
    QryName = "MyExportQuery"
    'Check if the old query is there
    If TableExistence(QryName) = True Then
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acQuery, QryName
        DoCmd.SetWarnings True
    End If
    
    'This is where I actually create the query
    
    With DB
        Set QDF = .CreateQueryDef(QryName, SQL)
    End With
    
    DoCmd.OutputTo acOutputQuery, QryName, acFormatXLS, "C:\MyPath\MyFile.XLS", False
            
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acQuery, QryName
    DoCmd.SetWarnings True
 
    RS.MoveNext
Loop
 
Set DB = Nothing
 
End Function
Public Function TableExistence(TableName As String) As Boolean
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
TableExistence = False
 
SQL = "SELECT NAME " & _
        "FROM  MSysObjects " & _
        "WHERE TRIM(UCASE(NAME)) = '" & Trim(UCase(TableName)) & "';"
 
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)
 
If RS.EOF = True Then
    TableExistence = False
Else
    RS.MoveFirst
    If Trim(UCase(RS!NAME)) = Trim(UCase(TableName)) Then
        TableExistence = True
    End If
End If
 
End Function

Open in new window

0
 

Author Closing Comment

by:AviationAce
ID: 31517977
You rock!  Thanks.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22997229
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question