Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge with Word based on Current Record (Access 2000)

    I have no idea if this can even be done but I will describe what I want to do. I have a database that tracks training dates and certification for my customers. When the training is done I print out a letter and certificates for each of the people that were trained. Until now what I have done is just open the word document that made a call to a query in the database that prompted for the project number and then spit out the letter. I then did that again for the certificates.

    What I actually want to do is be able to have a button right on the form so that It will print the letter and certificates based on the record that I am currently viewing. That way it doesn't ask for the project number twice but rather just stores the value of the Project Number field and runs the two queries using that stored information as the criteria.

    I hope this makes sense to someone. I know what I want it to do, just haven't the foggiest idea how to do it. Any feedback would be greatly appreciated!!

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

    Re: Mail Merge with Word based on Current Record (Access 2000)

    I do the following in such situations:
    <UL><LI>Create an SQL string in code that selects the data from the current record needed for the letter. I concatenate the value of the unique ID of the customer to the WHERE condition:

    strSQL = "SELECT LastName, FirstName, DateOfBirth FROM tblCustomers WHERE CustomerID = " & Me.CustomerID

    <LI>Use Automation to start Word, create a new document (objMergeDoc) based on the desired document, and initiate a mail merge with the generated SQL as data source:

    With objWordDoc.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource _
    Name:="", _
    LinkToSource:=True, _
    Connection:="DSN=Access;DBQ=" & CurrentDb.Name, _
    SQLStatement:=strSQL
    .SuppressBlankLines = True
    .Execute
    End With

    "Access" is a generic Access ODBC data source.
    <LI>You can repeat this for the certificate.[/list]Perhaps this is enough to get you started, otherwise post back.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Word based on Current Record (Access 2000)

    I have to be completely honest and say that I have no idea what I am doing. I have managed to do stuff in VB before but nothing like what you are describing. However, I do think that I can figure it out with a wee bit more help hehe!!

    First off, I already have the existing query that was created and the Word Document that is already created makes a call to that query. For the first part where you Create the SQL string in code, can I go to the query that i have created, remove any criteria, view the SQL expression and then cut and paste. Then add "WHERE [Project Info].[Project Number] = " & "[Project Info].[Project Number]" You kinda lost me with the "&Me.CustomerID at the end of your SQL statement. I know what Concatenate is but what is "Me" LOL

    I did notice however the SQL expression that Access generates is alot messier than what you have put in your example LOL

    As for the Automation to start word, I get an error on the "With objWordDoc.MailMerge" before it even gets to the meat of the script. I understand if you dont want to be a tutor and if you dont have time to go in to more detail I completely understand. Just trying to get my feet wet here hehe!!

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

    Re: Mail Merge with Word based on Current Record (Access 2000)

    My previous reply by no means pretended to offer working code, there is a lot more involved.

    You can copy the SQL from an existing query into a code window, but you'll have to do some cleaning up, as you found out. The most important point to observe is that the SQL of a stored query contains line breaks; you'll have to remove these, or break up the SQL into parts, for example (the underscores _ are continuation characters, they specify that the instruction is continued on the next line):<pre>strSQL = "SELECT LastName, FirstName, DateOfBirth " & _
    "FROM tblCustomers " & _
    "WHERE CustomerID = " & Me.CustomerID</pre>

    This last bit means that I select records from the tblCustomers table for which CustomerID (the unique identifier of each customer) is equal to the value of CustomerID for the current record of the form. "Me" is a way of referring to the form (or report) running the code.
    Another, less important point is that Access puts in enormous amounts of brackets and parentheses in SQL strings; this is a byproduct of the way Access translates design view into SQL.

    If you want to learn about Automation, take a look at the website of fellow Access moderator <!profile=WendellB>WendellB<!/profile>. You'll find a link to his website if you click his name to see his profile. On the website, look for Support, then Tutorials. The tutorial on Automation is brief, but it contains a lot of links to other useful information. I suggest that you take a look there, and come back here if you have more questions.

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Word based on Current Record (Access 2000)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    ok I am on a roll now I just have one more question LOL

    I went to that site you gave me and there was a link to the Microsoft KB that is a different way of doing it and seems to be easier for me to understand. This is the page I was reading.... http://support.microsoft.com/default.aspx?...kb;en-us;210271

    Anyway, It works perfect if it is a field that is directly in the form but I am wondering how to change it to pull information from a subform....

    .ActiveDocument.Bookmarks("Company").Select
    .Selection.Text = (CStr(Forms!Training_Records!CUSTNAME))

    .ActiveDocument.Bookmarks("Add1").Select
    .Selection.Text = (CStr(Forms!Invoice_To!Address_1))

    Basically, Training Records is the main Form and that first one works perfect, however the "Address_1" field is in an Form called "Invoice_To" which is a subform inserted in the Training_Records Form. is there a way to change the "Forms!Invoice_To!Address_1" so that is knows to look at the Subform??? I guess the issue becomes if there is more than one record in that subform which record does it pick from.

    I have a sneaky suspicion this isn't going to be able to done and then I may have to try and combine the two ideas. Look forward to hearing back.

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

    Re: Mail Merge with Word based on Current Record (Access 2000)

    The way to refer to a control on a subform is Forms!MainForm!SubForm!Control, so in your case Forms!Training_Records!Invoice_To!Address_1

    See Forms: Refer to Form and Subform properties and controls for a comprehensive overview of how to refer to subforms and their controls.

    <big>BUT</big> as you suspect yourself, you have a problem if the subform contains more than one record. The method above gets the value of Address_1 in the currently selected record of the subform. If you want to get the values from all records in the subform, the code becomes more complicated: you have to open a recordset that represents the records displayed in the subform, and loop through it. I don't know of that is what you want to do.

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Word based on Current Record (Access 2000)

    In response to your second last post, with the multiple records in a sub form, I will worry about that when I need to do the Certificates. With the Letter I only need the first record in the subform so it works perfect. Thanks for the Help with the format for the subforms, worked like a charm!!!

    As for using a report right in Access the only reason I haven't (and I wanted to in the first place) is because there are going to be some people that want to recall the letter at a later date who do not have Access (Licencing Issues). I will finish off the script with a bit of Code to Save the File somewhere else so that we can always look at it later without having to use a computer with Access.

    Anyway, thank you soo much for all your help, I am about 80% there now, I just have to figure out how to get a few fields that are not included in the Form but I think I know how to do that.... Again, thanks for all the help!!!

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

    Re: Mail Merge with Word based on Current Record (Access 2000)

    Alternative idea:

    Wouldn't it be easier to design the letter and/or the certificate as reports in Access? You can format a report to look the way you like, and you can use a subreport just the way you can use a subform (originally wrote subreport here - didn't make much sense that way).

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge with Word based on Current Record (Access 2000)

    you could always use snapshot viewer.

  10. #10
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Word based on Current Record (Access 2000)

    Now this is gonna sound really stupid but....

    THERE IS A VIEWER FOR THAT!?!?!?!?!

    LOL [img]/forums/images/smilies/smile.gif[/img] oh well, I got it done now and it works perfect!!!

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Mail Merge with Word based on Current Record (Access 2000)

    There's a viewer for files saved as snapshots (.snp) from Access. It is downloadable from Microsoft, but it's a proprietary format and only works with snapshot files.
    Charlotte

  12. #12
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge with Word based on Current Record (Access 2000)

    Thanks a bunch!!! Now I have two solutions!!

    Really apreciate all the help guys. I knew it was possible, was just a matter of figuring out how to do it. I have alot of other uses for this now that I know how to do it.

Posting Permissions

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