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!

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube