Access VBA：如何判断报表是否打印至物理打印机而非预览或PDF

在mydb.accdb Access应用中，我们通过报表打印发票，tblMainOrders表的DatePrinted字段用于记录订单最后打印时间，以此确认是否已向供应商发送附带PDFCreator生成的PDF附件的邮件。

原代码试图在Report_Close()事件中通过Me.CurrentView = 5判断是否更新字段，但该属性在报表预览、物理打印、PDF生成时始终为5，导致DatePrinted被错误更新。我们需要实现仅在实际打印到物理打印机时更新字段的逻辑。

原代码如下：

Private Sub Report_Close()
  Dim strSQL As String
  '
  ' we want to update .DatePrinted only if the report is really printed on Paper, not on Print Preview mode on Screen:
  '
  '
  ' not work, always true on Screen or true printer:
  'if(Me.Printer.DeviceName = "HP Laser Printer") Then
  '
  ' not work, this is always true:
  '
  if(Me.CurrentView = 5) Then
    strSQL = "UPDATE tblMainOrders SET DatePrinted = #" & Now() & "# WHERE (OrderID = 12345)"
    CurrentDb.Execute strSQL, dbSeeChanges
  End If
End Sub

解决方案 #

方法1：通过打印机设备名+Report_Print事件区分 #

如果你的场景中只有特定虚拟打印机（如PDFCreator）需要排除，可通过判断报表当前使用的打印机设备名，结合Report_Print事件（仅在打印操作触发时执行，预览不会触发）实现：

1. 在报表模块顶部定义模块级布尔变量，标记是否需要更新字段：

Private blnNeedUpdateDatePrinted As Boolean

2. 在Report_Print事件中，判断当前打印机是否为物理打印机（排除虚拟打印机）：

Private Sub Report_Print(Cancel As Integer)
    ' 替换为你的虚拟打印机设备名，可根据实际情况添加多个判断
    If Me.Printer.DeviceName <> "PDFCreator" Then
        blnNeedUpdateDatePrinted = True
    End If
End Sub

3. 修改Report_Close()事件，根据标记执行更新：

Private Sub Report_Close()
    Dim strSQL As String
    If blnNeedUpdateDatePrinted Then
        ' 格式化日期避免区域设置导致的错误
        strSQL = "UPDATE tblMainOrders SET DatePrinted = #" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "# WHERE (OrderID = 12345)"
        CurrentDb.Execute strSQL, dbSeeChanges
    End If
    ' 重置变量
    blnNeedUpdateDatePrinted = False
End Sub

方法2：用API通用判断物理/虚拟打印机 #

如果需要不依赖设备名的通用判断，可以调用Windows API获取打印机属性：

1. 在标准模块或报表模块中添加API声明（兼容32/64位Access）：

#If VBA7 Then
    Declare PtrSafe Function GetPrinter Lib "winspool.drv" Alias "GetPrinterA" (ByVal hPrinter As LongPtr, ByVal Level As Long, pPrinter As Any, ByVal cbBuf As Long, pcbNeeded As Long) As Long
    Declare PtrSafe Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As LongPtr, pDefault As Any) As Long
    Declare PtrSafe Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As LongPtr) As Long
#Else
    Declare Function GetPrinter Lib "winspool.drv" Alias "GetPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, pPrinter As Any, ByVal cbBuf As Long, pcbNeeded As Long) As Long
    Declare Function OpenPrinter Lib "winspool.drv" Alias "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, pDefault As Any) As Long
    Declare Function ClosePrinter Lib "winspool.drv" (ByVal hPrinter As Long) As Long
#End If

Type PRINTER_INFO_2
    pServerName As String
    pPrinterName As String
    pShareName As String
    pPortName As String
    pDriverName As String
    pComment As String
    pLocation As String
    pDevMode As Long
    pSepFile As String
    pPrintProcessor As String
    pDatatype As String
    pParameters As String
    pSecurityDescriptor As Long
    Attributes As Long
    Priority As Long
    DefaultPriority As Long
    StartTime As Long
    UntilTime As Long
    Status As Long
    cJobs As Long
    AveragePPM As Long
End Type

' 判断是否为物理打印机
Function IsPhysicalPrinter(strPrinterName As String) As Boolean
    Dim hPrinter As LongPtr
    Dim pi2 As PRINTER_INFO_2
    Dim cbNeeded As Long
    Dim lResult As Long
    
    If OpenPrinter(strPrinterName, hPrinter, ByVal 0&) = 0 Then Exit Function
    
    ' 获取所需缓冲区大小
    lResult = GetPrinter(hPrinter, 2, ByVal 0&, 0, cbNeeded)
    ' 获取打印机信息
    lResult = GetPrinter(hPrinter, 2, pi2, cbNeeded, cbNeeded)
    
    ' 通过Attributes判断：排除工作站/网络属性的打印机（虚拟打印机常见特征）
    If (pi2.Attributes And &H40) = 0 And (pi2.Attributes And &H80) = 0 Then
        IsPhysicalPrinter = True
    End If
    
    ClosePrinter hPrinter
End Function

2. 报表模块中调用该函数：

Private blnNeedUpdateDatePrinted As Boolean

Private Sub Report_Print(Cancel As Integer)
    If IsPhysicalPrinter(Me.Printer.DeviceName) Then
        blnNeedUpdateDatePrinted = True
    End If
End Sub

Private Sub Report_Close()
    Dim strSQL As String
    If blnNeedUpdateDatePrinted Then
        strSQL = "UPDATE tblMainOrders SET DatePrinted = #" & Format(Now(), "yyyy-mm-dd hh:nn:ss") & "# WHERE (OrderID = 12345)"
        CurrentDb.Execute strSQL, dbSeeChanges
    End If
    blnNeedUpdateDatePrinted = False
End Sub

关键说明 #

• Report_Print事件仅在执行打印操作（物理/虚拟）时触发，报表预览不会触发，这是区分预览和打印的核心。
• 方法1适合场景固定的情况，只需排除已知虚拟打印机；方法2更通用，无需硬编码设备名。
• 使用Format(Now(), "yyyy-mm-dd hh:nn:ss")统一日期格式，避免不同区域设置下的SQL语法错误。

内容的提问来源于stack exchange，提问作者jacouh