The last two lines, after the end of the document are necessary due to the way the Word cursor movement statement Selection.MoveDown works. (It's worth mentioning that the meaning of the word "cursor" is very different in Access and Word. It's a good example of how you have to be aware of the way a hosting environment works to use VBA successfully.) One of the best ways to discover exactly how to code programs like this is to trace through the code as you develop it using the Debug tool. Single step through the program and display the results in the Immediate window of the Visual Basic editor.
oApp.ActiveDocument.Close
oApp.Quit
Set oWord = Nothing
Set oApp = Nothing
Exit Sub
The end of the program releases resources and closes Word. Although some texts say this is an optional clean up step, in this case, it's more than that. Without it, running the program twice will result in an error because Access VBA doesn't release the reference to the Word objects and this interferes with the automation on the second execution.
This is also the reason that all of the statements that reference the Word object are fully qualified with the object name oApp. The program will run without the qualification because VBA searches it's reference objects for the right one and uses it. But it doesn't release the reference if it's not fully qualified at the end of the program. As a debugging note, Microsoft provides an excellent database of this type of problem at MSDN.Microsoft.com. This particular problem is documented here.
The end result of executing the program updates the table like this. You can download the Access database (which includes the VBA code) and the Word Document here.
Return to the first page >
VBA - Accessing Access > Page
1,
2,
3,
4,
5,
6
| 