帮助将激励报告保存为主管级别的个人PDF [英] Help saving incentive report as individual PDF at Supervisor Level

问题描述

我有一份员工奖励的访问报告。每个页面都是单个员工的数据。我们目前正在将整个报告保存为PDF，然后将相应的员工报告提取到他/她的主管的文件夹中。



主管Joe Dirt有4名员工。我们必须找到他的员工，从PDF信息中提取一个新的PDF并保存在Joe的文件夹中。

手动，艰苦的过程。



我已经研究过并提出了一个有效的代码，但并不完全。这将是我的主管名单，并给他们每个PDF和所有报告。 （报告长达24页，每个主管在他们的文件夹中获得相同的23页报告。即使他们不是这个领域的主管。）



我需要帮忙。我相信我应该能够添加一个where语句，它可以/将引用报告中的隐藏字段，即Sup_Sup_Name。我无法弄清楚代码。



这是我的代码：

I have an Access Report that is employee incentives. Each page is an individual employee''s data. We are currently saving the entire report as PDF, then extracting the appropriate employees report into his/her supervisor''s folder.

Supervisor Joe Dirt has 4 employees. We have to find his employees, extract from the PDF info a new PDF and save in Joe''s folder.
Manual, painstaking process.

I''ve researched and came up with a code that sorta works, but not fully. It will take my list of supervisors and give them EACH a PDF with ALL the reports. (Report is 24 pages long, each supervisor gets the SAME EXACT 23 page report in their folder. Even if they aren''t a supervisor in this area.)

I need help. I beleive I should be able to add a where statement that could/would refer to a hidden field on the report, which is the Sup_Sup_Name. I can''t figure out the code.

Here is my code:

Private Sub Command130_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SUP_Sup_name] FROM [query03 Retail Lender] ORDER BY [SUP_Sup_name];", dbOpenSnapshot)

Do While Not rst.EOF
    strRptFilter = "[SUP_Sup_Name] = " & Chr(34) & rst![SUP_Sup_Name] & Chr(34)

    DoCmd.OutputTo acOutputReport, "Indv Retail Lender", acFormatPDF, "g:\data folder\data\incentive\" & "\" & rst![SUP_Sup_Name] & ".pdf"
    DoEvents
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

Private Sub Report_Close()
strRptFilter = vbNullString
End Sub

Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
     Me.Filter = strRptFilter
     Me.FilterOn = True
End If
End Sub

推荐答案

您使用DAO，所以..我们可以动态更改查询：

You use DAO, so.. we can dynamically change query:

Function ChangeQuery(sQryName As String, sSQL As String) As Boolean
Dim db As Database, qry As QueryDef

'default returned value
ChangeQuery = True

'ignore errors to check if query by given name exists
On Error Resume Next

Set qry = CurrentDb.QueryDefs(sQryName)
If Not qry is Nothing Then CurrentDb.Querydefs.Delete sQryName

'in case of deletion
CurrentDb.QueryDefs.Refresh

'catch errors
On Error GoTo Err_ChangeQuery

'add new query and refresh collection of queries
Set qry = CurrentDb.CreateQueryDef(sQryName, sSQL)
CurrentDb.QueryDefs.Refresh

Exit_ChangeQuery:
    On Error Resume Next
    qry.Close
    Set qry = Nothing
    db.close
    Set db = Nothing
    Exit Function

Err_ChangeQuery:
    Err.Clear
    ChangeQuery = False
    Resume Exit_ChangeQuery
End Function

用法：

Usage:

Private Sub Command130_Click()
Dim rst As DAO.Recordset, bRetVal As Boolean, sSQL As String
 
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [SUP_Sup_name] FROM [query03 Retail Lender] ORDER BY [SUP_Sup_name];", dbOpenSnapshot)
 
Do While Not rst.EOF
    sSQL = "SELECT * FROM SupEmployees WHERE [SUP_Sup_Name] = " & rst![SUP_Sup_Name]
    bRetVal  = ChangeQuery("Indv Retail Lender", sSQL)
    If bRetVal Then DoCmd.OutputTo acOutputReport, "Indv Retail Lender", acFormatPDF, "g:\data folder\data\incentive\" & "\" & rst![SUP_Sup_Name] & ".pdf"
    DoEvents
    rst.MoveNext
Loop
 
rst.Close
Set rst = Nothing
End Sub

删除私有子报告_关闭（）和私人子报告_打开（取消为整数） ）来自报告。

这篇关于帮助将激励报告保存为主管级别的个人PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！