Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Question Unanswered: Need Access to Word help!

    I need to have a way to move data from Access to Word. I'm trying to do a mail merge of sorts, but the difference is that each record needs to have its own document. These documents will be uploaded to a document imaging system/database. (It has to go this route, I can't write to the other database directly). I have code that works, but we will be creating about 5,000 documents at a time and it takes about 45 minutes to run, mostly because of the 'printing... .doc' message that pops up for every document. A second reason for its slowness is the fact I'm creating a new query 5,000, once for every record.

    Can I get rid of that popup? Or is there a better way? Here is my code:

    strSQL = "SELECT ID FROM tblCaseload WHERE CreationDate >= #" & datCreationDate & "#"

    datTimerStart = Now()

    ''count records
    Set rst1 = CurrentDb.OpenRecordset(strSQL)
    rst1.MoveLast
    lngCount = rst1.RecordCount
    lngLoop = 0

    vbPrint = MsgBox("Do you want to create the " & lngCount & " items?", vbYesNo, "CONFIRM TO CONTINUE")

    If vbPrint = vbYes Then
    'continue
    Else 'cancel
    MsgBox "Job has been cancelled.", , "Job cancelled"
    Exit Sub
    End If


    ''delete exsting query
    Dim qdf As DAO.QueryDef

    For Each qdf In CurrentDb.QueryDefs
    If qdf.Name = "qryCaseload" Then
    CurrentDb.QueryDefs.Delete "qryCaseload"
    Exit For
    End If
    Next

    Set qdf = Nothing


    '*************** Create Items ***********************

    rst1.MoveFirst
    Do Until rst1.EOF

    strID = rst1("ID")
    strFileName = strID & ".doc"


    Set qDefNew = CurrentDb.CreateQueryDef("qryCaseload", "SELECT * FROM tblCaseLoad WHERE ID = '" & strID & "'")

    'this report uses qryCaseload as its record source
    DoCmd.OutputTo acOutputReport, "REPORT Caseload", acFormatRTF, strPath & strFileName

    CurrentDb.QueryDefs.Delete qDefNew.Name

    Set qDefNew = Nothing

    rst1.MoveNext

    lngLoop = lngLoop + 1

    Loop

    rst1.Close
    Set rst1 = Nothing

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't know of a way to get rid of the pop up, but my guess would be that it's only up while Access is busy outputting the report anyway, so I doubt it slows you down much. Rather than the whole QueryDef route, which would slow things down a bit, I use this type of thing:

    Emailing a different report to each recipient

    In your loop you'd copy the current ID to a form textbox, then output the report. The code in the report would use that textbox to filter itself.

    Edit: another option would be to base the report on a query that included a criteria pointing to that textbox. It makes the report a little less flexible, but avoids the code in the report.
    Paul

  3. #3
    Join Date
    Sep 2010
    Posts
    2

    Thumbs up

    Thanks, Mr. Pbaldy.

    Interesting idea that I will try today. As long as I can use two criteria - ID & CreationDate, it should work. You don't know how hard I looked to find something usable. I kept looking for things like "export report to Word" and got all kinds of mail merge hits, but nothing like this. I almost feel silly for not thinking of trying a form in the first place because it is so simple.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help, and welcome to the site by the way! Post back if you get stuck (two criteria should be no problem).
    Paul

  5. #5
    Join Date
    Sep 2010
    Posts
    1
    Thanks, Mr. Pbaldy.

Posting Permissions

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