Microsoft Corporation
March 1997
Summary: Applies to Microsoft® Word 97. Learn how to access information stored in a Microsoft Access 97 database using Data Access Objects (DAO) from Microsoft Word. (14 printed pages)
Introduction
The Invoice Solution
Using Microsoft DAO 3.5
The Invoice Solution Code
Localization
Using the Solution
Conclusion
Click to copy the InvSoln self-extracting executable file.
This article describes a sample invoice solution that demonstrates how to use Microsoft Word to retrieve information stored in a Microsoft Access database. The solution includes an invoice form designed in Word with data originating from the Northwind database included with Microsoft Access.
The invoice form is a Word template that consists of ActiveX® controls and form fields arranged in table cells. As the user completes the invoice form, information (such as available products and customer addresses) is retrieved from the Northwind database. Once the invoice is complete, it can be printed or saved as a Word document.
Note This solution uses Data Access Objects (DAO) to access information in a Microsoft Access database file. The solution requires the Northwind database and the Microsoft DAO 3.5 Object Library. Both of these items are included with Microsoft Access 97. If the Microsoft DAO 3.5 Object Library is not installed, rerun Setup to install Data Access Objects for Visual Basic, located under the Data Access option. The option to install the Northwind database is also available under the Sample Databases option in Setup.
Before you take a peek at the code in the Invoice Solution template, take a look at the online invoice form, included in the Invsoln file (click on hot text above to access). In Word, open the Invoice Solution.dot file. The Invoice Solution template is an online form that uses a combination of form fields and ActiveX controls arranged in table cells.
The following table lists the elements that make up the invoice form.
| Online form item | Type | Name |
| Order Date | ActiveX TextBox | txtOrderDate |
| Spin Control beside order date | ActiveX Spin Control | spnOrderDate |
| Required Date | ActiveX TextBox | txtRequiredDate |
| Spin Control beside required date | ActiveX Spin Control | spnRequiredDate |
| Order ID | ActiveX TextBox | txtOrderID |
| Sold To and Ship To addresses | Text in table cells | Not applicable |
| Ship Date | ActiveX TextBox | txtShipDate |
| Spin Control beside ship date | ActiveX Spin Control | spnShipDate |
| Ship Via | ActiveX ComboBox | cmbShippers |
| Items ordered (quantity, product, quantity per unit, unit price, discount) | Text in table cells | Not applicable |
| Amount | Formula field in a table cell | Not applicable |
| Subtotal, Tax, Total, and Balance Due | Form fields (calculation type) in table cells | Subtotal, Tax, Total, and BalanceDue |
| Freight and Deposit | Form fields (number type) in table cells | Freight, Deposit |
| Salesperson | Drop-down form field | SalesPeople |
Figure 1. Invoice toolbar
The floating Invoice toolbar has four buttons that perform the following operations:
The sample invoice solution includes four UserForms. You can view the individual UserForms in the Microsoft Visual Basic® Editor by expanding the Forms folder in the Invoice Solution project. The following pages show each of these UserForms and describe the purpose of each form.
Figure 2. Customer Type UserForm
The Customer Type UserForm prompts the user for the customer type (new or existing). Depending on which option is selected, the user can either enter address information for a new customer or select an existing customer from the names stored in the Northwind database.
Figure 3. Customer Information UserForm
The Customer Information UserForm accepts address information for a new customer. This UserForm is displayed if the user selects New Customer in the Customer Type UserForm. The user can either enter an address on each tab or select the check box if the shipping address is the same as the customer's billing address.
Figure 4. Existing Customers UserForm
The Existing Customers UserForm displays existing customer names from the Northwind database if the user selects Existing Customer in the Customer Type UserForm.
Figure 5. Order Items UserForm
The Order Items UserForm allows the user to order products available in the Northwind database. This UserForm is displayed after the customer address information is added to the invoice. The user can select the quantity and discount rate for each product ordered. The Add to Order button adds the order to the invoice table on the online form. After the order is complete, the user clicks the Done button, the items are totaled and the tax is computed. If the user clicks the Cancel button, the user is asked if he or she wants to clear the ordered items from the invoice table.
Microsoft DAO 3.5, which is included with Microsoft Office 97, is the newest version of DAO. DAO allows you to use a programming language to access and manipulate data in local or remote databases.
Before you start a new DAO programming project, you must add a reference to the Microsoft DAO 3.5 Object Library. To do this, click References on the Tools menu in the Visual Basic Editor and select the Microsoft DAO 3.5 Object Library check box.
Note The Invoice Solution project already includes a reference to the Microsoft DAO 3.5 Object Library.
After this reference is set for your project, you can access objects in the Microsoft DAO 3.5 Object Library. The next step is to create a workspace. Microsoft DAO 3.5 supports two different database environments or "workspaces": Microsoft Jet and ODBCDirect. Microsoft Jet workspaces allow you to access data in Microsoft Jet databases such as a Microsoft Access database. ODBCDirect workspaces allow you to access database servers through ODBC, without loading the Microsoft Jet database engine. In this case, the information needed is stored in a Microsoft Access database, so you can use a Microsoft Jet workspace.
The most direct way to create a Microsoft Jet workspace is to use the CreateWorkspace method of the DBEngine object. The syntax for the CreateWorkspace method is:
Set workspace = CreateWorkspace(Name, UserName, Password, UseType)
The following public subroutine resides in the ThisDocument class module of the Invoice Solution project. The routine creates a Microsoft Jet workspace and opens the database file stored in the strDatabaseName variable (Northwind.mdb).
Public Sub Initialize()
' Initializes Jet workspace and opens database.
Dim strDb As String
Set oWorkspace = _
CreateWorkspace(Name:="JetWorkspace", _
UserName:="admin", Password:="", UseType:=dbUseJet)
strDb = _
Options.DefaultFilePath(wdProgramPath) & _
strFolderLocation & strDatabaseName
Set oDatabase = OpenDatabase(strDb)
End Sub
Routines in the Invoice Solution project call the above Initialize subroutine before retrieving information from the Northwind database. For example, the GetCustomer subroutine in the CustomerType UserForm calls the Initialize subroutine before using the OpenRecordset method to return a RecordSet object.
Private Sub GetCustomers()
' Get existing customers from Customers table in the database.
If oDatabase Is Nothing Then Call ThisDocument.Initialize
Set oRecordSet = oDatabase.OpenRecordset("Customers")
You use Recordset objects to manipulate data in a database at the record level. When you use DAO objects, you manipulate data almost entirely by using Recordset objects.
This section describes the routines in the modules and forms that are part of the Invoice Solution project.
The Invoice module includes public variable declarations for the Invoice Solution project and routines that initialize controls on the online form.
The following public constant declarations may need to be localized if the invoice solution is not intended for the U.S. version of Word. The strDatabaseName and strFolderLocation variables specify the database name and location used by the OpenDatabase method in the Initialize subroutine. The strDateFormat variable specifies the format of the dates on the invoice form (order, shipped, and required date). The last two constant declarations are used for message strings.
Public Const strDatabaseName As String = "Northwind.mdb"
Public Const strFolderLocation As String = "\Samples\"
Public Const strDateFormat As String = "mm/dd/yy"
Public Const strNotFound As String = "Address not found"
Public Const strClearAll As String = _
"Do you want to clear all " & _
"ordered items from the invoice?"
Note For more information about adapting this solution for other language versions of Office, see the "Localization" section later in this article.
The oDataBase, oWorkSpace, and oRecordSet variables are used to designate a workspace, database, and set of records so that data can be retrieved from the Northwind database. The iRowNum variable is used to keep track of the current row number as items are ordered and added to the invoice table.
Public oDataBase As Database
Public oWorkSpace As Workspace
Public oRecordSet As Recordset
Public iRowNum As Integer
The following public constant declarations are used to help make the code more readable.
Public Const iAddressTable As Integer = 3
Public Const iOrderTable As Integer = 5
Public Const iAddressTableRow As Integer = 1
Public Const iSoldToAddress As Integer = 2
Public Const iShipToAddress As Integer = 4
The constants are used instead of hard-coded numbers that refer to tables and rows in the online form. For example, the following instruction refers to the fifth table in the online form. Which table is this instruction referring to?
Set mytable = ActiveDocument.Tables(5)
By using predefined constants, the same instruction becomes more clear. The following instruction refers to the table that contains order information.
Set mytable = ActiveDocument.Tables(iOrderTable)
The Invoice module includes four public subroutines and five private subroutines.
AutoNew Subroutine
This public subroutine automatically runs when a new document is based on the Invoice Solution template. This routine initializes the global iRowNum variable used to keep track of the table row number as items are added to the invoice table (the table of ordered items). Next, the following four private routines (SetDates, GetNextOrderId, GetShippers, and GetSalesPeople) are called to initialize controls on the online form. Finally, the CustomerType UserForm is displayed.
SetDates Subroutine
This private subroutine is called from the AutoNew subroutine. The txtOrderDate, txtRequiredDate, and txtShipDate TextBox controls are set to the current date.
GetNextOrderId Function
This private function is called from the AutoNew subroutine. The last order number is retrieved from the Orders table (OrderID field) and incremented by one. The new value is set in the txtOrderId TextBox control.
GetShippers Subroutine
This private subroutine is called from the AutoNew subroutine. The shipping companies are retrieved from the Shippers table (CompanyName field) and added to the cmbShippers ComboBox control.
GetSalesPeople Subroutine
This private subroutine is called from the AutoNew subroutine. The salespeople's names are retrieved from the Employees table (FirstName and LastName fields) and added to the SalesPeople form field.
AddAnItem Subroutine
This public subroutine runs when the Add An Item button on the Invoice toolbar is clicked. This subroutine calls the GetProducts subroutine in the ThisDocument class module.
CreateNewInvoice Subroutine
This public subroutine runs when the Create New Invoice button on the Invoice toolbar is clicked. This subroutine calls the ClearInvoice and AutoNew subroutines.
ClearInvoice Subroutine
This private subroutine is called by the CreateNewInvoice subroutine. The routine removes the address information for a customer from the invoice and calls the ClearItems subroutine.
ClearItems Subroutine
This public subroutine removes order information from the orders table. If the table has more than three rows, the additional rows are deleted and text is removed from rows 2 and 3. This subroutine is called if the user clicks the Cancel button in the OrderItems UserForm and clicks Yes in the resulting message box. The routine is also called by the ClearInvoice subroutine.
At the end of the AutoNew subroutine, the CustomerType UserForm is displayed and prompts the user for the customer type (new or existing). The UserForm includes three private subroutines, described below.
cmdCancel_Click Subroutine
This private subroutine runs when the Cancel button is pressed in the CustomerType UserForm. The routine uses the Unload statement to close the CustomerType UserForm.
cmdOK_Click Subroutine
This private subroutine runs when the OK button is clicked in the CustomerType UserForm. The routine uses an If...Then...End If structure to execute different instructions, depending on whether the user selects New Customer or Existing Customer in the CustomerType UserForm. If the user selects New Customer (optNew = True), the CustomerInformation UserForm is displayed. If the user selects Existing Customer (optExisting = True), the GetCustomers subroutine is called and the ExistingCustomers UserForm is displayed.
GetCustomer Subroutine
This private subroutine is called by the cmdOK_Click subroutine if the user chooses the Existing Customer option in the CustomerType UserForm. This routine retrieves the existing customers' names from the Customers table in the Northwind database. One by one, the values in the CompanyName field (of the Customers table) are added to the lbExistingCustomers ListBox control in the ExistingCustomers UserForm.
This UserForm is displayed if the user selects New Customer in the CustomerType UserForm. The CustomerInformation UserForm is used to request address information for a new customer. The UserForm includes two private subroutines, described below.
cmdCancel_Click Subroutine
This private subroutine runs when the Cancel button is clicked in the CustomerInformation UserForm. The routine uses the Unload statement to close the CustomerInformation UserForm.
cmdOK_Click Subroutine
This private subroutine runs when the OK button is clicked in the CustomerInformation UserForm. This routine retrieves the address field values from the CustomerInformation UserForm for both the customer billing address and the shipping address fields. The strings are then passed to the ConcatenateAddress function, where they are concatenated into a single string (strAddr). Finally, the two address strings are inserted into the first row of columns 2 and 4 of the address table. The final two instructions in this routine close the CustomerInformation UserForm and call the GetProducts subroutine (in the ThisDocument class module).
This UserForm is displayed if the user selects Existing Customer in the CustomerType UserForm. This UserForm includes the routines described below.
cmdCancel_Click Subroutine
This private subroutine runs when the Cancel button is clicked in the ExistingCustomers UserForm. The routine closes the CustomerInformation UserForm.
cmdOK_Click Subroutine
This private subroutine runs when the OK button is clicked in the ExistingCustomers UserForm. The routine creates a RecordSet object using an SQL statement to return the necessary fields from the Invoices query. The FindFirst method locates the first record where the company name matches the name selected in the lbExistingCustomer ListBox, and makes that record the current record. Next, the subroutine calls the GetBillToAddress and GetShipToAddress routines. Finally, the ExistingCustomers UserForm is closed and the GetProducts subroutine (in the ThisDocument class module) is called.
lbExistingCustomers_DblClick Subroutine
This private subroutine runs if the user double-clicks an item in the lbExistingCustomers ListBox Control. The results are the same as the results of selecting an item in a list box and clicking the OK button. The routine calls the cmdOK_Click routine.
GetSoldToAddress Subroutine
This private subroutine retrieves the billing address fields from the Northwind database using the RecordSet object defined in the cmdOK_Click subroutine. The address strings are then passed to the ConcatenateAddress function, where they are concatenated into a single string (strAddr). Finally, the string is inserted into row 1, column 2, of the address table.
GetShipToAddress Subroutine
This private subroutine retrieves the shipping address fields from the Northwind database using the RecordSet object defined in the cmdOK_Click subroutine. The address strings are then passed to the ConcatenateAddress function, where they are concatenated into a single string (strAddr). Finally, the string is inserted into row 1, column 4, of the address table.
GetRegion Function
This private function sets the text stored in the theregion variable to an empty string ("") if the Region field in the Northwind database is empty.
The OrderItems UserForm is displayed after the address information is added to the invoice.
cmdAddToOrder_Click Subroutine
This private subroutine runs when the Add To Order button is clicked on the OrderItems UserForm. This routine uses the FindFirst method to retrieve the unit price and quantity per unit description from the Products table in the Northwind database. This information, along with the quantity, product name, and the discount selected in the OrderItems UserForm, is added to the table of orders. The following formula is used to calculate the price of the ordered items:
price = (quantity * unit price * (100 - discount)) / 100
The global iRowNum variable is used to keep track of the current row number as rows are added to the order table.
cmdDone_Click Subroutine
This private subroutine runs when the Done button is clicked in the OrderItems UserForm. This routine calculates the subtotal value and inserts the tax rate from the OrderItems UserForm. The second section in the online form is set to Unprotected for forms so that the freight and deposit fields can be inserted by the user.
cmdCancel_Click Subroutine
This private subroutine runs when the Cancel button is clicked in the OrderItems UserForm. This routine asks users if they really want to remove all ordered items from the invoice table. If a user clicks Yes, the ClearItems subroutine in the ThisDocument class module is called. If a user clicks No, the cmdDone_Click subroutine is called.
spnQuantity_SpinDown and spnQuantity_SpinUp Subroutines
These private routines increment or decrement the quantity by one when the up or down arrow is clicked in the OrderItems UserForm.
spnDiscount_SpinDown and spnDiscount_SpinUp Subroutines
These private routines increment or decrement the discount rate by five when the up or down arrow is clicked in the OrderItems UserForm.
This module includes three public routines called by multiple routines, as well as private routines that respond to the SpinUp and SpinDown events for Spin controls on the online form.
This public subroutine creates a Microsoft Jet workspace and opens the database name stored in the strDatabaseName variable (Northwind.mdb by default). This routine is always called before a Recordset object is returned.
Note The Initialize subroutine includes error trapping for run-time error number 3024, which occurs if the database file can't be found. Additional error handling may be necessary in other routines. For more information about error handling, refer to Chapter 14 of the Microsoft Office 97/Visual Basic Programmer's Guide or Chapter 8 of Building Applications with Microsoft Access 97.
This public function combines several address fields into a single address string. This function is called by the cmdOK_Click routine in the CustomerInformation UserForm, as well as by the GetShipToAddress and GetBillToAddress subroutines in the ExistingCustomers UserForm.
This public subroutine is called by the cmdOK_Click routines in the ExistingCustomers and CustomerInformation UserForms. This subroutine retrieves the list of available products from the Current Product List query (ProductName field) and adds the products to the lbItems ListBox control in the OrderItems UserForm. After the products are added, the quantity, discount, and tax values are preset and the OrderItems UserForm is displayed.
These private routines increment or decrement the required date, the order date, or the ship date by one day when the appropriate up or down arrow is clicked in the online form.
This section describes how to adapt the invoice solution for other language versions of Microsoft Office.
Public constant declarations in the Invoice module may need to be changed depending upon the language version of Office. For example, the constant declaration for the date format should be changed for the German release of Office.
Public Const strDateFormat As String = "dd.MM.yy"
Refer to the "Public Variable Declarations" section earlier in this article for more information.
The number formatting for the text form fields on the invoice needs to be changed for countries with currencies other than U.S. dollars ($). By default, the form fields use the following number format: $#,##0.00;($#,##0.00). To change the number format, open the Invoice Solution.dot file and unprotect the document. Double-click one of the six text form fields that use number formatting. Change the number formatting in the resulting dialog box and click OK. After you've changed the number formatting for all six fields, protect the second document section and save the template.
If the table, query, and field names in the Microsoft Access database have been localized, changes need to be made to the DAO instructions that access data in the database. For example, the following instruction in the GetCustomers subroutine refers to the Customers table in the Microsoft Access database.
Private Sub GetCustomers()
' Get existing customer names from Customers table.
If oDatabase Is Nothing Then Call ThisDocument.Initialize
Set oRecordSet = oDatabase.OpenRecordset("Customers")
If the Microsoft Access database doesn't include a Customers table, the OpenRecordset method fails. To correct this problem, the reference to the Customers table needs to be modified to match the content of the Microsoft Access database. For example, the Customers table in the Nordwind.mdb file (note German spelling) included with the German release of Office is called Kunden. The following instruction correctly references the table of customer names in the Nordwind database:
Set oRecordSet = oDatabase.OpenRecordset("Kunden")
The ConcatenateAddress subroutine combines address fields into a single address string (strAddr). The order in which the address fields are combined may need to be modified if the following address format isn't correct for the language version of Office:
Name
Address
City, Region PostalCode
Country
To use this solution, copy the Invoice Solution.dot file to your user templates folder (by default, C:\Program Files\Microsoft Office\Office\Templates). The user templates folder is set in the Options dialog box (Tools menu, File Locations tab) in Word. The template now appears in the New dialog box (File menu). Select the Invoice Solution template in the New dialog box, and then click the OK button. This action causes the AutoNew subroutine in the Invoice module to run.
This solution demonstrates how you can access information stored in an external database by using DAO. For further information about DAO, refer to the topics in Data Access Objects Help (Dao35.hlp), such as the "Data Access Objects Overview" topic. For more information about the DAO methods and properties used in this solution, look up the following topics in DAO Help:
CreateWorkspace
OpenDatabase
OpenRecordset
FindFirst
MoveNext
Fields
NoMatch
If DAO Help isn't installed, rerun Setup to install Programming Help, located under the Help Topics option.