Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    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
    End If
    strIN = strIN & "'" & lstStates.Column(0, i) & "',"
    End If
    Next 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
    End If

    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
    Next varItem

    Exit Sub


    If Err.Number = 5 Then
    MsgBox "You must make a selection(s) from the list", , "Selection Required !"
    Resume Exit_cmdOpenQuery_Click
    'Write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_cmdOpenQuery_Click
    End If

    End Sub

    And here is the Macro code
    Private Sub ExportMacro_Click()
    On Error GoTo Err_ExportMacro_Click

    Dim stDocName As String

    stDocName = "macExportOnlineStateQuery"
    DoCmd****nMacro stDocName

    Exit Sub

    MsgBox Err.Description
    Resume Exit_ExportMacro_Click

    End Sub

  2. #2
    Join Date
    Jun 2009

    Found typo in my macro

    I got it working... I had a type that I did not see....


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts