Unanswered: Macro to Export Query not working correctly
First time Access user here, so hope this makes sense.. Using MS Access 2002.
I successfully created a query. And the results are correct. The query will select "ALL" records of a specified "State" that is selected from a combo box. I created a button and when clicked I want the query result to be outputted to an excel file. I am doing this with a macro. What happens, it creates an excel file with "ALL" records, not of the query results. I created the new macro by selecting the "Macros" from the "Objects".
Action set to OutpuTo
Object Type = Query
Object Name = qryOnlineState
Output Format = Microsoft Excel (.xls)
Output Filename = c:\documents and settings\All users\desktop\Online_Test_Export.xls
Autostart = No
Here is the query code:
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM Online"
'Build the IN string by looping through the listbox
For i = 0 To lstStates.ListCount - 1
If lstStates.Selected(i) Then
If lstStates.Column(0, i) = "All" Then
flgSelectAll = True
strIN = strIN & "'" & lstStates.Column(0, i) & "',"
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [ContactState] in (" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
MyDB.QueryDefs.Delete "State Query"
Set qdef = MyDB.CreateQueryDef("State Query", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "State Query", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.lstStates.ItemsSelected
Me.lstStates.Selected(varItem) = False
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list", , "Selection Required !"
'Write out the error and exit the sub
And here is the Macro code
Private Sub ExportMacro_Click()
On Error GoTo Err_ExportMacro_Click