?
Solved

Print different word documents as report (during recordset loop)

Posted on 2006-06-04
32
Medium Priority
?
351 Views
Last Modified: 2006-11-18
I have a looping recordset that prints a number of reports, depending on whether or not a value (my engineer number)exists in the recordset -

Dim rs As DAO.Recordset
dim i as integer

Set rs = CurrentDb.OpenRecordset("Query48")
If rs.EOF And rs.BOF Then
End If

rs.MoveFirst
While Not rs.EOF
  DoCmd.OpenReport "FrontSheet", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "rptPayingIn", acViewNormal, , "[Engineer number]=" & rs![Engineer number]

  'Print multiple times, based on value of SheetCount from recordset
  for i = 1 to rs!SheetCount
      DoCmd.OpenReport "JobSheets", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  next i

  DoCmd.OpenReport "TASR", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_VAT(type1 and (type2 not ILO))_1", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_VAT type2 ILO_4", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_mrktgcommsCC", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_NON_VAT(type1 and (type2 not ILO))_2", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoEvents
  rs.MoveNext
Wend


I've been asked to provide functionality so that users can insert a word document (or any document!) during the above print run.  The document might depend on the engineer type - I have 3 types of engineer.  Currently I've amended the recordset to include a true/false field for engineer type A, B and C.  

If the recordset has engineer type A set to true, it should print a document once from <path to doc> then move on.

Is this easy to include?  I think I would need 3 document types to match the engineer types (it could be the same document, just called something different).

Hope this is clear!

Lapchien
0
Comment
Question by:Lapchien
  • 16
  • 16
32 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16827776
Hi Lapchien

do these docs u want to print already exist? how do u determine the path of the file to print

if u have an engineer type, and document printing is based on that, u can add this in

select case  rs!EngineerType     'this is a field in the table
    case "A"
       'do whatever
    case 'B'
       'do whatever
    case 'C'
       'do whatever
    case else    'unknown type
end select
0
 

Author Comment

by:Lapchien
ID: 16827937
The trouble is that I don't want to print these external docs every week - only 'on demand' - my recordset table contains 3 fields called 'InsertA' and 'InsertB' and InsertC' for each EngineerNumber.  Users can update these 3 fields themselves from a form.

If EngineerNumber 1 has field InsertA set to true, then document A should be printed (not document B or C).

If EngineerNumber 1 has InsertA set to false, then no external document would be printed.

The path to the document would be in the code - or I could store it in the table (in UNC format).

All of this needs to be called from within the above loop.



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828007
why dont u create a table

engineer number
print status

and a simple form

the users can then decide when they want to print, by setting values here

then before you do your loop
read the values, as its only a small table 3 values
u can store the values in 3 different variables or an array

then use these variables in your loop
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:Lapchien
ID: 16828048
Sure, I see where you are going, thats just I was just experimenting with!  I have a table with 2 fields, called as you suggest EngineerNumber and PrintStatus.  Table is called tblPrintReps.

EngineerNumber is the engineer number ID, print status will be set to A or B or C.

The user could update the print status from the form.

Can you help with a bit of code?  Not too sure on how to do a lookup from within a loop...

Something like -

Within the recordset - lookup EngineerNumber from tblPrintReps - if PrintStatus set to A then
'print this document
Within the recordset - lookup EngineerNumber from tblPrintReps - if PrintStatus set to B then
'print this document
Within the recordset - lookup EngineerNumber from tblPrintReps - if PrintStatus set to C then
'print this document

How do I handle if no print status is set?



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828065
one of two ways

easiest way is

bPrintA = DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'A'")
bPrintB = DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'B'")
bPrintC = DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'C'")

or

bPrintA = False
bPrintB = False
bPrintC = False
dim rs as dao.recordset
set rs = currentdb.openrecordset("SELECT * FROM tblReps")
do while not rs.eof
    select case rs!EngineerNumber
        case 'A'
            bPrintA = True
        case 'B'
             bPrintB = True
         case 'C'
             bPrintC = True
     end select
     rs.movenext
loop
rs.close
set rs=nothing


Now all u do is use the variables


if bPrintA = True then 'print whatever

etc


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828328
u should assume false if no print status is set
safety first!!!


just use the variables inside your loop and print whatever u need to


in that DLOOKUP, u need to check for null
or in table definition, give it a default value of False

0
 

Author Comment

by:Lapchien
ID: 16828346
Ok, so this should work..:?

Dim rs As DAO.Recordset
dim i as integer

Set rs = CurrentDb.OpenRecordset("Query48")
If rs.EOF And rs.BOF Then
End If

bPrintA = DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'A'")
bPrintB = DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'B'")
bPrintC = DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'C'")

rs.MoveFirst
While Not rs.EOF
  DoCmd.OpenReport "FrontSheet", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
 
 if bPrintA = True then 'print whatever
 if bPrintB = True then 'print whatever
 if bPrintC = True then 'print whatever


  DoCmd.OpenReport "rptPayingIn", acViewNormal, , "[Engineer number]=" & rs![Engineer number]

  'Print multiple times, based on value of SheetCount from recordset
  for i = 1 to rs!SheetCount
      DoCmd.OpenReport "JobSheets", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  next i

  DoCmd.OpenReport "TASR", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_VAT(type1 and (type2 not ILO))_1", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_VAT type2 ILO_4", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_mrktgcommsCC", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoCmd.OpenReport "qry_NON_VAT(type1 and (type2 not ILO))_2", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
  DoEvents
  rs.MoveNext
Wend
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16828566
Looks fine, u just need to ensure that DLOOKUP does not return NULL unless bPrintA is defined as variant.

if default value is not false then try this

dim bPrintA, bPrintB, bPrintC as boolean
 
if isnull(DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'A'")) = false then bPrintA = true else bPrintA = false

if isnull(DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'B'")) = false then bPrintB = true else bPrintB = false

if isnull(DLOOKUP("PrintStatus","tblPrintReps","EngineerNumber = 'C'")) = false then bPrintC = true else bPrintC = false
0
 

Author Comment

by:Lapchien
ID: 16831061
Hmmm struggling with this!

Rocki, could you go through the A B C bits?  I can't see how the table fields are defined, are the true/false, or text types?  If true or false, why A B or C?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16831545
Hi Lapchien, I realised the mistake on my part,

try this, Ive commented so hopefully it helps u to understand



    Dim rs As DAO.Recordset
    Dim i As Integer
   
    Dim rv
    Dim bPrintA, bPrintB, bPrintC As Boolean
   
   
    Set rs = CurrentDb.OpenRecordset("Query48")
    If rs.EOF And rs.BOF Then
        MsgBox "No Records"
        Exit Sub
    End If
   
    'Assume no prints required i.e. set to false
    bPrintA = False
    bPrintB = False
    bPrintC = False
   
    rv = DLookup("PrintStatus", "tblPrintReps", "EngineerNumber = 'A'")
    If IsNull(x) = False Then bPrintA = rv
   
    rv = DLookup("PrintStatus", "tblPrintReps", "EngineerNumber = 'B'")
    If IsNull(x) = False Then bPrintB = rv
   
    rv = DLookup("PrintStatus", "tblPrintReps", "EngineerNumber = 'C'")
    If IsNull(x) = False Then bPrintC = rv
   
    'DIAGNOSTICS = look in immediate window
    If bPrintA = True Then Debug.Print "Printing A" Else Debug.Print "No Print A"
    If bPrintB = True Then Debug.Print "Printing B" Else Debug.Print "No Print B"
    If bPrintC = True Then Debug.Print "Printing C" Else Debug.Print "No Print C"
   
    rs.MoveFirst
    While Not rs.EOF
        DoCmd.OpenReport "FrontSheet", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        If bPrintA = True Then 'print whatever
        If bPrintB = True Then 'print whatever
        If bPrintC = True Then 'print whatever
       
        DoCmd.OpenReport "rptPayingIn", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        'Print multiple times, based on value of SheetCount from recordset
        For i = 1 To rs!SheetCount
            DoCmd.OpenReport "JobSheets", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        Next i
       
        DoCmd.OpenReport "TASR", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT(type1 and (type2 not ILO))_1", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT type2 ILO_4", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_mrktgcommsCC", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_NON_VAT(type1 and (type2 not ILO))_2", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        DoEvents
        rs.MoveNext
    Wend
   
    'Close down
    rs.Close
    Set rs = Nothing
0
 

Author Comment

by:Lapchien
ID: 16831670
gives me a 'Wend without while' when I compile it
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16832026
this is because of your code

        If bPrintA = True Then 'print whatever
        If bPrintB = True Then 'print whatever
        If bPrintC = True Then 'print whatever


It messes it up

Either u need to complete the code or remove them


0
 

Author Comment

by:Lapchien
ID: 16832656
did that... now get Invalid use of null!

Rocki can I just check I have this correct?

I have a table tblPrintReps, and 2 fields EngineerNumber (set to number) and PrintStatus (set to text).

I have 2500 engineers, and they are all of either type A, type B or type C (employed, sub-contract or franchisee).

Some weeks I might want to send a letter within the print run to just engineer types A and B, other weeks just to engineer type C or all engineer types, other weeks no doucument at all.

My users are able to update the table tblPrintReps so that PrintStatus is populate with either an A, B or C.

To test it I'm using this in the recordset:

        If bPrintA = True Then msgbox "A selected"
        If bPrintB = True Then msgbox "B selected"
        If bPrintC = True Then msgbox "C selected"

But still getting the Invalid use of null error...
0
 

Author Comment

by:Lapchien
ID: 16832664
IuoN error is on:

If IsNull(X) = False Then bPrintC = rv

                                     ^^^^^^^

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16833493
PrintStatus has to be set to Boolean (yes/no field)

and that isNull(x)

sorry again, I renamed the variable to rv (my usual name for return value)
its better than using x

If IsNull(x) = False Then bPrintA = rv

should be

If IsNull(rv) = False Then bPrintA = rv


So sorry about that


just to note
If u run this test with your several hundred records, u gonna have a lot of msgboxes appearing

0
 

Author Comment

by:Lapchien
ID: 16833722
Sure, I just stripped out the engineers list to half a dozen for testing.  I'll let you know what happens!
0
 

Author Comment

by:Lapchien
ID: 16835963
Rocki, on this line -

rv = DLookup("PrintStatus", "tblPrintReps", "EngineerNumber = 'A'")

Should EngineerNumber be set to A (or B or C)?

If so - that's wrong, I've probably not made it clear.  The Engineer number is a number from 1 to 2500 (and growing).  Within these there are 3 groups of engineers, A, B, C...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16836483
Some confusion somewhere then

Rereading, let me try understand

there are 3 external documents, we call A, B, C

The user picks one of these?

If so, what we want is

tblPrintReps
EngineerNumber
DocumentNo  (int)

valid values for DocumentNo is 0 (none), 1 (docA), 2 (docB), 3 (docC)

default value is 0
on your where user can select via an option group, 4 radio buttons in it, values 0 to 3

does this make sense now

Am I on the right path?

Logic to code in loop is very simple, the difficulty is for me to understand what exactly u want :(
Not having a good day so far

code inside loop

iDocNo = DLOOKUP("DocumentNo","tblPrintReps","EngineerNumber = " & rs!EngineerNumber)

select case iDocNo
    case 1
         msgbox "We Print Doc A"
    case 2
          msgbox "We Print Doc B"
     case 3
           msgbox "We Print Doc C"
     case else
           msgbox "No prints"
end select

0
 

Author Comment

by:Lapchien
ID: 16844288
Rocki - I just can't get it to work...  I've tried several things, so maybe look at it a different way?

Within the table I use as my recordset (Query48) there are 3 fields called 'InsertILO' and 'InsertPAYE' and 'InsertSUB'.

These are all true/false fields, default false.

The main recordset loop is based on the field 'Engineer number', and that exists in all my reports, called during the loop.

Could we not just add code to say:

<during the recordset loop, as well as looking at the engineer number, in that particular record also look at 'InsertILO' and 'InsertPAYE' and 'InsertSUB'.>

<If 'InsertILO' = true then 'print whatever>
<If 'InsertPAYE' = true then 'print whatever>
<If 'InsertSUB' = true then 'print whatever>

?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16851309
Firstly, how do you define what extra document gets printed based on the Engineer?
I assume there is a form.
Where is this underlying data held?

If this was held in tblPrintReps

PrintRepID
EngineerNumber
DocumentNo  this holds,0,1,2,3   0=nodoc, 1=DocA, 2=DocB, 3=DocC


Then what we can do in the code is something like this

First add this enum in some module somewhere

Public Enum DocumentNoTypes
    NoDoc = 0
    DocA = 1
    DocB = 2
    DocC = 3
End Enum


Now back to your looping code



    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim rv
   
   
    Set rs = CurrentDb.OpenRecordset("Query48")
    If rs.EOF And rs.BOF Then
        MsgBox "No Records"
        Exit Sub
    End If
   
    rs.MoveFirst
    While Not rs.EOF
        DoCmd.OpenReport "FrontSheet", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        'We perform a lookup on the tblPrintReps table to determine what to print
        rv = DLookup("DocumentNo", "tblPrintReps", "EngineerNumber = " & rs!EngineerNumber)

        'Note if engineernumber is a string, we do this
        rv = DLookup("DocumentNo", "tblPrintReps", "EngineerNumber = '" & rs!EngineerNumber & "'")

        'rv now holds the document print status, now check it and see what we need to do    
        Select Case rv
            Case DocumentNoTypes.DocA
                MsgBox "We Print Doc A"

            Case DocumentNoTypes.DocB
                MsgBox "We Print Doc B"

            Case DocumentNoTypes.DocC
                MsgBox "We Print Doc C"

        End Select

        'Continue with rest of code        
        DoCmd.OpenReport "rptPayingIn", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        'Print multiple times, based on value of SheetCount from recordset
        For i = 1 To rs!SheetCount
            DoCmd.OpenReport "JobSheets", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        Next i
       
        DoCmd.OpenReport "TASR", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT(type1 and (type2 not ILO))_1", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT type2 ILO_4", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_mrktgcommsCC", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_NON_VAT(type1 and (type2 not ILO))_2", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        DoEvents
        rs.MoveNext
    Wend
   
    'Close down
    rs.Close
    Set rs = Nothing




0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16851350
What are InsertILO, InsertPAYE and InsertSUB?

are these the fields that indicate what extra document to display?

If so, then this is what we should be using then
What is the logic when it comes to printing
Do we print all those set to True
or do we print one if True but not the other? If we just do one but not the other, if two are set to True, which one do u print?



so as long as those 3 fields are in Query48 (may I suggest you rename this to something more meaningful e.g. qryDocsToPrint or something along those lines)


    Dim rs As DAO.Recordset
    Dim i As Integer
    Dim rv
   
   
    Set rs = CurrentDb.OpenRecordset("Query48")
    If rs.EOF And rs.BOF Then
        MsgBox "No Records"
        Exit Sub
    End If
   
    rs.MoveFirst
    While Not rs.EOF
        DoCmd.OpenReport "FrontSheet", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        DoCmd.OpenReport "rptPayingIn", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        'Print multiple times, based on value of SheetCount from recordset
        For i = 1 To rs!SheetCount
            DoCmd.OpenReport "JobSheets", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        Next i
       
        DoCmd.OpenReport "TASR", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT(type1 and (type2 not ILO))_1", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT type2 ILO_4", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_mrktgcommsCC", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_NON_VAT(type1 and (type2 not ILO))_2", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        'Print additional documents
        If rs!InsertILO Then
            MsgBox "ILO is true. Lets print something"
        End If
       
        If rs!InsertPAYE Then
            MsgBox "PAYE is true. Lets print something"
        End If
       
        If rs!InsertSUB Then
            MsgBox "SUB is true. Lets print something"
        End If
       
        DoEvents
        rs.MoveNext
    Wend
   
    'Close down
    rs.Close
    Set rs = Nothing
0
 

Author Comment

by:Lapchien
ID: 16851429
The requirement is to be able to insert word document/s, at will, during a batched print run.  Some weeks there won't be a need to insert any additional documents, some weeks we might need to print documents only for 1 group of engineers, some weeks all.

InsertILO, InsertPAYE and InsertSUB are all true/false fields on the table the underlies Query48.

I'll run through the code...


0
 

Author Comment

by:Lapchien
ID: 16851545
Seems to be skipping over:

       'Print additional documents
        If rs!InsertILO Then
            MsgBox "ILO is true. Lets print something"
        End If
       
        If rs!InsertPAYE Then
            MsgBox "PAYE is true. Lets print something"
        End If
       
        If rs!InsertSUB Then
            MsgBox "SUB is true. Lets print something"
        End If



Only 1 of InsertILO / InsertSUB / InsertPAYE would be set to true (for each engineer).
0
 

Author Comment

by:Lapchien
ID: 16851899
Ok, this is what I have (and it seems to work!):

On Error Resume Next

Dim rs As DAO.Recordset
    Dim i As Integer
    Dim rv
    Dim stILOdoc, stPAYEdoc, stSUBdoc
    Dim oAppi As Object
    Dim oAppp As Object
    Dim oApps As Object
   
    Set rs = CurrentDb.OpenRecordset("Query48")
    If rs.EOF And rs.BOF Then
        MsgBox "Nothing top print - did you run PREP first?", vbCritical, "Engineer Statements"
        Exit Sub
    End If
   
    rs.MoveFirst
    While Not rs.EOF
        DoCmd.OpenReport "FrontSheet", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "rptPayingIn", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        For i = 1 To rs!CountSheets
            DoCmd.OpenReport "JobSheets", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        Next i
       
        DoCmd.OpenReport "TASR", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT(type1 and (type2 not ILO))_1", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_VAT type2 ILO_4", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_mrktgcommsCC", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
        DoCmd.OpenReport "qry_NON_VAT(type1 and (type2 not ILO))_2", acViewNormal, , "[Engineer number]=" & rs![Engineer number]
       
        If rs!InsertILO Then
    Set oAppi = CreateObject("Word.Application")
    oAppi.Visible = True
    oAppi.Documents.Open ("C:\ilo.doc")
    oAppi.Options.PrintBackground = False
    oAppi.ActiveDocument.PrintOut
    oAppi.Quit

        End If
       
        If rs!InsertPAYE Then
            Set oAppp = CreateObject("Word.Application")
    oAppp.Visible = True
    oAppp.Documents.Open ("C:\paye.doc")
    oAppp.Options.PrintBackground = False
    oAppp.ActiveDocument.PrintOut
    oAppp.Quit
   
        End If
       
        If rs!InsertSUB Then
       
            Set oApps = CreateObject("Word.Application")
    oApps.Visible = True
    oApps.Documents.Open ("C:\sub.doc")
    oApps.Options.PrintBackground = False
    oApps.ActiveDocument.PrintOut
    oApps.Quit
   
        End If
       
        DoEvents
        rs.MoveNext
    Wend
   
    rs.Close
    Set rs = Nothing

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16855808
well thats an alternative way to print a document.
Can I suggest u try something else

Make use of shellexecute api

check out the code here, add it into a module

http://www.vbaccelerator.com/codelib/shell/shellex.htm

This is the func I used

If u use the code specified in the link

u can print docs like this

e.g.
If rs!InsertSUB Then
    ShellEx "C:\sub.doc", , , , "print", Me.hWnd
end if


ShellExecute is good in that give it any file, it opens it according to what is the registered file in windows
0
 

Author Comment

by:Lapchien
ID: 16857349
Sure, that looks better!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16858914
I use ShellExecute myself and I know how good it is, which is why I recommend using that
0
 

Author Comment

by:Lapchien
ID: 16876861
Rocki, the above works very well, except that... if one of the word documents is over 6 pages in length, I get an error '...doc is already open, do you want to... open a read only....'

I have to click 'Read only' and then the code loops.  This is okay - as long as I'm at the PC to click Ok.  

On word documents of less than 6 pages it isn't an issue.

Can I put a pause of a second or 2 after:

ShellEx "C:\sub.doc", , , , "print", Me.hWnd

?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16881455
Thats interesting, perhaps it is,

U could try a pause, see if that makes a difference

the best way to do this is the use the API call

add this to a module


Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)


Now call it, e.g.

Sleep(2000)

that sleeps for 2 secs, remember value passed in is millisecs
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16881456
Before u try the sleep code

try this first

put this code, where u feel u need to add the delay

DoEvents

0
 

Author Comment

by:Lapchien
ID: 16881756
cheers, will try.  

what does the doevents do?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16881783
DoEvents is used so it yields execution so that the operating system can process other events
so perhaps the cpu can process the print? give it a try
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question