Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Using Graphs in Access Word Merge

Posted on 2011-02-15
8
517 Views
Last Modified: 2012-05-11
I'm new to Access but I'm using Access 2010 data to generate some documents using the Word Merge feature. I am able to get data from queries in Access into my document just fine.

Now I want to generate some graphs based on this data for the Word Merge, but I'm having trouble figuring out how I'd do that. Do I generate the graph in an Access form then use the Word Merge to import it? Or do I try to create a graph in Word and use Access as the source? I've tried both - and couldn't figure it out.
0
Comment
Question by:DVation191
  • 3
  • 3
  • 2
8 Comments
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 125 total points
ID: 34899939
You export data, not objects like graphs.  There are various possibilities here.  I wouldn't use Word, but save reports with the graphs (or PivotCharts) to PDF format.  Maybe you could just import the data to a Word doc, and append a PDF doc with the graph.
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 375 total points
ID: 34900105
Helen, pardon me for jumping in ...

A method I have used with success in the past is to create the graph in Access and then save it to a image file. You can then insert the image into your Word document.

Here is where I first learn how a while back: Email/Export Charts from Access
*** Note Even though a lot of the examples are in Access 97, most will work fine in Access 2000-2010.

There is also: Creating Charts with VBA in Word 2010
0
 
LVL 20

Author Comment

by:DVation191
ID: 34900410
I'll look into these suggestions but just wanted to note something before anyone else posted.

The challenge here is that I'm generating  hundreds of documents with the word merge function based on a table of information in Access. Some of the data in those records would best be displayed as a chart or graph and not as a raw number. I'm not sure any of the proposed solutions address that yet, but I'll certainly check.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 375 total points
ID: 34901056
That is exactly why I put charts in my reports in Access.  I do charting a lot in Access.

I do agree with Helen that you should probably look into using a report.  The Access RTF control can do lots of advanced formatting.  I use an Access report to generate letter all the time.

Really curious:
Since you are using Access 2010 which has a built-in RTF support, is there some reason you not using an Access report?  
0
 
LVL 20

Author Comment

by:DVation191
ID: 34906258
"Since you are using Access 2010 which has a built-in RTF support, is there some reason you not using an Access report"

Being new to Access, I did not realize that Access had the "word merge" functionality built in. I was under the impression that a report was just a single page document. I'll look into this as well.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 375 total points
ID: 34907852
Maybe this example will give you some insight on working with Access reports:

Batch Printing and Save As PDF

This example could easily be updated to send the PDF as an email.

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34938644
Here is some code that creates a filtered report for each record in a recordset, and saves it as a PDF and then emails it:
Public Sub SendInterventionEmails()
'Created by Helen Feddema 10-Jan-2010
'Last modified by Helen Feddema 10-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   Dim rstIntervention As DAO.Recordset
   Dim lngCount As Long
   Dim lngID As Long
   Dim rpt As Access.Report
   Dim strFileName As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strTitle As String
   Dim strCurrentPath As String
   Dim strFileNameAndPath As String
   Dim strEmailSource As String
   
   strEmailSource = "qryInterventionEmail"
   strRecordSource = "qryMissingAssignments"
   strQuery = "qryMissingAssignmentsSingleStudent"
   Set dbs = CurrentDb
   Set rstIntervention = dbs.OpenRecordset(strEmailSource)
   strCurrentPath = Application.CurrentProject.Path & "\"
   
   'Use path selected with SelectFolder procedure
   'strCurrentPath = SelectFolder()

   With rstIntervention
      Do While Not .EOF
         lngID = ![StID]
         Debug.Print "Processing Student ID " & lngID
         strFileName = "Intervention Report for " & ![StFirst] _
            & " " & ![StLast] & ".pdf"
         strFileNameAndPath = strCurrentPath & strFileName
         
         'Create filtered query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[StID] = " & Chr(39) & lngID & Chr(39) & ";"
         Debug.Print "SQL for " & strQuery & ": " & strSQL
         lngCount = CreateAndTestQuery(strQuery, strSQL)
         Debug.Print "No. of items found: " & lngCount
         If lngCount = 0 Then
            GoTo NextStudent
         End If
      
         'Open report with filtered query record source
         strReport = "rptMissingAssignmentsNew"
         DoCmd.OpenReport ReportName:=strReport, _
            View:=acViewPreview, _
            windowmode:=acWindowNormal
         Set rpt = Reports(strReport)
         DoCmd.OutputTo objecttype:=acOutputReport, _
            objectname:=strReport, _
            outputformat:=acFormatPDF, _
            outputfile:=strFileNameAndPath
         
         'Create email
         Set itm = appOutlook.CreateItem(olMailItem)
         itm.Subject = "MISSING WORK"
         itm.Body = "The attached file lists your missing assignments"
         itm.To = ![Email]
         itm.Attachments.Add Source:=strFileNameAndPath, _
            Type:=olByValue
         
         'For editing before sending
         itm.Display
         
         'For sending automatically
         'itm.Send
         DoCmd.Close objecttype:=acReport, _
            objectname:=strReport, _
            Save:=acSaveNo

NextStudent:
         .MoveNext
      Loop
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SendInterventionEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified by Helen Feddema 10-Jan-2010

On Error Resume Next
   
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34938649
In order to automate the process of putting graphs into the Word docs as images, or creating them as separate PDF files, you will need to somehow standardize them, perhaps working in batches, where each batch requires a graph of a certain type, which would have a corresponding report.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

856 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