Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DDE Parameter Problem (Access 2000 / Word 2000)

    I have a form that when a "Print approval" button is pushed, it starts a macro that uses RunApp to start a Word Document. This Word document is set up to pull the fields contact_full_name, Vendor_name, Address, city, state, zip, Contact_first_Name, customer, Term, spread1, Approval_Amount_2, and Expdate out of a query, and merge them into a Word document via DDE. Each record has a application number, and the query specifies the application number showing on the form [Forms]![Activity Log]![Application Number] so Word selects the correct record [Application Number] to pull the data from. Word then creates a new document with the merged data and remains open so that the document can be modified, and then saved under a different file name.

    This was all working fine until I changed the database using the start up option to prevent users from getting under the covers. Now when the "Print approval" button is pushed, the macro launches Word, but Word pops up with a box that says "Enter Parameter Value" specifying [Forms]![Activity Log]![Application Number]. When I open the database while holding down the shift key, the Word document merges the data with no user intervention needed.

    I'm a complete noobie when it comes to VB, and I've searched around and round, and haven't found a good simple solution that would allow me to use Automation with a query and get rid of DDE. I haven't figured out how to fix it so DDE works properly either. I've also updatede to the latest service packs and patches for both Word and Access. Any help or advice would be greatly appreciated.

    Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    Is Word starting a second copy of Access where the relevant form is not open? This has been a common problem. Removing the Application title from the Startup options is supposed to fix this.

    Another approach to this situation is to replace the merge fields in the word doc with bookmarks, then push the data directly into the document from Access. This is much much quicker, but does involve some heavy VBA code.
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    I removed the application title from the Startup options and now it works fine. Thanks! Too bad its taken me all day to come up with a simple answer.

    Meanwhile, I am interested in using bookmarks (or some other method to speed things up and get rid of DDE), but the example I found in the Microsoft KB used a form rather than a query to populate the bookmarks in Word, so it was a bit over my head.

    http://support.microsoft.com/default.aspx?...Product=acc2000

    I'd appreciated it if someone could clue me in on how to adapt this to my needs, or point to a better example.

    Again, THANKS!

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    I have been changing some merge docs to Bookmark docs recently, and the speed change is significant.

    Here is some code, that generates a letter in four secs on my computer (if Word is already running). The merge takes about 20 secs.

    The Word doc needs to be a template now, so a new document is created based on the template.

    A query is built in code as an sql string, then a recordset is opened based on the query. The data from the recordset is put into some string variables, and the bookmarks are set to equal the values in these strings.


    Private Sub print_new_Click()
    On Error GoTo Print_new_Errhand

    Dim Strletter As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim ObjWrd As Word.Application
    Dim objDocument As Word.Document
    Dim mypath As String
    Dim strtitle As String
    Dim strfirstname As String
    Dim strOthername As String
    Dim strsurname As String
    Dim strAddress1 As String
    Dim straddress2 As String

    mypath = CurrentProject.Path & "templates"
    Strletter = "newChangeOfAddress.dot"
    Set db = CurrentDb()

    If Not IsNull(Me!DonorID) Then
    sql = "SELECT ActiveDonor.* from ActiveDonor where ActiveDonor.[DonorID]=" & Me![DonorID]

    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    Set ObjWrd = GetObject(, "Word.Application")

    If Not rs.EOF Then
    rs.MoveFirst
    strtitle = Nz(rs![Title], "")
    strfirstname = StrConv(Trim(Nz(rs![First Name], "")), vbProperCase)
    strOthername = StrConv(Trim(Nz(rs![Other Name], "")), vbProperCase)
    strsurname = StrConv(Trim(Nz(rs![Last Name], "")), vbProperCase)
    strAddress1 = Trim(Nz(rs![PostAddress1], ""))
    If Len(Nz(rs![PostAddress2], "")) > 0 Then
    strAddress1 = strAddress1 & vbNewLine & Nz(rs![PostAddress2], "")
    End If
    straddress2 = Nz(rs![PostCity], "") & " " & Nz(rs![PostState], "") & " " & vbTab & Nz(rs![Postal Code], "")

    Set objDocument = ObjWrd.Documents.Add(mypath & Strletter)
    With objDocument.Bookmarks
    .Item("title").Range.Text = strtitle
    .Item("firstname").Range.Text = strfirstname
    .Item("othername").Range.Text = strOthername
    .Item("surname").Range.Text = strsurname
    .Item("Postaddress1").Range.Text = strAddress1
    .Item("PostAddress2").Range.Text = straddress2
    .Item("title2").Range.Text = strtitle
    .Item("surname2").Range.Text = strsurname
    End With
    ObjWrd.Visible = True
    ObjWrd.Activate
    ObjWrd.WindowState = wdWindowStateMaximize
    ObjWrd.ActiveWindow.WindowState = wdWindowStateMaximize

    Else
    MsgBox ("There is no data for the letter.")
    End If
    rs.Close

    Set rs = Nothing
    Set db = Nothing
    Set objDocument = Nothing
    Set ObjWrd = Nothing
    Else
    MsgBox ("Select a donor.")
    End If

    Exit_print_letter_new_click:
    Exit Sub

    Print_new_Errhand:
    If Err.Number = 429 Then 'word is not running
    Set ObjWrd = CreateObject("Word.Application")
    Resume Next
    ElseIf (Err.Number = 0) Or (Err.Number = 20) Then
    Resume Next
    Else
    MsgBox (Err.Number & " " & Err.Description)
    Resume Exit_print_letter_new_click
    End If
    End Sub
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    In addition to John's VBA example using bookmarks, you might explore using an ODBC driven merge using Word - it's a fair bit quicker that DDE, but not quite as fast as automation. You might also want to take a look at our Word Merge Tutorial which talks about how to overcome the parameter query issue (with thanks to Hans) and Automation Tutorial which has a number of links to MSKB articles on automation.
    Wendell

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    This looks great but it is bit beyond my current capabilities, so naturally I have some questions like:

    What does this line do? mypath = CurrentProject.Path & "templates"
    This would be the name of my template? Strletter = "newChangeOfAddress.dot"

    I could go on, but I really need to develop a better understanding of VB before I could discuss this intelligently. I do appreciate you sharing it, since it gives me some ideas.

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    Will do. Thanks for the links.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    I am not sure if your questions were rhetorical, but here are the answers.

    What does this line do? mypath = CurrentProject.Path & "templates"

    I need to specify the full path of the template. Instead of hard coding in a path, I keep my templates in a folder called templates, which is a subfolder of the folder that the current mdb file is in. CurrentProject.path will return this path. This way the files can be moved from my testing and development environment to the real place where they will be used, and nothing needs to be changed to make it work.


    This would be the name of my template? Strletter = "newChangeOfAddress.dot"
    Yes this is the name of the template.
    (Because I am in the proces of changing over, my new bookmarked templates all have new in front of their names.)
    Regards
    John



  9. #9
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    It was meant rhetorically, but now you have my curiosity going...

    How would I use this if I merely wanted to pull the values out of an existing query and merge them into a word template?

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    I will have a go at listing the steps. At some points there are proably some alternatives, but I will just give one way.

    * Make a folder 'templates' at the the current database location. Put a copy of your template in this folder. Open and replace each merge field with a bookmark.
    ( The exact number of these will vary according to the code. I for instance didn't have separate bookmarks for Town, State and Postcode, but collected all this stuff into one string and put it into a bookmark Address2)
    * Opening recordsets in code using parameter queries causes problems. My way around this is to include the sql of the query in the code as a string, rather than point to a saved query. So, open your query in sql view, copy the sql , and paste it into the code instead of my sql.

    Note that the parameter for the query needs to be outside the quote marks : "SELECT ActiveDonor.* from ActiveDonor where ActiveDonor.[DonorID]=" & Me![DonorID]
    If your parameter was a string value rather than a number you would need to enclose it in single quotes.

    Modify the declarations of variables to correspond to the fields in your query, then adjust the code that pulls the data from the recordset to corepond to your new variables, and similarly with the code that fills the bookmarks.


    Try it and see what happens.
    Regards
    John



  11. #11
    Star Lounger
    Join Date
    Mar 2002
    Location
    Chicago, Illinois, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDE Parameter Problem (Access 2000 / Word 2000

    Thanks for the directions. I'm going to have to back burner this for the time being, but once I free up some time, I'll give it a go.

Posting Permissions

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