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

    Unanswered: Help Opening 2 Diff Recordsets to Populate Word Tables

    I am trying to create a word form letter from Access 2003 - I almost have it working except for 1 issue

    Any help is appreciated..


    I have 2 tables in the word doc table 1 and Table 2

    I am using the code below to create a word doc based on a template, and loop thru a recordset and fill in the names of Directors

    What I really need to do is to open 2 diff recordsets and loop thru the tables - filling the names in as needed
    if I use the first block of code below (create form letters) - it works well
    except if one person holds 2 offices - i get them in the signature block twice

    So I need to loop thru 1 recordet to populate table 1
    then another to populate the sigs needed in table 2

    The diff is that if any one person holdsmorethan one office - I only want them in the sig table once

    word doc example ---------------------

    Table 1 shows a list of directors (uses a query named qryEntities_Directors)

    Name Office

    John Smith President
    Sarah Ryan Vice resident
    Sara Ryan Treasurer

    at the bottom is a list of signatures
    (I want this to open a recordset based on a diff query that shows Distinct Directors)
    If Sarah Ryan Holds 2 Offices - Then I only want to show her once

    By _________________ John Smith

    By _________________ Sarah Ryan




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

    Private Sub CreateFormLetter()

    'move info to Word Form Letter

    Dim WordObj As Word.Application
    Dim db As Database
    Dim rs As New ADODB.Recordset
    Dim rs2 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

    With WordObj
    .Visible = True

    DoEvents

    'add word doc based on a template

    Dim strDocAdd As String

    strDocAdd = "C:\templates\DirectorsResolution.dot"

    .Documents.Add strDocAdd

    DoEvents

    Dim strSQL As String

    strSQL = "SELECT * from qryEntities_Directors"
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


    DoEvents


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


    i = 2

    Do While Not rs.EOF

    i = i + 1

    'selects the third cell (leaves titles) and populates table 1 with director names/titles)


    objTable.Cell(i, 1).Range.Text = rs("cName")
    objTable.Cell(i, 2).Range.Text = rs("Title")
    objTable.Cell(i, 2).Select
    .Selection.InsertRowsBelow NumRows:=1


    ' I can uncomment the code below to populate the signatures in table 2 - except one person may hold 2 offices
    ' and I only want them in the signature table once


    ' objTableB.Cell(i, 1).Select
    '.Selection.TypeText Text:="By:"
    '.Selection.MoveRight Unit:=wdCell
    '.Selection.TypeText Text:="___________________________________"
    '.Selection.TypeParagraph
    '.Selection.TypeText Text:=" " & rs("cName")
    '.Selection.TypeParagraph
    '.Selection.TypeParagraph
    '.Selection.TypeParagraph
    'objTableB.Cell(i, 1).Select
    '.Selection.InsertRowsBelow NumRows:=1


    rs.MoveNext

    Loop



    ' Call SetSigs - I was thinking of calling another function to open a 2nd recordset?


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




    End With

    ' will use this code when finished to save the doc with a unique name - got that part ok when needed

    '.ActiveDocument.Close
    ' Set WordDoc = Nothing




    '.ActiveDocument.SaveAs "C:\NewDoc.doc"
    'End With

    rs.Close





    End Sub

    --------------------------------------------------

    Private Sub SetSigs()

    here is where I need to open a diff recordset - distinct names to populate the signature table (table 2)

    '----

    Dim rs2 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
    With WordObj

    Dim strSQL2 As String



    strSQL2 = "SELECT * from qryEntities_Directors_Distinct"



    rs2.Open strSQL2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic




    DoEvents





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


    i = 0

    Do While Not rs2.EOF

    i = i + 1



    objTableB.Cell(i, 1).Select
    .Selection.TypeText Text:="By:"
    .Selection.MoveRight Unit:=wdCell
    .Selection.TypeText Text:="___________________________________"
    .Selection.TypeParagraph
    .Selection.TypeText Text:=" " & rs2("cName")
    .Selection.TypeParagraph
    .Selection.TypeParagraph
    .Selection.TypeParagraph
    objTableB.Cell(i, 1).Select
    .Selection.InsertRowsBelow NumRows:=1




    rs2.MoveNext




    Loop


    End With




    End Sub
    Attached Thumbnails Attached Thumbnails tables.jpg  
    Last edited by abinboston; 02-19-08 at 02:41. Reason: attached file

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not kn owing your data or table layout, I'd of thought a select distinct should do the job. but you need to identify the two elements that make a specific row unique, I'm presuming in this case thats going to be company AND director, although you may get away with just
    select distinct(directorid), myothercolumns from mytable where companyid=blah

    incidentally I think for a question like this posting the whole of you code is not helping you. It makes your post very long to read, in this case it doesn't really help the nature fo the problem, although the SQL & table design may have been more pertinent. Its a difficult line to travel how much detail to put in the post. In my view there should be as much as possible, without drowning out the problem with redundant or irrelevant stuff.. effectively for this question all the word automation is irrelevant.

    because the post is quite long some people may just look at it and say I can't be bothered, or if the OP can't be bothered to think about the problem, why should I.
    Last edited by healdem; 02-19-08 at 03:27.

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I see lots of code, but not sure of why you have two tables.
    I also searched the code you posted for the words "if" or "dcount". I encourage you to read about Dcount and Dlookup in Help.

    Not understanding the structure of what you are doing, here is a suggestion from which you may glean an idea. Can you make one recordset, and based on the data you get on each loop refer to the other table for additional information? If needed, use the data referenced in table2. Move on to the next record.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  4. #4
    Join Date
    Aug 2004
    Posts
    173
    jpshay - thanks for the reply. My recordsets/queries do return the records as needed - all for the first one and a distinct selection on the second.


    The idea of 2 tables is this

    Since we do not know th enumber of officers - the .dot template has a table with 1 row - as the recordset loops - it inserts as many rows as needed in the .doc file created. The table is also located in the document where it belongs.

    Then at the very end of the document - the signatures of each officer need to be there. Same as above - we do not know how many there may be - so the table in the .dot file has 1 row, and x no of rows are inserted as needed. This is also located in the document where it belongs. (the image is not the whole document - just the table portions)

    Again - the tables are not the issue - I have that working fine. What I need to figure out is the correct syntax to open the 2nd recordset and populate table 2

    If I use the firset recordset - it all works - except I get dupes in table 2 if one person holds 2 offices. So a 2nd recordset to o a distinct selection works - but I amhaving issues with the looping????

    Thanks - AB




    The problem is that I am sketchy as to how, and when to open the 2nd recordset.

    Thanks - AB

  5. #5
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I cleaned up your code. I deleted vast white spaces, unused variables, unnecessary items, and lots of lines of comments. That clean up is attached. After examining it, I don't understand what is failing. The second function looks like it should work, expecially if you are creating a recordset based on the a query that is distinct.

    Note: This line strSQL = "SELECT * from qryEntities_Directors" could just include the actual SLQ that equals the query.

    When to call the functions? I would call function CreateFormLetter() then call SetSigs(). Unless you have a big bundle of these items. For example, if you have a hundred customers, then you need a bigger loop for all of this. The big loop would go through its criteria, and your inside recordsets would need to be built off of the current customer name.
    Attached Files Attached Files

Posting Permissions

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