Print different word documents as report (during recordset loop)

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

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.

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

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?

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

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

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

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

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?

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

gives me a 'Wend without while' when I compile it

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

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...

IuoN error is on:

If IsNull(X) = False Then bPrintC = rv

                                     ^^^^^^^

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

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

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...

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

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>

?

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

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...

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).

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

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

Sure, that looks better!

I use ShellExecute myself and I know how good it is, which is why I recommend using that

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

?

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

Before u try the sleep code

try this first

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

DoEvents

cheers, will try.  

what does the doevents do?

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