title: MA Access 2010 VBA code outputs in RUN cmd in macro but not off form button
body: <p>The following is called from a MS Access 2010 macro. I can use the macro RUN command to run the routine and it correctly creates and stores a file of data in the "c:\EOW" folder.
  When I run the macro from a button on a form, it appears to do exactly the same thing and exits as the macro RUN does but there is no file output as there is with the macro RUN method.
  Any advice appreciated.</p>
  
  <p>Scott</p>
  
  <p>Call in macro is "ExportQueryToTxt("MYOBWklyInvoices","c:\EOW\MYOBWklyInvoices.txt",1,",")"
  and the VBA function is below;</p>
  
  <pre><code>Option Compare Database
  Option Explicit
  
  Public Function ExportQueryToTxt(ByVal DataSource As String, _
                       ByVal FileName As String, _
                       Optional DocIDIndex As Long = 0, _
                       Optional ByVal ListSeparator As String = ",")
  
  ' See  http://www.dbforums.com/microsoft-access/1664379-automatically-adding-blank-lines-text-file.html
  '
  ' SMW NOTE: definitely does not work with a query with a PARAMETER in it
  '           Does work if make a table from query and use it
  ' DataSource:    Name of a table, a SELECT query or a SELECT SQL statement.
  '                In any case, the rowset must be sorted on the DocIDIndex column.
  
  ' FileName:      Name of the output file that will receive the exported rows.
  
  ' DocIDIndex:    Ordinal position of the column in the rowset
  '                that contains the document ID (default = 0 --&gt; first column).
  
  ' ListSeparator: Character used to separate the different columns of data
  '                in the text output file (default = ",")
  '
  ' Example of call: ExportQueryToTxt "Qry_Export", "c:\export.txt", 1, ";"
  '
      Dim dbs As DAO.Database
      Dim rst As DAO.Recordset
      Dim fld As DAO.Field
      Dim intHandle As Integer
      Dim strLine As String
      Dim varDocID As Variant
  
      intHandle = FreeFile
      Open FileName For Output As #intHandle   ' Use 'For Append' to add lines to an existing file, if any.
      Set dbs = CurrentDb
      ' MsgBox ("Started")
      Set rst = dbs.OpenRecordset(DataSource)
  
      ' SMW load first line with MYOB field names + input week number parameter
      strLine = ""
      strLine = "Ignore,Invoice,Customer PO,Description,Account,Amount,Inc Tax,Supplier,Journal Memo,SP First Name,Tax Code,GST Amount,Category,CardID"
      Print #intHandle, strLine
      strLine = ""
      ' End SMW
      With rst
          If Not .EOF Then
          ' note that following will group anything wth same invoice or similar number togeher in
          ' sequential rows then inject a blank row when the invoice number changes
          varDocID = rst.Fields(DocIDIndex).Value
              Do Until .EOF
                  For Each fld In rst.Fields
                      If Len(strLine) &gt; 0 Then strLine = strLine &amp; ListSeparator
                      strLine = strLine &amp; fld.Value
                  Next
                  If rst.Fields(DocIDIndex).Value &lt;&gt; varDocID Then strLine = vbNewLine &amp; strLine
                  Print #intHandle, strLine
                  strLine = ""
                  varDocID = rst.Fields(DocIDIndex).Value
                  .MoveNext
              Loop
          End If
          .Close
      End With
      Set rst = Nothing
      Close #intHandle
      ' SMW added to advise when done
      ' MsgBox ("Finished making invoices")
  
  End Function
  </code></pre>