?
Solved

Exported info doesn't show up on first page of header (word)

Posted on 2007-07-31
5
Medium Priority
?
242 Views
Last Modified: 2012-06-27
I have information exported from Access to a word document using the following code:

Private Sub cmdSaveAll_Click()

    Dim WordObj As Word.Application
    Dim db As DAO.Database
    Dim rsInspectionReport As Recordset
    Dim mystrSQL As String
    Dim MyFolderPath As String
    Dim InspReportName As String
    Dim strSaveNamePath As String
   
    Set WordObj = CreateObject("Word.application")
    Set db = CurrentDb
   
   
    mystrSQL = "SELECT * FROM qryInspectionReportAll WHERE [InspNumber] =" & [Forms]![frmInspectionReportMenu]![txtIDNumber]
    Set rsInspectionReport = db.OpenRecordset(mystrSQL)
   
    With WordObj
       
        .Visible = True
   
    rsInspectionReport.MoveLast
    rsInspectionReport.MoveFirst
       
    Do Until rsInspectionReport.EOF = True
   
        .Documents.Add ("\\rdc1\surety\surety reviews\suretyreview.dot")
       
        If Not IsNull(rsInspectionReport!InspNumber) Then
            .ActiveDocument.Bookmarks("InspNum").Range.InsertAfter rsInspectionReport![InspNumber]
        End If
       
        If Not IsNull(rsInspectionReport!Agency) Then
            .ActiveDocument.Bookmarks("Agency").Range.InsertAfter rsInspectionReport![Agency]
        End If
       
        If Not IsNull(rsInspectionReport!ExternalControlNumber) Then
            .ActiveDocument.Bookmarks("ControlNum").Range.InsertAfter rsInspectionReport![ExternalControlNumber]
        End If
       
        If Not IsNull(rsInspectionReport!ISDate) Then
            .ActiveDocument.Bookmarks("IStartDate").Range.InsertAfter rsInspectionReport![ISDate]
        End If
       
        If Not IsNull(rsInspectionReport!IEDate) Then
            .ActiveDocument.Bookmarks("IEndDate").Range.InsertAfter rsInspectionReport![IEDate]
        End If
       
        If Not IsNull(rsInspectionReport!InspectionTitle) Then
            .ActiveDocument.Bookmarks("ITitle").Range.InsertAfter rsInspectionReport![InspectionTitle]
        End If
       
        If Not IsNull(rsInspectionReport!FindingNum) Then
            .ActiveDocument.Bookmarks("FNumber").Range.InsertAfter rsInspectionReport![FindingNum]
        End If
       
        If Not IsNull(rsInspectionReport!FDate) Then
            .ActiveDocument.Bookmarks("FDate").Range.InsertAfter rsInspectionReport![FDate]
        End If
       
        If Not IsNull(rsInspectionReport!Reviewer) Then
            .ActiveDocument.Bookmarks("Reviewer").Range.InsertAfter rsInspectionReport![Reviewer]
        End If
       
        If Not IsNull(rsInspectionReport!AreaOfInterest) Then
            .ActiveDocument.Bookmarks("Interest").Range.InsertAfter rsInspectionReport![AreaOfInterest]
        End If
       
        If Not IsNull(rsInspectionReport!FactorsAffecting) Then
            .ActiveDocument.Bookmarks("Factors").Range.InsertAfter rsInspectionReport![FactorsAffecting]
        End If
       
        If Not IsNull(rsInspectionReport!Finding) Then
            .ActiveDocument.Bookmarks("Finding").Range.InsertAfter rsInspectionReport![Finding]
        End If
       
        If Not IsNull(rsInspectionReport!Reference) Then
            .ActiveDocument.Bookmarks("Reference").Range.InsertAfter rsInspectionReport![Reference]
        End If
       
        If Not IsNull(rsInspectionReport!Recommendation) Then
            .ActiveDocument.Bookmarks("Recommend").Range.InsertAfter rsInspectionReport![Recommendation]
        End If
       
        If Not IsNull(rsInspectionReport!Category) Then
            .ActiveDocument.Bookmarks("Category").Range.InsertAfter rsInspectionReport![Category]
        End If
       
        If Not IsNull(rsInspectionReport!Responsibility) Then
            .ActiveDocument.Bookmarks("Resp").Range.InsertAfter rsInspectionReport![Responsibility]
        End If
       
        If Not IsNull(rsInspectionReport!AcknowledgedBy) Then
            .ActiveDocument.Bookmarks("Acknow").Range.InsertAfter rsInspectionReport![AcknowledgedBy]
        End If
       
        If Not IsNull(rsInspectionReport!ClosedDate) Then
            .ActiveDocument.Bookmarks("CDate").Range.InsertAfter rsInspectionReport![ClosedDate]
        End If
       
        If Not IsNull(rsInspectionReport!MgrAssess) Then
            .ActiveDocument.Bookmarks("MgrAss").Range.InsertAfter rsInspectionReport![MgrAssess]
        End If
       
        If Not IsNull(rsInspectionReport!CorrActPlan) Then
            .ActiveDocument.Bookmarks("CorrAct").Range.InsertAfter rsInspectionReport![CorrActPlan]
        End If
       
        If Not IsNull(rsInspectionReport!Notes) Then
            .ActiveDocument.Bookmarks("Notes").Range.InsertAfter rsInspectionReport![Notes]
        End If
       
        If Not IsNull(rsInspectionReport!VerifiedBy) Then
            .ActiveDocument.Bookmarks("VerifiedBy").Range.InsertAfter rsInspectionReport![VerifiedBy]
        End If
       
        If Not IsNull(rsInspectionReport!ReportAge) Then
            .ActiveDocument.Bookmarks("ReportAge").Range.InsertAfter rsInspectionReport![ReportAge]
        End If
       
        If Not IsNull(rsInspectionReport!MyECD) Then
            .ActiveDocument.Bookmarks("FindECD").Range.InsertAfter rsInspectionReport![MyECD]
        End If
       
        MyFolderPath = "\\rdc1\surety\surety reviews\inspection reports\Inspection " & rsInspectionReport!InspNumber
       
        If Dir(MyFolderPath, vbDirectory) = "" Then
            MkDir MyFolderPath
        Else
       
        End If
       
       
        InspReportName = rsInspectionReport!FindingNum
        .ActiveDocument.SaveAs MyFolderPath & "\" & InspReportName
       
        .ActiveDocument.Close
               
        rsInspectionReport.MoveNext
        Loop
   
    End With
   
    rsInspectionReport.Close
   
    Set rsInspectionReport = Nothing
   
    WordObj.Quit
   
End Sub

Private Sub cmdSaveSingle_Click()


    Dim WordObj As Word.Application
    Dim db As DAO.Database
    Dim rsInspectionReport As Recordset
    Dim mystrSQL As String
    Dim MyFolderPath As String
    Dim InspReportName As String
    Dim strSaveNamePath As String
   
    Set WordObj = CreateObject("Word.application")
    Set db = CurrentDb
   
   
    mystrSQL = "SELECT * FROM qryInspectionReportAll WHERE [RecordID] =" & [Forms]![frmInspectionReportMenu]![txtIDNumber]
    Set rsInspectionReport = db.OpenRecordset(mystrSQL)
   
    With WordObj
       
        .Visible = True
   
    rsInspectionReport.MoveLast
    rsInspectionReport.MoveFirst
       
    Do Until rsInspectionReport.EOF = True
   
        .Documents.Add ("\\rdc1\surety\surety reviews\suretyreview.dot")
       
        If Not IsNull(rsInspectionReport!InspNumber) Then
            .ActiveDocument.Bookmarks("InspNum").Range.InsertAfter rsInspectionReport![InspNumber]
        End If
       
        If Not IsNull(rsInspectionReport!Agency) Then
            .ActiveDocument.Bookmarks("Agency").Range.InsertAfter rsInspectionReport![Agency]
        End If
       
        If Not IsNull(rsInspectionReport!ExternalControlNumber) Then
            .ActiveDocument.Bookmarks("ControlNum").Range.InsertAfter rsInspectionReport![ExternalControlNumber]
        End If
       
        If Not IsNull(rsInspectionReport!ISDate) Then
            .ActiveDocument.Bookmarks("IStartDate").Range.InsertAfter rsInspectionReport![ISDate]
        End If
       
        If Not IsNull(rsInspectionReport!IEDate) Then
            .ActiveDocument.Bookmarks("IEndDate").Range.InsertAfter rsInspectionReport![IEDate]
        End If
       
        If Not IsNull(rsInspectionReport!InspectionTitle) Then
            .ActiveDocument.Bookmarks("ITitle").Range.InsertAfter rsInspectionReport![InspectionTitle]
        End If
       
        If Not IsNull(rsInspectionReport!FindingNum) Then
            .ActiveDocument.Bookmarks("FNumber").Range.InsertAfter rsInspectionReport![FindingNum]
        End If
       
        If Not IsNull(rsInspectionReport!FDate) Then
            .ActiveDocument.Bookmarks("FDate").Range.InsertAfter rsInspectionReport![FDate]
        End If
       
        If Not IsNull(rsInspectionReport!Reviewer) Then
            .ActiveDocument.Bookmarks("Reviewer").Range.InsertAfter rsInspectionReport![Reviewer]
        End If
       
        If Not IsNull(rsInspectionReport!AreaOfInterest) Then
            .ActiveDocument.Bookmarks("Interest").Range.InsertAfter rsInspectionReport![AreaOfInterest]
        End If
       
        If Not IsNull(rsInspectionReport!FactorsAffecting) Then
            .ActiveDocument.Bookmarks("Factors").Range.InsertAfter rsInspectionReport![FactorsAffecting]
        End If
       
        If Not IsNull(rsInspectionReport!Finding) Then
            .ActiveDocument.Bookmarks("Finding").Range.InsertAfter rsInspectionReport![Finding]
        End If
       
        If Not IsNull(rsInspectionReport!Reference) Then
            .ActiveDocument.Bookmarks("Reference").Range.InsertAfter rsInspectionReport![Reference]
        End If
       
        If Not IsNull(rsInspectionReport!Recommendation) Then
            .ActiveDocument.Bookmarks("Recommend").Range.InsertAfter rsInspectionReport![Recommendation]
        End If
       
        If Not IsNull(rsInspectionReport!Category) Then
            .ActiveDocument.Bookmarks("Category").Range.InsertAfter rsInspectionReport![Category]
        End If
       
        If Not IsNull(rsInspectionReport!Responsibility) Then
            .ActiveDocument.Bookmarks("Resp").Range.InsertAfter rsInspectionReport![Responsibility]
        End If
       
        If Not IsNull(rsInspectionReport!AcknowledgedBy) Then
            .ActiveDocument.Bookmarks("Acknow").Range.InsertAfter rsInspectionReport![AcknowledgedBy]
        End If
       
        If Not IsNull(rsInspectionReport!ClosedDate) Then
            .ActiveDocument.Bookmarks("CDate").Range.InsertAfter rsInspectionReport![ClosedDate]
        End If
       
        If Not IsNull(rsInspectionReport!MgrAssess) Then
            .ActiveDocument.Bookmarks("MgrAss").Range.InsertAfter rsInspectionReport![MgrAssess]
        End If
       
        If Not IsNull(rsInspectionReport!CorrActPlan) Then
            .ActiveDocument.Bookmarks("CorrAct").Range.InsertAfter rsInspectionReport![CorrActPlan]
        End If
       
        If Not IsNull(rsInspectionReport!Notes) Then
            .ActiveDocument.Bookmarks("Notes").Range.InsertAfter rsInspectionReport![Notes]
        End If
       
        If Not IsNull(rsInspectionReport!VerifiedBy) Then
            .ActiveDocument.Bookmarks("VerifiedBy").Range.InsertAfter rsInspectionReport![VerifiedBy]
        End If
       
        If Not IsNull(rsInspectionReport!ReportAge) Then
            .ActiveDocument.Bookmarks("ReportAge").Range.InsertAfter rsInspectionReport![ReportAge]
        End If
       
        If Not IsNull(rsInspectionReport!MyECD) Then
            .ActiveDocument.Bookmarks("FindECD").Range.InsertAfter rsInspectionReport![MyECD]
        End If
       
        MyFolderPath = "\\rdc1\surety\surety reviews\inspection reports\Inspection " & rsInspectionReport!InspNumber
       
        If Dir(MyFolderPath, vbDirectory) = "" Then
            MkDir MyFolderPath
        Else
       
        End If
       
       
        InspReportName = rsInspectionReport!FindingNum
        .ActiveDocument.SaveAs MyFolderPath & "\" & InspReportName
       
        .ActiveDocument.Close
               
        rsInspectionReport.MoveNext
        Loop
   
    End With
   
    rsInspectionReport.Close
   
    Set rsInspectionReport = Nothing
   
    WordObj.Quit

End Sub

I am not sure if this is an existing problem or one that just started, but the information that is in the header of the word document doesn't show up on the first page.  If there is more than one page to the document, the information shows up on the following pages (in the header).

I don't think this is a problem with my code, but a setting in Word...I just don't know what the setting would be or where I would find it to change it.  Please help!

Lena
0
Comment
Question by:LenaWood
  • 3
  • 2
5 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 19605373
In the Word document, choose File/Page Setup. On the layout tab, make sure that you don't have 'Different first page' ticked.
0
 
LVL 10

Author Comment

by:LenaWood
ID: 19605421
The first page header is slightly different than the second page.  The second page header says continued on it so that it shows it is not the first page...I need there to be a page header on each page...the first page header is different than pages 2-??

Any other suggestions?
Lena
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 19605731
I am a bit confused. Is the first page different , but in the wrong way? If so, what is actually wrong with the page.
Note that you can also differentiate between what headers display by using page numbers or field calculations that depend on page numbers

{If {Page} < {Numpages} "Continued"}
0
 
LVL 10

Author Comment

by:LenaWood
ID: 19609488
I figured out why this wasn't working.

When I made it so that my headers were not the same, I didn't take into account that I couldn't have two bookmarks named the same in two different places.  When I wanted the headers to be different, then the table on my first page header no longer had working bookmarks.

I am giving them new bookmark names, changing my code in the database and everything seems to be working great!

I will award the points to you for heading me in the right direction about the header button being chekced.  Without that information I wouldn't have realized that I actually had two different tables and needed to create new book marks.

Thanks for giving me some insight!
Lena
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 19613301
Thanks. I was wondering how you had designed it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…
Suggested Courses

864 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