Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have a database of chldren and their parents, who have been participants in a research study. At the conclusion, they will undergo an assessment, which results in a numerical score.
    The project wants to write to the parents and tell them how the kids went, but they don't want to actually reveal the numerical score. Instead they want to display it in a manner, like I have shown below.
    (This is just a rough prototype and the score would not be visible on the final product.)

    [attachment=88166:results.gif]

    Normally I would write to the parents via a Mail Merge. So my question is: Can I put a diagram like that above in a merge document, and have the position of the marker determined by the score.

    I could do the letter as an Access report. In that case I would use the following code in the Format Event of the Detail section of the report. (The screen shot above was created that way.)

    Code:
    Dim lngLeft As Long
    Dim lngwidth As Long
    lngwidth = Me.BoxRange.Width
    Me.LabelMarker.Left = Me.BoxRange.Left + CLng(lngwidth * Me.score / 100 - Me.LabelMarker.Width / 2)
    I presume I could use similar code in Word, but I don't know where to put it. The code would need to execute for each merge record.
    Attached Images Attached Images
    Regards
    John



  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts
    There are a number of application-level events relating to MailMerge (attached illustration is from Word 2002). I haven't done any programming around merge, but these look like a good place to start.

    Gary

    [attachment=88169:AppLevelMailMergeEvents.JPG]
    Attached Images Attached Images

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by John Hutchison View Post
    Can I put a diagram like that above in a merge document, and have the position of the marker determined by the score.
    Here's a thought: Is it possible to merge a value into an {ADVANCE} field to create white space to the left (or right) of the image? See Field codes: Advance field - Word - Microsoft Office Online. That seems easier than formatting the position of the image itself, and less hassle than creating a bunch of images and using {INCLUDEPICTURE}.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks for those two suggestions. I will look into this and consider my options.
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Shortly after asking this (10 months ago) the requirement was dropped so I forgot about it...but recently it was resurrected so I have come back to this and I think I have it working.

    Code:
    Option Explicit
     Dim WithEvents MailMergeApp As Word.Application
     Dim lngRectangleLeft As Long
     Dim lngRectangleWidth As Long
     Dim lngStarWidth As Long
     Dim lngScore As Long
    Private Sub Document_Open()
     Set MailMergeApp = Word.Application
     lngStarWidth = Me.Shapes("Star").Width
     lngRectangleLeft = Me.Shapes("Rectangle").Left
     lngRectangleWidth = Me.Shapes("Rectangle").Width
    End Sub
    
    Private Sub Document_Close()
     Set MailMergeApp = Nothing
    End Sub
    
    Private Sub MailMergeApp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
     	lngScore = Me.MailMerge.DataSource.DataFields.Item(8).Value
     	Me.Shapes("star").Left = lngRectangleLeft + CLng(lngRectangleWidth * lngScore / 100 - lngStarWidth / 2)
     
    End Sub
    Initially I used the MailMergeAfterRecordMerge event. This seemed to work, but the position of the Marker actually reflected the score of the previous person. Moving to BeforeRecordMerge fixed that.

    Another issue was knowing the name of the Rectangle and Star that I inserted into the document. I found that if I selected each in turn then ran this code from the immediate window, I could rename each of them to Star and Rectangle
    Code:
    ActiveWindow.Selection.ShapeRange(1).Name = "Star"
    ActiveWindow.Selection.ShapeRange(1).Name = "Redtangle"
    So thanks again for the suggestion that pointed me in this direction.
    Regards
    John



  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi John,

    You could probably achieve what you're after using a field coded as:
    {QUOTE{Set Score {MERGEFIELD Mark}}{IF{Score}>= 1 " "}{IF{Score}>= 2 " "}{IF{Score}>= 3 " "}{IF{Score}>= 4 " "}{IF{Score}>= 5 " "}{IF{Score}>= 6 " "}{IF{Score}>= 7 " "}{IF{Score}>= 8 " "}{IF{Score}>= 9 " "}"X"}
    where 'Mark' is the data field's name, the " " in each string represents a space or tab (bounded by the double quotes) and the "X" is whatever symbol you want to use to represent the score's position. As coded, the field provides for 10 positions (0-9), though you could add more. If you don't need fine gradations and the mark is expressed as a score out of 100, you could also embed the mergefiled into a formula, like:
    {={MERGEFIELD Mark}/10} or {={MERGEFIELD Mark}/20}

    Alternatively, for something that works with finer gradations (as hinted at by Jefferson), you could use code like:
    {ADVANCE \r{MERGEFIELD Score}}X
    or, say:
    {ADVANCE \r{QUOTE{={ MERGEFIELD QUANTITY}*4.25}}}X

    With either of the above approaches, no macros are required.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Thanks I will explore the Advance option. The score is out of 100 I think, and wants fairly fine gradation.

    The link Jefferson provided no longer leads to anything useful, and searching for Advance within Word Help did not return any results, so I used the VBA option.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •