Link to home
Start Free TrialLog in
Avatar of LenaWood
LenaWood

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LenaWood
LenaWood

ASKER

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
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image
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"}
Avatar of LenaWood
LenaWood

ASKER

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
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image
Thanks. I was wondering how you had designed it.