MA Access 2010 VBA代码输出位于RUN cmd in宏，而不是off form按钮中

Question

以下代码是从MS Access 2010宏调用的。我可以使用宏RUN命令来运行该例程，它会正确地在"c:\EOW“文件夹中创建并存储一个数据文件。当我从窗体上的按钮运行宏时，它似乎执行与宏运行完全相同的操作并退出，但没有像宏运行方法那样的文件输出。任何建议都很感谢。

史考特

宏中的调用是"ExportQueryToTxt("MYOBWklyInvoices"，"c:\EOW\MYOBWklyInvoices.txt "，1，“，")”，下面是VBA函数；

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 --> 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) > 0 Then strLine = strLine & ListSeparator
                    strLine = strLine & fld.Value
                Next
                If rst.Fields(DocIDIndex).Value <> varDocID Then strLine = vbNewLine & 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

Answer 1

您不需要涉及宏，这样做通常不会产生有用的错误消息。(嵌套引号可能有问题。)

显示Button的属性表单，找到On Click事件并直接在该行中键入以下内容：

=ExportQueryToTxt("MYOBWklyInvoices","c:\EOW\MYOBWklyInvoices.txt",1,",")

请注意，等号是必需的，不再需要用引号括起来。

向函数添加一个简单的MsgBox，这样就可以证明它正在运行。