Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Print and close code runs too fast and then won't run again

    I have made a word template that I update using field codes in order to create a label.

    I have cobbled together the following code to call the template, shove values in it, set the printer to a certain tray, print and then close without saving.

    I initially had the problem that the the word doc was being closed before the print sequence had finished so I put a msgbox in and that seemed to solve it but now I can only call the process once and have it work. If I use it again then nothing happens as far as word is concerned but the msgbox still comes up.

    To get it to run again I have to shut down and reopen the DB.

    I would also like to be able to get rid of the msgbox if some sort of wait can be put in.

    EDIT:

    ok, the ActiveDocument.PageSetup is returning: <The remote server machine does not exist or is unavailable> basically a second button press has no ActiveDocument to work on.

    Using the ActiveDocument was the only code I could find which would allow me to specify the paper bin.

    I have no idea how to solve this problem, anyone have any ideas?

    Code:
    Private Sub cmdPrintLabel_Click()
    
       On Error GoTo cmdPrintLabel_Click_Error
       
       DoCmd.RunCommand acCmdSaveRecord
    
       Dim dbs As Database
       Dim objDocs As Object
       Dim objWord As Object
       Dim prps As Object
       Dim rst As dao.Recordset
       Dim strCustomer As String
       Dim strJobNum As String
       Dim x As Integer
       
       Dim strTestFile As String
       Dim strWordTemplate As String
       Dim strWordDoc As String
       Dim prtr As Printer
       
       'Create a Word instance to use for the Quote; uses the existing Word
       'instance if there is one, otherwise creates a new instance
       On Error Resume Next
       Set objWord = GetObject(, "Word.Application")
       If Err.Number = 429 Then
          'Word is not running; creating a Word object
          Set objWord = CreateObject("Word.Application")
          Err.Clear
       End If
    
    'count the number of word documents and if greater than 0 get them shut down
    If objWord.Documents.count > 0 Then
    If MsgBox("Any open Word docs will be auto closed without saving.", vbOKOnly, "There are Word docs open!") = vbOK Then
    objWord.Documents.Close
    End If
    End If
      
       'strWordTemplate = "C:\Program Files\Microsoft Office\Templates\JobLabels.dotm"
       strWordTemplate = "R:\JobLabels.dotm"
          
          'Check for existence of template in template folder,
       'and exit if not found
       strTestFile = Nz(Dir(strWordTemplate))
       If strTestFile = "" Then
          MsgBox strWordTemplate & " template not found; can't create Label"
          Exit Sub
       End If
       
       Set Application.Printer = Nothing
       Set prtr = Application.Printer
       
       prtr.PaperBin = acPRBNUpper
             
       Set objDocs = objWord.Documents
       objDocs.Add strWordTemplate
       
       Set objWord.Printer = prtr
       Debug.Print objWord.Printer
       'Write information to Word custom document properties from
       'previously created variables
       Set prps = objWord.ActiveDocument.CustomDocumentProperties
       prps.Item("JobNum").Value = [Job #]
       prps.Item("Customer").Value = cboAccName.Column(1)
        
       'Highlight the entire Word document and update fields, so the data
       'written to the custom doc props is displayed in the DocProperty fields
       objWord.selection.WholeStory
       objWord.selection.Fields.Update
       objWord.selection.HomeKey Unit:=6
       objWord.visible = True
       objWord.Activate
       objWord.windowstate = 0
       With ActiveDocument.PageSetup
       .FirstPageTray = wdPrinterUpperBin
       ActiveDocument.PrintOut
       End With
       objWord.visible = False
       MsgBox ("Your Label has been printed.")
       objWord.Quit SaveChanges:=wdDoNotSaveChanges
       
       Set Application.Printer = Nothing
       
       Set objWord = Nothing
       
       cboFindJob.SetFocus
       
       On Error GoTo 0
       Exit Sub
    
    cmdPrintLabel_Click_Error:
    
        MsgBox "Error " & Err.Number & " (" & Err.DESCRIPTION & ") in procedure cmdPrintLabel_Click of VBA Document Form_frmQuickCard"
    End Sub
    My problem seems to be in here somewhere

    Code:
       With ActiveDocument.PageSetup
       .FirstPageTray = wdPrinterUpperBin
       ActiveDocument.PrintOut
       End With
       objWord.visible = False
       MsgBox ("Your Label has been printed.")
       objWord.Quit SaveChanges:=wdDoNotSaveChanges
    THanks to all in advance.
    Last edited by weyrman; 2015-07-14 at 07:49.
    "Heading for the deep end"

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Problem needing to close and reopen database

    Hi
    To get it to run again I have to shut down and reopen the DB.
    I am wondering if you need to close a recordset or the database in the code.
    In the published excerpt there are DIM statements for database and dao.recordset


    something like
    Code:
    rst.close    
    Set rst= Nothing
    Cheers
    G

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •