Hi All
I am working on a project in which we have to print word documents of each policy so if the policystatus is "Live" then we have to print a document with header(barcode) on it. If the policystatus is "Cancelled" then print off a different sheet. The following code is written that works fine but the only thing is the print order is not right. documents jump up the queue. The documents spooled last get printed off first sometimes. Any other alternator to write the following code so that sheets prints off in order:
I know its a very long code but to explain whats happening I had to post it. If we can write the code in a way so that printing sheets come up in the order. Any help would be much appreciated.
Thanks
I am working on a project in which we have to print word documents of each policy so if the policystatus is "Live" then we have to print a document with header(barcode) on it. If the policystatus is "Cancelled" then print off a different sheet. The following code is written that works fine but the only thing is the print order is not right. documents jump up the queue. The documents spooled last get printed off first sometimes. Any other alternator to write the following code so that sheets prints off in order:
Code:
Public LivePol As Variant
Public CancelPol As Variant
Sub GetDataFromDataBase()
LivePol = 0
CancelPol = 0
Dim myDataBase As Database
Dim myActiveRecord As Variant
Set myDataBase = OpenDatabase("J:\backfile.mdb")
Set myActiveRecord = myDataBase.OpenRecordset("select * from MasterTBL where PolicyNumber>=" & TxtFPolNo & " and PolicyNumber<=" & TxtLPolNo & " order by PolicyNumber")
If myActiveRecord.BOF = True And myActiveRecord.EOF = True Then
MsgBox "Record not found"
End If
Do While (Not myActiveRecord.EOF)
If myActiveRecord.Fields("PolicyStatus") = "Live" Then
Call FuncBarcode(myActiveRecord.Fields("PolicyNumber").Value, myActiveRecord.Fields("LastName").Value, myActiveRecord.Fields("EDA").Value)
LivePol = LivePol + 1
Else
Call FuncLapsed(myActiveRecord.Fields("PolicyNumber").Value, myActiveRecord.Fields("LastName").Value, myActiveRecord.Fields("PolicyStatus").Value)
CancelPol = CancelPol + 1
End If
myActiveRecord.MoveNext
Loop
Call FuncReceipt(TxtFPolNo, TxtLPolNo, LivePol, CancelPol)
myActiveRecord.Close
myDataBase.Close
End Sub
Code:
Sub FuncLapsed(a As Variant, b As Variant, c As Variant)
Dim m_docCurrent As Word.Document
Set m_docCurrent = Word.Documents.Open("J:\Lapsed Policy.docx", , False)
m_docCurrent.Activate
With Selection
'Reference type
Font_Heading
.TypeText vbTab & "Backfile Project - Lapsed Policy"
.TypeParagraph
.TypeParagraph
Font_Normal
.TypeParagraph
.TypeText vbTab & "Policy Number : " & a
'######################################################################
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Policy Surname : " & b
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Date : " & Format(Date, "dd/mm/yyyy")
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Time : " & Format(Now, "hh:mm:ss")
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Username : " & Environ("username")
'######################################################################
.TypeParagraph
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Status : " & c
.TypeParagraph
.TypeParagraph
Font_Text
.TypeText "Note : "
.TypeText "This policy has lapsed. Do not scan. "
.TypeText "Staple this page to the front of the file."
.TypeText " Put the file into designated Lapsedfile Box."
With ActiveDocument.Sections(1)
.Footers(wdHeaderFooterPrimary).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
.Footers(wdHeaderFooterPrimary).Range.Text = Trim$(cpStorage)
.Footers(wdHeaderFooterPrimary).Range.Font.Bold = True
.Footers(wdHeaderFooterPrimary).Range.Font.Name = "Arial"
.Footers(wdHeaderFooterPrimary).Range.Font.Size = 28
End With
End With
m_docCurrent.PrintOut
m_docCurrent.Close savechanges:=False
CreateIndexPage = True
End Sub
Code:
Sub FuncBarcode(a As Variant, b As String, c As Date)
Dim vsRefType As String
Dim vsRef1 As Variant
Dim vsAddRefData As String
Dim vdtDocDate As Date
Dim vsSaveBarcodeFilename As Boolean
'######################################################################
'On Error GoTo CreateIndexPage_ERROR
'Barcode variables
vsRefType = "Policy Number"
vsRef1 = a
vsAddRefData = b
vdtDocDate = c
vsSaveBarcodeFilename = True
'Barcode constants
Const CODE_FOR_EVENT_TYPE As String = "E00008"
Const STR_EVENT_GENERAL_FILE As String = "General File"
Const CODE_FOR_DOC_TYPE As String = "D00013"
Const STR_DOC_CORR_OUT As String = "General"
Dim m_docCurrent As Word.Document
Set m_docCurrent = Word.Documents.Open("J:\WilliamsLea-AIMM\Unit Rate MI\104k Policy Backfile Project\Barcode\Barcode.doc", , True)
m_docCurrent.Activate
With Selection
.TypeText vbTab & "Reference Type : " & vsRefType
.TypeParagraph
.TypeParagraph
.TypeText vbTab
.TypeText AllowableCharacters("T00001", True)
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Reference : "
.Font.Size = 22
.TypeText vsRef1
.Font.Size = 12
.TypeParagraph
.TypeParagraph
.TypeText vbTab
Font_Barcode
'#####BARCODE PAGE VERSION#############################################
' .TypeText PrepForBarcode("R" & vsRef1 & IIf(Len(vsRef2) > 0, "/" & vsRef2, ""))
'#####COBRA PAGE VERSION###############################################
' .TypeText AllowableCharacters("R" & vsRef1 & IIf(Len(vsRef2) > 0, "/" & vsRef2, ""), True)
.TypeText AllowableCharacters("R" & vsRef1, True)
'######################################################################
Font_Normal
'Any additional reference date
If Len(vsAddRefData) > 0 Then
Font_Normal
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Additional data : " & vsAddRefData
.TypeParagraph
.TypeParagraph
.TypeText vbTab
Font_Barcode
'#####BARCODE PAGE VERSION#############################################
' .TypeText PrepForBarcode("A" & vsAddRefData)
'#####COBRA PAGE VERSION###############################################
.TypeText AllowableCharacters("A" & vsAddRefData, True)
'######################################################################
End If
'The event type
Font_Normal
.TypeParagraph
.TypeParagraph
'#####BARCODE PAGE VERSION#############################################
' .TypeText vbTab & "Event Type : " & vsEventType
'#####COBRA PAGE VERSION###############################################
.TypeText vbTab & "Event Type : " & STR_EVENT_GENERAL_FILE
'######################################################################
.TypeParagraph
.TypeParagraph
.TypeText vbTab
Font_Barcode
.TypeParagraph
.TypeParagraph
.TypeText vbTab & "Document Date : " & Format(vdtDocDate, "DD/MM/YYYY")
.TypeText vbTab
.TypeParagraph
.TypeParagraph
.TypeText "Created by : " & Environ("username")
.TypeParagraph
.TypeParagraph
.TypeText vbTab
With ActiveDocument.Sections(1)
.Footers(wdHeaderFooterPrimary).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
.Footers(wdHeaderFooterPrimary).Range.Text = Trim$(cpStorage)
.Footers(wdHeaderFooterPrimary).Range.Font.Bold = True
.Footers(wdHeaderFooterPrimary).Range.Font.Name = "Arial"
.Footers(wdHeaderFooterPrimary).Range.Font.Size = 28
End With
End With
m_docCurrent.PrintOut
m_docCurrent.Close savechanges:=False
End SubI know its a very long code but to explain whats happening I had to post it. If we can write the code in a way so that printing sheets come up in the order. Any help would be much appreciated.
Thanks
