Results 1 to 8 of 8
  1. #1
    claven123 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    16

    Send mult records to word via VBA

    I have a table with three main classes. Active, Honorary and Junior. I would like to make a list of all the active members and send that data to a bookmark in word. This will be about 40 records. So, the list will be 40 names. And the same with the other three classes.



    I am able to do this with the current record and get the one name to show up in the word doc. However, I want to do this for all 40 members with the class active.

    Do I do this with a record set? And do I then loop thru all the records (about 90) and do a SELECT LastName, FirstName FROM TblMembers WHERE Status = "Active"

    I've seen code to do the one record, but I'm having trouble getting the code correct.

    Thanks,

    Dennis

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I've not done this before so how about showing us the code for the one line that works? Maube we can adjust it for you for more than one line.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    claven123 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    16
    Here is the code...

    Code:
    Private Sub CmdWordPrint_Click()
    
        Dim Word As New Word.Application
        Set Word = CreateObject("Word.Application")
        Dim MergeDoc As String
        MergeDoc = Application.CurrentProject.Path
        MergeDoc = MergeDoc + "\PPFDIncidentReport.dotx"
    
        Word.Documents.Add MergeDoc
    
        Word.Visible = True
    
        With Word.ActiveDocument.Bookmarks
        
            
        txtIndex = ""
            
        .Item("Index").Range.Text = Me.City
        
        
    End With
    
    
    End Sub
    This only pulls the information from the current active record.




    I was thinking about using something along the lines of this code below and modifying it to work for my db. I'm not really sure how this works.... Not sure what to sub for my titles and objects.

    Code:
    'Private Sub SetQuery(strQueryName As String, strSQL As String)
        'On Error GoTo ErrorHandler
        
            'Dim qdfNewQueryDef As QueryDef
            'Set qdfNewQueryDef = CurrrentDb.QueryDefs(strQueryName)
            'qdfNewQueryDef.SQL = strSQL
            'qdfNewQueryDef.Close
            'RefreshDatabaseWindow
            
    'Exit Sub
    
    'Error Handler:
        'MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    'Exit Sub
    
    'End Sub
    
    'Private Sub cmdMergeIt_Click()
    'On Error GoTo ErrorHandler
    
    'Dim strPostalCode As String
    'strPostalCode = txtPostalCode.Value
    'Dim strSQL As String
    
    'strSQL = "SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Position, FROM TblMembers WHERE TblMembers.Status = ' " & strPostalCode & " ' ;"
    
    'Dim strDocumentName As String
    'strDocumentName = "\..........docx"
    
    'Call SetQuery("qryLabelQuery", strSQL)
    This code below is another option I was thinking about modifying for my db. Using a record set

    Code:
    'Dim mWord As Object
    'Dim cnxn As ADODB.Connection
    'Dim rL As ADODB.Recordset
    'Set cnxn = CurrentProject.Connection
    'Set rL = New ADODB.Recordset
    'Set mWord = CreateObject("Word.Application")
    'mWord.Documents.Add "C:\odis\odis_PPS_100_CaseClosing.dot"
    'With mWord.ActiveDocument
    '.Bookmarks("off_name").Range.Fields(1).Result.Text =
    'GetOffenderName()
    'rL.Open "current_sup", cnxn, adOpenForwardOnly, adLockReadOnly,
    'adCmdTableDirect
    'If Not rL.EOF Then
    '..Bookmarks("cs_open_date").Range.Fields(1).Result .Text =
    'rL.Fields ("cs_open_date")
    'rL.Close
    '.Bookmarks("sup_adjustment").Range.Fields(1).Resul t.Text =
    'Nz(Forms("SupervisionForm")!memAdjustment, "")
    '.Bookmarks("Today").Range.Fields(1).Result.Text = Date
    'End With
    'mWord.Visible = True
    'Set mWord = Nothing
    Thanks for any help.

  4. #4
    claven123 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    16
    Of note, I already have a query that pulls the data I need. It's name is QryRosterActiveJunior.

    Here is the sql statement:

    SELECT TblMembers.ID, [LastName] & ", " & [FirstName] AS FullName, TblMembers.LastUpdated, TblMembers.Status, TblMembers.JoinDate
    FROM TblMembers
    WHERE (((TblMembers.Status)="Active"))
    ORDER BY [LastName] & ", " & [FirstName];

    What I really need out of the query is the second column, the full name.

    All my data is in the table: TblMembers

    The field that is the decision point is: Status


    I would like to use the current query and not have to make a new one, but will do that if neeed.

    I'm ganna keep working on it.


    Thanks,

    Dennis

  5. #5
    ajetrumpet is offline Banned
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    claven,

    are you doing a merge with word? It looks like it. I've done it through Access, but I don't know if your need is the same. Here is the function that I have written to automate a simple word merge:

    Code:
    Function MailMerge(mDoc As String, _
                       strSql As String)
    
    On Error GoTo Err_Handle
    
    '******************************************************************************
    '_____________________________________________________________________________*
    '                                                                             |
    'THIS FUNCTION USES THE CURRENT DATABASE AS THE MAIL MERGE SOURCE.  IT ALSO   |
    'USES LETTERS AS THE MERGE FORMAT.  THERE IS NO ARGUMENT TO CHANGE THIS.      |
    '_____________________________________________________________________________|
    '                                                                             *
    'Author: Adam Evanovich                                                       *
    'Date: 5/9/2005                                                               *
    'Purpose:  To run an automated Mail Merge with a MS Word template.            *
    '                                                                             *
    'Arguments:                                                                   *
    'mDoc > Full path of the Merge document.                                      *
    'strSQL> The query to run the Merge with.                                     *
    '                                                                             *
    '******************************************************************************
    
    
    Dim oApp As New Word.Application
    Dim oMainDoc As Word.Document
    Dim sData As String
    
       oApp.Visible = True
          sData = CurrentProject.Path & "\" & CurrentProject.Name
    
       Set oMainDoc = oApp.Documents.Open(mDoc)
       
          With oMainDoc.MailMerge
              .MainDocumentType = wdFormLetters
              .OpenDataSource Name:=sData, _
                              SQLStatement:=strSql
          End With
       
       With oMainDoc
           .MailMerge.Destination = wdSendToNewDocument
           .MailMerge.Execute
       End With
       
       oApp.Activate
       oApp.Documents.Parent.Visible = True
       oApp.Application.WindowState = 1
       oApp.ActiveWindow.WindowState = 1
    
    Set oApp = Nothing
    Set oMainDoc = Nothing
    
    Exit Function
    
    Err_Handle:
       Set oApp = Nothing
       Set oMainDoc = Nothing
          MsgBox "An error occurred..." & vbCrLf & vbCrLf & err.Description
    
    End Function '//LL
    If you're doing one document per record, that code might actually help you out. The db has to be set to ''shared'' obviously though, because it'll throw a ''locked'' error otherwise.

    The other thing I will say is that if you search: http://www.access-programmers.co.uk/forums/search.php for ms word and posts started by Mike375, you're more than likely to find your solution. Mike is obsessed with integrating word and Access, and he uses nothing else but bookmarks. I know there are at least a couple of threads started over there that cover bookmarks, because I helped solve one of them.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You do understand that static queries (pre-defined) only take up the room necessary to describe the SQL and some strategy? No data is used in the definition of the query. Why the reluctance to creating another query?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    claven123 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    16
    Thanks for the input, but I need to send multiple records to word as a list, not one record. I will look at that link.

    I'm not apposed to making a new query, but thought it would be easier to use what I have.

    Any insight on how to do the code or an example to work off of?

    Thanks,

    Dennis

  8. #8
    claven123 is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Posts
    16
    Any insight on this. I was able to make a list box in a form and get it the correct way. However, the list box always has a box around it when printed, even when set to transparent.

    I can also create this with a report, but the list of names needs to go on the right side of the report from top to bottom and the left side with all the places to fill in data. (this is a fire report tool I'm making) This is a paper from they will print out that has all the members on it and at the fire scene the officer will fill in the house number, temp, wind direction etc... address occupants etc..

    I would love to be able to do this with word, but I can't seem to get the entire list to populate to a bookmark. Any advice on this?

    Thanks,

    Dennis

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How can I send an email from access???
    By Asma in forum Programming
    Replies: 2
    Last Post: 12-07-2011, 07:49 AM
  2. send object report
    By ldappa in forum Access
    Replies: 1
    Last Post: 07-19-2010, 10:10 AM
  3. Send mail to the chosen ones
    By carstenhdk in forum Import/Export Data
    Replies: 0
    Last Post: 05-18-2010, 11:51 PM
  4. Export single records to new Word Document
    By karmaimages in forum Import/Export Data
    Replies: 0
    Last Post: 11-19-2009, 03:37 PM
  5. Send parameters to queries
    By Merkava in forum Programming
    Replies: 8
    Last Post: 11-06-2009, 02:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums