时间戳

首页 > 解决方案 > 保存具有不同名称的访问报告

vba - 保存具有不同名称的访问报告

问题描述

我正在尝试使用 Access Reports 保存每个员工的声明。但是，我在将Employee Name字段附加到MyFileName变量时遇到问题。

我可以将个人员工报告导出为具有以下格式的 PDF：

Statement_[EENo].pdf

但是，我希望它包含以下格式的员工姓名：

Statement_[Employee Name]_[EENo].pdf

Private Sub Command2_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String

    mypath = "FILE LOCATION"

    Set db = CurrentDb()

    Set rs = db.OpenRecordset("SELECT distinct [EENo] & [Employee Name] FROM [Query]", dbOpenSnapshot)

    rs.MoveFirst
    Do While Not rs.EOF
        temp = rs("EENo" & "Employee Name")
        MyFileName = "Statement" & "_" & [Employee Name] & "_" & Format(rs("EENo"), "000000") & ".PDF"

        DoCmd.OpenQuery "1 - Query: Firm Admin_EE"
        DoCmd.Close acQuery, "1 - Query: Firm Admin_EE", acSaveYes
        DoCmd.OpenReport "REPORT", acViewReport, , "[EENo]=" & temp
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
        DoCmd.Close acReport, "REPORT"
        DoEvents

        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

标签： vbams-accessms-access-reports

解决方案

这应该有效：

temp = rs("EENo").Value & rs("Employee Name").Value
MyFileName = "Statement_" & rs("Employee Name").Value & "_" & _
              Format(rs("EENo").Value, "000000") & ".PDF"

编辑：我并没有真正看过你的 SQL，并且错过了你在查询中组合了两列。我不使用访问，但尝试：

Set rs = db.OpenRecordset("SELECT distinct [EENo] , [Employee Name] FROM [Query]", _
                           dbOpenSnapshot)

推荐阅读