Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name Fields and MailMerge (2000)

    Dear All,

    I have a question which is bothering me. I have a form setup with name fields for two lots of people: -

    Name1Initials
    Name2Initials
    Name1FirstName
    Name2FirstName
    Name1Surname
    Name2Surname

    What I want to do is merge to word which I can sort out but have come stuck on using the following criteria for the merge.

    I want to create a merge letter but if the surnames are the same I do not want word to merge it like this:-

    Dear Mr Patel and Mrs Patel I would want Dear Mr and Mrs Patel. is this done at the query level or vb stage? I have had a stab at it but have failed.

    Also if Surname2 is blank then word should only use the Name1 set of name data to do the letter so it would look like this: -

    Dear Mr Patel

    Next if the surnames were not the same then it would merge Dear Mr Patel and Mrs Kaur

    Any ideas?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Name Fields and MailMerge (2000)


  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Fields and MailMerge (2000)

    Attached.

    Thanks for your help so far. I have to credit this website as being an excellent resource, especially as I have only been using Access 2k for two weeks so far.
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Name Fields and MailMerge (2000)

    The following calculated field may do what you want:

    MergeName: "Dear " & IIf([Surname2]=[Surname1],[Title1] & " and " & [Title2] & " " & [Surname1],[Title1] & " " & [Surname1] & IIf(IsNull([Surname2]),""," and " & [Title2] & " " & [Surname2]))

    See the query in the attached version.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Fields and MailMerge (2000)

    Ok, Thanks for this works a treat, just one questions now.

    I have entered a new valuation on the form if I click the button I get an error saying the merge data is empty. If I move backa record and go back tot he record I was on I can print. So I need to add a refresh command at the top of the vba code before it starts to write the mailmerge? How do I do this?

    Luke
    Best Regards,

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Name Fields and MailMerge (2000)

    Insert

    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Fields and MailMerge (2000)

    I have managed to resolve. I added an after-update event to another field in surname1 me.refresh

    Getting there....slowly.
    Best Regards,

    Luke

  8. #8
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Fields and MailMerge (2000)

    Sorry last one promise.

    At the end of the click event how do I get it to update a yes/no field called LetterSent to be true (tick appear).

    Luke
    Best Regards,

    Luke

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Name Fields and MailMerge (2000)

    Near the end of the PrtApptLetter_Click( procedure, add a line

    Me.LetterSent = True

Posting Permissions

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