DDE Parameter Problem (Access 2000 / Word 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

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%5B/url%5D

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!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.)

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Will do. Thanks for the links.

Reply | Quote

Will do. Thanks for the links.

Reply | Quote

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.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

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%5B/url%5D

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!

Reply | Quote