Wait while pdf opens and prints before proceding

[oxicottin]

Hello, How do I get the VBA below to wait until the .pdf opens and prints before continuing? My reports all stack in order by dates etc and the pdf is in the middle when its supposed to be after the first print. The code in question is marked with HERE.... TO HERE. I tried using DoEvents after the line:

CreateObject("Shell.Application").Namespace(0).ParseName(strSafetyContactPath).InvokeVerb ("Print")

but it seems like it doesn't wait? I also tried sleep for 8 seconds using:

Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliSeconds As Long)

And when it gets to the Sleep(8000) Access closes.

Public Sub Print4Days()
    Dim strSafetyContactPath As String
  
'***********************************************************************************************************
'Print Weekly safety hudle cover sheet report
    DoCmd.OpenReport "rpt_WeeklySafetyHuddle", acViewNormal
  
HERE '***********************************************************************************************************
'Print .pdf if textbox is checked if not then move on
  
'Pdf Documents full path in from txtUnboundSafetyCPath
    strSafetyContactPath = Nz(Forms!frm_WeeklySafetyHuddle!txtUnboundSafetyCPath, 0)
  
    If Len(Dir$(strSafetyContactPath)) > 0 Then
        SetAttr strSafetyContactPath, vbNormal
'Print the .pdf file
        CreateObject("Shell.Application").Namespace(0).ParseName(strSafetyContactPath).InvokeVerb ("Print")
    Else
'Do Nothing there wasnt a file just move on
    End If
  
TO HERE '***********************************************************************************************************
'Print Monday - Tuesday SpotCheck Reports
    Dim x As Integer
  
    For x = 4 To 3 Step -1
        DoCmd.OpenReport "rpt_SpotCheck", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - x, "Long Date")
        DoCmd.Close
    Next
  
'***********************************************************************************************************
'Print Wednesdays Gemba Walk and SpotCheck Reports
    DoCmd.OpenReport "rpt_GembaWalk", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - 2, "Long Date")
    DoCmd.OpenReport "rpt_SpotCheck", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - 2, "Long Date")
  
'***********************************************************************************************************
'Print Thursdays SpotCheck Report
    DoCmd.OpenReport "rpt_SpotCheck", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - 1, "Long Date")
  
'***********************************************************************************************************
'Close Acrobat Reader
        Call Close_Acrobat_Reader
      
'Delete the .pdf file
        Kill strSafetyContactPath
  
'***********************************************************************************************************
'Clear a few textboxes on frm_WeeklySafetyHuddle
     Forms!frm_WeeklySafetyHuddle.txtUnboundSafetyCPath = ""
     Forms!frm_WeeklySafetyHuddle.txtVWIReviewed = ""
     Forms!frm_WeeklySafetyHuddle.txtSOCReviewed = ""
     Forms!frm_WeeklySafetyHuddle.txtLOTOReviewed = ""
     Forms!frm_WeeklySafetyHuddle.chkPrintPaperLift = False

'Set focus to current week button on frm_WeeklySafetyHuddle
   Forms!frm_WeeklySafetyHuddle.cmdCurrentFriday.SetFocus
    
'***********************************************************************************************************
'Completed message box
MsgBox "4 day printout is completed!", vbInformation, "Completed"
  
End Sub

 

[The_Doc_Man]

The problem is a holdover from the beginnings of printer queues. Once you issue the PRINT command, whether from a command line; a click on a Windows menu; a click in a program's menu; or execute a VBA command, the command sends the entire file to the printer queue which is in a separate memory segment (in a different task, a SYSTEM service actually). Once it is there, you cannot see it directly because at that point, it is NOT yours. It belongs to the system. As soon as the relevant file has been transferred to the print queue, the PRINT command relinquishes control.

You can get to see the visible printers via

Printers object (Access)

Office VBA reference topic

docs.microsoft.com

and you can find a specific printer by a For Each <printer object> In Application.Printers loop. This would perhaps help you figure out which printer was your target if you didn't specify a particular printer. Once you have a printer object representing the one to which you sent your file, you can look at its properties via

Printer object (Access)

Office VBA reference topic

docs.microsoft.com

Unfortunately, this doesn't get you much closer though you can at least get a specific device name for subsequent operations. You can find out if the print spooler is running via

Checking Print Spooler status (running or not)

I need to detect whether the Print Spooler service is running. I can find various resources for VB.NET (e.g., using ServiceProcess.ServiceController to actually manipulate the service), but nothing...

stackoverflow.com

Obviously, if the print spooler is not running, your job is not active. But to test its status in greater detail, you have to play with some Win32 VBA code. Try this, which MIGHT actually give you what you want.

Visual Basic | Get the number of print jobs in a print queue Code Example

visualbasic.happycodings.com

IF your printer is local to your machine, you should have no trouble doing this. However, if your printer is shared among many machines, then you might run afoul of system security since you would be looking at not only your own print jobs, but everyone else's print jobs, too. And to Windows, seeing details about someone else's work is a strict NO-NO. Therefore, digging deep in the print queue will have to include error handling for the security errors you would probably encounter.

 

[The_Doc_Man]

I see you just picked a long enough time to let it print, hoping it is enough time. If it works for you, go for it.

 

[oxicottin]

@The_Doc_Man I can get it to print correctly using the code below for a 64 bit device but for some reason half way through running the code it closes or hides my form then showes is again.

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)

Option Compare Database
Option Explicit

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)


Public Sub Print4Days()
    Dim strSafetyContactPath As String
   
'***********************************************************************************************************
'Print Weekly safety hudle cover sheet report
    DoCmd.OpenReport "rpt_WeeklySafetyHuddle", acViewNormal
   
'***********************************************************************************************************
'Print .pdf if textbox is checked if not then move on
   
'Pdf Documents full path in from txtUnboundSafetyCPath
    strSafetyContactPath = Nz(Forms!frm_WeeklySafetyHuddle!txtUnboundSafetyCPath, 0)
   
    If Len(Dir$(strSafetyContactPath)) > 0 Then
        SetAttr strSafetyContactPath, vbNormal
'Print the .pdf file
        CreateObject("Shell.Application").Namespace(0).ParseName(strSafetyContactPath).InvokeVerb ("Print")
       
    Sleep (8000) '8 Seconds
   
'Close Acrobat Reader
        Call Close_Acrobat_Reader
       
'Delete the .pdf file
        Kill strSafetyContactPath
    Else
'Do Nothing there wasnt a file just move on
    End If
   
'***********************************************************************************************************
'Print Monday - Tuesday SpotCheck Reports
    Dim x As Integer
   
    For x = 4 To 3 Step -1
        DoCmd.OpenReport "rpt_SpotCheck", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - x, "Long Date")
        DoCmd.Close
    Next
   
'***********************************************************************************************************
'Print Wednesdays Gemba Walk and SpotCheck Reports
    DoCmd.OpenReport "rpt_GembaWalk", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - 2, "Long Date")
    DoCmd.OpenReport "rpt_SpotCheck", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - 2, "Long Date")
   
'***********************************************************************************************************
'Print Thursdays SpotCheck Report
    DoCmd.OpenReport "rpt_SpotCheck", , , , , Format(Forms!frm_WeeklySafetyHuddle!txtWeekEnding - 1, "Long Date")

'***********************************************************************************************************
'Clear a few textboxes on frm_WeeklySafetyHuddle
     Forms!frm_WeeklySafetyHuddle.txtUnboundSafetyCPath = ""
     Forms!frm_WeeklySafetyHuddle.txtVWIReviewed = ""
     Forms!frm_WeeklySafetyHuddle.txtSOCReviewed = ""
     Forms!frm_WeeklySafetyHuddle.txtLOTOReviewed = ""
     Forms!frm_WeeklySafetyHuddle.chkPrintPaperLift = False

'Set focus to current week button on frm_WeeklySafetyHuddle
   Forms!frm_WeeklySafetyHuddle.cmdCurrentFriday.SetFocus
     
'***********************************************************************************************************
'Completed message box
MsgBox "4 day printout is completed!", vbInformation, "Completed"
   
End Sub