Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Access to Word Mail Merge Help - One to Many Recordsets

    I am using the code below to create a document based on a .dot file
    this document has 1 bookmark for corporationname
    it also has 2 tables in a specific location that is populated with one to many data from various recordsets
    and some text for the letter itself....

    it works well so far - for creating and saving 1 document (it is run form a form with a record selected)

    I would like to add another recordset - tblEntities , then loop thru that to create 1 mail merge document with each entity in the one document if each letter is 2 pages, then 100 entities = 200 pages

    how can I combine the code below with mail merge code to create one document vs only an individual document?

    I tried a mail merge with only the corp or entity name and 1 officer - and I created all the pages.
    The issue is the one to many tables with a variable number of officers/shareholders

    how can I do a mail merge - AND use the one to many recordsets of officers and shareholders in the tables?

    Any ideas are appreciated

    AB







    code below
    -----------------------------------


    'move info to Word Form Letter

    Dim WordObj As Word.Application
    Dim db As Database
    Dim rsPresident As New ADODB.Recordset
    Dim rsChairman As New ADODB.Recordset
    Dim rsDirectors As New ADODB.Recordset
    Dim rsDirectorsDistinct As New ADODB.Recordset
    Dim rsOfficers As New ADODB.Recordset
    Dim rsOfficersDistinct As New ADODB.Recordset
    Dim rsShareholders As New ADODB.Recordset
    Dim rsShareholdersDistinct As New ADODB.Recordset
    Set WordObj = CreateObject("Word.Application")
    Set db = CurrentDb()
    Dim ID, Criteria
    Dim objTable As Table
    Dim objTableB As Table
    Dim intNumRows As Integer
    Dim i As Integer
    Dim j As Integer

    With WordObj
    .Visible = True

    DoEvents

    'add word doc based on a template

    Dim strDocAdd As String
    'strDocAdd = Chr(34) & Me.txtStoredPath & "\" & Me.txtFormLetter & Chr(34)


    strDocAdd = "C:\templates\ShareholdersResolution.dot"
    ' .Documents.Add "C:\templates\invoice.dot"

    .Documents.Add strDocAdd

    DoEvents


    Dim strSQLPresident As String

    strSQLPresident = "SELECT * from qryEntities_President WHERE (((qryEntities_President.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsPresident.Open strSQLPresident, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents

    Dim strSQLChairman As String
    strSQLChairman = "SELECT * from qryEntities_Chairman WHERE (((qryEntities_Chairman.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsChairman.Open strSQLChairman, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents



    Dim strSQLDirectors As String

    strSQLDirectors = "SELECT * from qryEntities_Directors WHERE (((qryEntities_Directors.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsDirectors.Open strSQLDirectors, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents

    Dim strSQLDirectorsDistinct As String
    strSQLDirectorsDistinct = "SELECT * from qryEntities_Directors_Distinct WHERE (((qryEntities_Directors_Distinct.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsDirectorsDistinct.Open strSQLDirectorsDistinct, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents


    DoEvents

    Dim strSQLOfficers As String
    strSQLOfficers = "SELECT * from qryEntities_Officers WHERE (((qryEntities_Officers.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsOfficers.Open strSQLOfficers, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents


    Dim strSQLOfficersDistinct As String
    strSQLOfficersDistinct = "SELECT * from qryEntities_Officers_Distinct WHERE (((qryEntities_Officers_Distinct.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsOfficersDistinct.Open strSQLOfficersDistinct, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


    DoEvents

    Dim strSQLShareholders As String
    strSQLShareholders = "SELECT * from qryEntities_Shareholders WHERE (((qryEntities_Shareholders.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsShareholders.Open strSQLShareholders, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents


    Dim strSQLShareholdersDistinct As String
    strSQLShareholdersDistinct = "SELECT * from qryEntities_Shareholders_Distinct WHERE (((qryEntities_Shareholders_Distinct.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsShareholdersDistinct.Open strSQLShareholdersDistinct, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents


    Dim vCorpName As String

    vCorpName = rsDirectors("EntityName")

    .ActiveDocument.Bookmarks("corporationname").Selec t
    .Selection.TypeText vCorpName

    Set objTable = .ActiveDocument.Tables(1)
    Set objTableB = .ActiveDocument.Tables(2)


    i = 2

    Do While Not rsDirectors.EOF

    i = i + 1


    objTable.Cell(i, 1).Range.Text = rsDirectors("cName")
    'objTable.Cell(i, 3).Range.Text = rsOfficers("Title")
    '.Selection.InsertRowsBelow NumRows:=1
    objTable.Cell(i, 1).Select
    .Selection.InsertRowsBelow NumRows:=1


    rsDirectors.MoveNext

    Loop

    'populate signatures with rs2 - a distinct selection of officer names

    i = 2

    Do While Not rsShareholders.EOF

    i = i + 1

    objTableB.Cell(i, 1).Select
    .Selection.TypeText Text:=""
    .Selection.MoveRight Unit:=wdCell
    .Selection.TypeText Text:="___________________________________"
    .Selection.TypeParagraph
    '.Selection.TypeText Text:=" " & rsShareholders("cShareholderSig")


    If rsShareholders("ShareholderType") = "Individual" Then
    .Selection.TypeText Text:=rsShareholders("cShareholderSig")

    Else
    ' .Selection.TypeText Text:=rsShareholders("cShareholderSig")
    ' .Selection.TypeParagraph
    .Selection.TypeText Text:=vCorpName
    .Selection.TypeParagraph
    .Selection.TypeText Text:="Authorized Signing Officer"

    End If

    .Selection.TypeParagraph
    .Selection.TypeParagraph
    .Selection.TypeParagraph
    objTableB.Cell(i, 1).Select
    .Selection.InsertRowsBelow NumRows:=1

    rsShareholders.MoveNext

    Loop


    ' Clean up.
    'Set objDoc = Nothing
    Set objTable = Nothing
    Set objTableB = Nothing




    ' End With





    .ActiveDocument.SaveAs "C:\SHResolutions" & "\" & Me.EntityName & "_SHR_08" & ".doc"

    'End With

    End With
    '.ActiveDocument.Close
    ' Set WordDoc = Nothing


    rsPresident.Close
    rsChairman.Close

    rsDirectors.Close
    rsDirectorsDistinct.Close
    rsOfficers.Close
    rsOfficersDistinct.Close

    rsShareholders.Close
    rsShareholdersDistinct.Close
    Last edited by abinboston; 03-01-08 at 16:47. Reason: spelling

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    I don't mean to pick at your code, but it happens while I'm reading it and figuring out what you're getting at.

    Dim strSQLPresident As String

    strSQLPresident = "SELECT * from qryEntities_President WHERE (((qryEntities_President.EntityCode)=" & "'" & Me.EntityCode & "'))"
    rsPresident.Open strSQLPresident, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    DoEvents
    Okay, first thought: could this be wrapped up in a function?

    Next: SELECT * really leaves me clueless as to what columns you're using.

    Next: Take out all those DoEvents.

    Now I'm getting down to the last of them: you open all those recordsets and don't use them. Please, when posting code, snip out the redundant stuff so I don't have to sift through it.

    how can I do a mail merge - AND use the one to many recordsets of officers and shareholders in the tables?

    You just need to join the different tables together. Make a query, copy the SQL code and paste it in.

    The other alternative is to open up a new recordset during each iteration and look up the data that way. But it's always best to let the DBMS handle joins rather than recoding it in VB.

  3. #3
    Join Date
    Aug 2004
    Posts
    173

    Reply...

    Thanks for the reply..

    The info below will answer some ??s

    In regards to the many recordsets - I use the same code for a few diff letters - some use a few of the recordsets - others use diff ones.

    Once I am finished getting this to work - then I wil clean it all up, removing any unused recordsets.

    Tables - when I say tables, I mean tables in MS Word... I have a 1 row table in a fixed location in the Word .dot file - this where a list of officers or shareholders goes.


    if one officer then it fills the table row

    if more - it inserts a row and adds each officer.
    Any entity may have 1, 2, 3, 4, 10, 15 etc officers

    Doing it this way allows the text below the tables to dynamically move down


    As I said - I can generate one document for one entity using the code shown.
    What I cannot do is create a mail merge letter - meaning all 100+ entities in one word document....


    I can do it using a regular mail merge doc and only the entityname - but when I try to populate the word tables using the recordsets - it will not work.

    I can create a function/module to do this - but for the ease of making it all work, I use a buton on a form - for now :-)

    Thanks - AB

  4. #4
    Join Date
    Aug 2004
    Posts
    173

    Do Events

    I added the doevents for this reason - on my system a P4 with 2GB RAM - it works fine - at my clients site - on a few of their old and slow pcs over a network it did not seem to open the recordset and the word doc generated an error

    Adding the DoEvents seems to have fixed that?


    Is adding DoEvents a bad thing? From what I read online - adding DoEvents makes sure that the event is completed before moving on to the next event?



    Thanks - AB
    Last edited by abinboston; 03-02-08 at 02:35.

  5. #5
    Join Date
    Aug 2004
    Posts
    173

    One More - Next: SELECT * really leaves me clueless

    the query qryPresident only selects ONE record - the Officer where the title is President - I have a query that selects entity code and officername where title = "President"

    since I added a parameter - entityid - the recordset will always return 1 record - for each entity.

    AB

  6. #6
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    The info below will answer some ??s

    In honesty, I've got no clue as to what you're asking. Sorry.

Posting Permissions

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