• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1935
  • Last Modified:

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?
0
AviationAce
Asked:
AviationAce
  • 5
  • 4
1 Solution
 
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
 
AviationAceSole-ProprietorAuthor Commented:
You rock!  Thanks.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now