DoCmd.OutputTo acOutputTable WHERE

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?
AviationAceSole-ProprietorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
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
AviationAceSole-ProprietorAuthor Commented:
I think my problem still remains.  I will need to change the WHERE statement before the query is ran.
0
Jim P.Commented:
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

AviationAceSole-ProprietorAuthor Commented:
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
Jim P.Commented:
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
AviationAceSole-ProprietorAuthor Commented:
I understand that.  But can you give me an example of using as SQL SELECT WHERE statement in a DoCmd.OutputTo command?
0
Jim P.Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AviationAceSole-ProprietorAuthor Commented:
You rock!  Thanks.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.