Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Unanswered: Print custom Letter from Access Form...

    I am attaching the database and the word document.
    (MS word 97 and Access 97 format)

    I would like to put a button on my form call frm_groups "5500/5558/5330" tab

    what would this button do?

    - well, I want this button to take the word file and insert appropriate information. IT IS RED IN THE WORD FILE.
    - There will be 5 or 6 word letters and this letters are driven by Plan Type. The attach word is a sample letter for 401(k).
    - In the form on the top you can see plan type.
    - however, when end user click the button called "print 5500 regulatory letter" it should take this particular word file and print and give end user an option to save the word file.

    -----------------------------------------------------------------------------
    Anyway, my goal is to automate some letters. This letters are different and depends on plan type (401k, 403b,401a or so on). when end user click the button to print or generate a regulatory letter they system should find the appropriate word document and fill the red inked stuff with access data info.
    ------------------------------------------------------------------------------
    ANY HELP WILL BE APPRECIATED.
    ------------------------------------------------------------------------------
    Suggession: May be we add another field called letter name in the tbl_Plan_Types table and go from there.
    Last edited by doran_doran; 09-16-03 at 01:29.

  2. #2
    Join Date
    Sep 2001
    Posts
    46
    Private Sub cmdCreateLetter_Click()

    Dim rstReplaceCodes As New ADODB.Recordset
    Dim strCurrAppDir As String
    Dim strFinalDoc As String
    Dim varReplaceWith As Variant
    Dim docWord As Word.Document

    On Error GoTo Error_cmdCreateLetter_Click

    '-- Get the application's path and establish the final file name
    strCurrAppDir = CurrentProject.path
    strFinalDoc = strCurrAppDir & "\DemoTest.doc"

    '-- If the final file is already there, delete it.
    On Error Resume Next
    Kill strFinalDoc
    On Error GoTo Error_cmdCreateLetter_Click

    '-- Copy the template so it doesn't get written over.
    FileCopy strCurrAppDir & "\Patient.DOC", strFinalDoc

    '-- Create the OLE instance of Word, then activate it.
    Set appWord = New Word.Application

    '-- Create the object variable
    Set docWord = appWord.Documents.Add(strFinalDoc)

    appWord.Visible = True

    '-- Open the table of replace codes then cycle through them.
    rstReplaceCodes.Open "tblAutomationWordReplaceCodes", _
    CurrentProject.Connection

    Do While Not rstReplaceCodes.EOF

    '-- Get the actual value to replace with, then use the
    ' Word replace.
    varReplaceWith = Eval(rstReplaceCodes!ReplaceWithFieldName)
    varReplaceWith = IIf(IsNull(varReplaceWith), " ", CStr(varReplaceWith))


    With docWord.Content.Find

    If rstReplaceCodes!CodeToReplace = "{ha31}" Then
    If varReplaceWith < 10 Or varReplaceWith > 20 Then
    With .Replacement
    .ClearFormatting
    .Font.Bold = True
    .Font.Italic = True
    .Font.Color = wdColorRed
    End With
    Else

    With .Replacement
    .ClearFormatting
    .Font.Bold = False
    .Font.Italic = False
    .Font.Color = wdColorBlack
    End With
    End If
    End If



    .Execute FindText:=rstReplaceCodes!CodeToReplace, _
    ReplaceWith:=varReplaceWith, Format:=True, _
    Replace:=wdReplaceAll


    End With

    rstReplaceCodes.MoveNext

    Loop


    Exit Sub

    Error_cmdCreateLetter_Click:

    Beep
    MsgBox "The Following Error has occurred:" & vbCrLf & _
    Err.Description, vbCritical, "OLE Error!"
    Exit Sub

    End Sub

  3. #3
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Did you get to work...

    Did it work for you.

    I am getting Compile Error: "User-Defined type not defined"

    Is there a way you can zip the database and attach it here so I can get a better sense. Thanks a billion for your help.

    You rock, You awesome. You are genius.

  4. #4
    Join Date
    Sep 2001
    Posts
    46

    Re: Print custom Letter from Access Form...

    Well , send me mdb I will try it for u...mine working
    crazy
    ====
    yeekuan@pd.jaring.my

  5. #5
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Here is the database.....

    The e-mail address did not work. so i have attach the file here.
    Last edited by doran_doran; 09-16-03 at 01:29.

  6. #6
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Anyone wants to share his/her genius brain...

    Can anyone please help me on this project? Prompt respond will be very appreciated.

  7. #7
    Join Date
    Apr 2003
    Posts
    31

    Add Reference

    you need to add the following reference.

    1. open database
    2. click "alt+F11
    3. from the menu bar, choose "Tools > References"
    4. scroll down to "Microsoft ActiveX Data Objects 2.7 Library"
    5. check the box next to it.

    it starts to work but i get the following error message "type mismatch". not quite sure why but will try to find out.

  8. #8
    Join Date
    Apr 2003
    Posts
    31

    Dim statement

    you need to add the following line to your VB code:

    Dim appWord As Word.Application

    however, that doesn't fix the problem. now you get a SQL error.

  9. #9
    Join Date
    Apr 2003
    Posts
    31

    GOT THE ANSWER

    hi,

    i've got the answer for you and included it within a sample database.

    As you'll notice on your form, there's a button called "Print 5550 Letter".
    You need to copy the button and then modify the code that's behind it.

    If you notice the code below, the (" ") contains the bookmark with the Word file. example, "DATE" is a bookmark location.

    Next, the part of the code that says "frmgroups.GA_5500_Contact_Person_Title" refers to the form name and the field name. frmgroups is the FORM and GA_5000.... is the field name located within that form. You'll need to modify to make sure that it outputs the right data to the right place.

    Hope this works for you. If you still have problem, send me a private message and we can discuss.

    Well, hope if works for you.

    -----------------------------------------------------------------------------
    Sub PrintInvoiceWithWord(frmgroups As Form_frmgroups)

    Dim objWord As Word.Application
    Dim rst As Recordset
    Dim strSQL As String
    Set objWord = New Word.Application

    objWord.Documents.Add _
    Application.CurrentProject.Path & "\5550Template.dot"

    objWord.Visible = True

    ' Add header information using predefined bookmarks
    With objWord.ActiveDocument.Bookmarks
    .Item("Date").Range.Text = frmgroups.TodaysDate
    .Item("Manager").Range.Text = frmgroups.Manager
    .Item("AdminName").Range.Text = frmgroups.Primary_Administrator
    .Item("ComplianceAdmin").Range.Text = frmgroups.PrimaryAdmin
    .Item("Title").Range.Text = frmgroups.GA_5500_Contact_Person_Title
    .Item("EndingDate").Range.Text = frmgroups.EndingDate
    .Item("Address1").Range.Text = frmgroups.Ga_5500_Address1
    .Item("Address2").Range.Text = frmgroups.Ga_5500_City & ", " & _
    frmgroups.Ga_5500_State & " , " & frmgroups.Ga_5500_Zip
    End With

    ' We're done
    Set objWord = Nothing
    End Sub
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2003
    Posts
    31

    forgot try this one

    i forgot to upload the correct one. i've added a feature on the search form for you. let me know what you think.
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2003
    Posts
    31

    Everything works... if else & different docs

    OK, i've got everything completed. here's a list of things that i finished and a list of things you'll need to do.

    my things:
    1. on the from "frm_report", i've added buttons called "start date" & "end date". when you press these buttons it will open up a mini calander for the user to select a date. however, you do not have to use the calander and you can input data directly into the field. OK?

    2. when you go to extract the file, place them all into the same folder, including the subfolders that i've created. if you do not, the word files will not open. i've placed all the plan type docs into seperate folders, made it easier to locate different type of documents.

    3. on form "frmgroups" under the tab "5550/5558/5330" i've added 2 group items, one for plan type and the other for letter type. in order to print the document you want, you'll need to select one from each group. there really isn't anyway around this and it works.

    4. if you plan on importing this information into another database, make sure that you import all the modules & class vb. if you don't, the vb script will not work properly.

    things you'll need to do:
    1. you'll need to modify the vb code for the form so it identified the proper fields within the form. i wasn't sure what ones to use. i've made note above the code to assist, but if you have problems, let me know.

    well, i'm getting tired but i'm happy i was able to help. let me know if you have any questions or comments.
    Attached Files Attached Files

  12. #12
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    sample db

    sample db
    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
  •