In this post I will be explaining how the code for the Word module works.
Create a module called modWelcomeLetter in Access. Add this code:
Option Compare Database
Option Explicit
Public Sub OpenWelcomeLetter(lngId As Long)
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim lngGuestID As Long
Dim strGuestName As String
Dim strHotelName As String
Dim strManagerName As String
lngGuestID = DLookup("[GuestID_FK]", "tblBookings", "[BookingID]=" & lngId)
strGuestName = DLookup("[Title]", "tblGuests", "[GuestID]=" & lngGuestID) & " " & _
DLookup("[FirstName]", "tblGuests", "[GuestID]=" & lngGuestID) & " " & _
DLookup("[LastName]", "tblGuests", "[GuestID]=" & lngGuestID)
strHotelName = DLookup("[HotelName]", "tblSettings")
strManagerName = DLookup("[ManagerName]", "tblSettings")
DoCmd.Hourglass True
'Opens Word template and adds fields into template
Dim WordObj As Word.Application
Dim strWordPath As String
Set WordObj = GetObject(, "Word.Application")
Dim oRng As Word.Range
'If an error is thrown we will use open another instance of Word
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If
'Ensure Word is visible
WordObj.Visible = True
'gets word path from tblSettings
strWordPath = DLookup("[WelcomeLetterPath]", "tblSettings")
WordObj.Documents.Add _
Template:=strWordPath, _
newtemplate:=False
'add info to bookmarks
WordObj.Activate
With WordObj
Set oRng = .ActiveDocument.Bookmarks("GuestName").Range
oRng.Text = strGuestName
.ActiveDocument.Bookmarks.Add "GuestName", oRng
Set oRng = .ActiveDocument.Bookmarks("HotelName").Range
oRng.Text = strHotelName
.ActiveDocument.Bookmarks.Add "HotelName", oRng
Set oRng = .ActiveDocument.Bookmarks("ManagerName").Range
oRng.Text = strManagerName
.ActiveDocument.Bookmarks.Add "ManagerName", oRng
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.GoTo what:=wdGoToBookmark, Name:="GuestName"
End With
WordObj.Activate
WordObj.Selection.WholeStory
WordObj.Selection.Fields.Update
Set WordObj = Nothing
DoCmd.Hourglass False
On Error GoTo 0
End Sub
Below I explain how the code fits together.
Public Sub OpenWelcomeLetter(lngId As Long) On Error Resume Next Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSql As String Dim lngGuestID As Long Dim strGuestName As String Dim strHotelName As String Dim strManagerName As String
-Public Sub OpenWelcomeLetter(lngId As Long) The sub-procedure has a required argument (lngID) that will be the ID number of the booking we need to get the guest name for.
-On Error Resume Next Although it is generally preferable to handle errors using the On Error Goto ErrorHandler style, the resume next part plays an important role here (as you will see below).
lngGuestID = DLookup("[GuestID_FK]", "tblBookings", "[BookingID]=" & lngId)
strGuestName = DLookup("[Title]", "tblGuests", "[GuestID]=" & lngGuestID) & " " & _
DLookup("[FirstName]", "tblGuests", "[GuestID]=" & lngGuestID) & " " & _
DLookup("[LastName]", "tblGuests", "[GuestID]=" & lngGuestID)
strHotelName = DLookup("[HotelName]", "tblSettings")
strManagerName = DLookup("[ManagerName]", "tblSettings")
DoCmd.Hourglass True
-lngGuestID = DLookup(“[GuestID_FK]”, “tblBookings”, “[BookingID]=” & lngId) We first need to look up the GuestID from the Guest table using the passed lngID argument.
-strGuestName = DLookup(“[Title]”, “tblGuests”, “[GuestID]=” & lngGuestID) & ” ” & _
DLookup(“[FirstName]”, “tblGuests”, “[GuestID]=” & lngGuestID) & ” ” & _
DLookup(“[LastName]”, “tblGuests”, “[GuestID]=” & lngGuestID)
Although the strGuestName statement looks complicated, all we are doing is looking up the Title, FirstName and LastName from the GuestID we obtained and then concatenating them (stringing them together).
-strHotelName = DLookup(“[HotelName]”, “tblSettings”) The HotelName is stored in tblSettings and we use the Dlookup function to retrieve it.
-DoCmd.Hourglass True Opening up Word and modifying it from Access can take a lot of overhead and consequently may take some time. It is prudent to set the HourGlass property of the mouse pointer to true to alert the user that the program hasn’t crashed. This will cause the mouse pointer to change from an arrow to a spinning circle (Windows 7).
'Opens Word template and adds fields into template Dim WordObj As Word.Application Dim strWordPath As String Set WordObj = GetObject(, "Word.Application") Dim oRng As Word.Range
-Dim WordObj As Word.Application This is the code where we begin to manipulate Word from Access. Here we instruct Access to free some memory space for a Word Application. We will be referring to this memory slot as WordObj.
-Set WordObj = GetObject(, “Word.Application”) Here we are telling Access “Ok, so let us go and get an open Word Application and assign it to WordObj”. A Word application is basically Microsoft Word. So, why not, I hear you say, use the term Word doc instead of Word Application? Well, you might have 3 open Word documents but they will be using just ONE Word Application. Think of it as the master program.
But wait! What if there is no Word application? Will the application crash and burn? No. Of course not! Remember that On Error Resume Next statement at the start? Well this is precisely why we put that there. An Error will be generated but the application will continue all the same…
'If an error is thrown we will open another instance of Word
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If
'Ensure Word is visible
WordObj.Visible = True
-Set WordObj = CreateObject(“Word.Application”) If an error has been generated (no existing Word application), then one will need to be opened, right? So, let’s create one.
WordObj.Visible = True We also need to make sure we can see the Word Application. Although this seems obvious, sometimes with automation (Excel for example) you may want to keep the Application invisible whilst you obtain information and then close it.
'gets word path from tblSettings
strWordPath = DLookup("[WelcomeLetterPath]", "tblSettings")
WordObj.Documents.Add _
Template:=strWordPath, _
newtemplate:=False
'add info to bookmarks
WordObj.Activate
Now we have a Word Application, we need to open a document inside it. We get the path to the Word document from tblsettings and …
WordObj.Documents.Add Template:=strWordPath, newtemplate:=False
…open said template.
WordObj.ActivateWe now need to tell Access that we are going to be working on the Word document now so will be needing to activate it.
With WordObj
Set oRng = .ActiveDocument.Bookmarks("GuestName").Range
oRng.Text = strGuestName
.ActiveDocument.Bookmarks.Add "GuestName", oRng
Set oRng = .ActiveDocument.Bookmarks("HotelName").Range
oRng.Text = strHotelName
.ActiveDocument.Bookmarks.Add "HotelName", oRng
Set oRng = .ActiveDocument.Bookmarks("ManagerName").Range
oRng.Text = strManagerName
.ActiveDocument.Bookmarks.Add "ManagerName", oRng
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.GoTo what:=wdGoToBookmark, Name:="GuestName"
End With
This is the code where we add the data we want to the bookmarks.
-Set oRng = .ActiveDocument.Bookmarks(“GuestName”).Range We are telling that we want the oRng variable to be equal to the value of the Bookmark named GuestName.
-oRng.Text = strGuestName Here we change the text within the bookmark to whatever strGuestName happens to be.
-.ActiveDocument.Bookmarks.Add “GuestName”, oRng At this point we have essentially overwritten the bookmark (which isn’t a problem in itself but if we have any fields in Word that reference that particular bookmark they won’t work as there won’t be anything to reference). So, we need to re-declare the bookmark.
-.Selection.Fields.Update Here we update the fields in the Word document.
-.Selection.GoTo what:=wdGoToBookmark, Name:=”GuestName” Here we attempt to place the curser at the bookmark GuestName so that when the Word doc opens up the whole text is not highlighted.
Set WordObj = Nothing DoCmd.Hourglass False On Error GoTo 0 End Sub
-Set WordObj = Nothing When creating an object it is good programming practice to destroy it before terminating the procedure.
-DoCmd.Hourglass Makes the mouse pointer turn back into an arrow.
-On Error GoTo 0 This resets the error handling.
Phew! This code may seem complicated but if you break it down you will see that it is all there for a reason.
Happy automating!
Thanks very much for everything!!!! A very big fan and do enjoy the one day free trial.
Your welcome Michael. Glad you are enjoying the site.