Writing a Function that Uses Database Objects

All database objects that you can create and manipulate with Access's graphical development environment also can be created and manipulated with Access VBA code. You can use Access VBA to create a new table, to add records to the table, to define a query to select records from the table, to create a form to display the data, and then to print a report. In other words, you can use Access VBA to create an application exactly the same way you use the dBASE language or PAL. The purpose of Access's graphical development environment, however, is to minimize the need for code in applications.

The nilTestQuery function you write in the following example uses the QueryDef and Database object data types to create a query with a SQL statement and then displays that query in Datasheet view.

The nilTestQuery function contains a number of Access VBA reserved words that have been explained in the preceding sections of this chapter, but an example of one reserved word (DoCmd) has not yet been provided. Describing every Access VBA reserved word requires a book in itself, as you can see by the length of the Access VBA Language Reference if you have the Office Developer Edition. As you enter the keywords in this example, place the caret inside the keyword and press the F1 key to obtain a detailed explanation of the keyword and its use from Access VBA's Help system.

To create the nilTestQuery function, follow these steps:

1.	Close any open module; then make the Database window active, click the Modules tab, and click the New button to create a new module.
2.	You can accept the default Option Compare Database statement that Access adds to the Declarations section of a new module, or you can change the compare modifier to Text so that comparisons will not be case-sensitive. Add the Option Explicit statement to force explicit declaration of variables if you haven't specified Require Variable Declaration in the options for modules.
3.	You need to declare and assign data types to the variables that the procedure uses. Below the Option Explicit statement, type the following statements to create object variables with module-level scope: Private dbNWind As Database Private qdfTest As QueryDef
4.	To create the new procedure, type Function nilTestQuery below the preceding two statements. It's equally effective to create a subprocedure by typing Sub TestQuery.
5.	You need to specify the database, in this case Northwind.mdb, that contains the table for the query. Type the following statement: Set dbNWind = CurrentDB()
6.	You cannot have two QueryDef objects of the same name, so you need to delete the QueryDef created by multiple executions of the procedure with the Collection.Delete method. Enter the following statements: On Error Resume Next dbNWind.QueryDefs.Delete "qryTest" On Error GoTo 0 An error is generated if the QueryDef you attempt to delete doesn't exist; qryTest doesn't exist the first time you run the function, so an error occurs. The On Error Resume Next statement causes Access to disregard errors that occur in code below the statement. On Error GoTo 0 resumes runtime error checking.
7.	You need a variable to hold (point to) the definition of the query. Type the following: Set qdfTest = dbNWind.CreateQueryDef("qryTest")
8.	Now enter the SQL statement that you use to create and run the query. You can add the SQL statement as the optional second argument of the CreateQueryDef method instead of setting the value of its SQL property. Type the following: qdfTest.SQL = "SELECT * FROM Suppliers WHERE SupplierID < 11;"
9.	Execute the query and display the Recordset object created over the QueryDef in a datasheet. The Access action, OpenQuery, creates a Recordset and displays the rows of the Recordset in Datasheet view. You use the DoCmd object (a reserved word in Access) to execute actions in Access VBA. Enter the following statement: DoCmd.OpenQuery "qryTest" Your Module window appears, as shown in Figure 27.16. Figure 27.16. Code for the nilTestQuery function.
10.	Click the Compile Loaded Modules button of the toolbar or choose Debug, Compile All Modules to verify the syntax of the Access VBA statements you entered.
11.	Click the Save button of the toolbar, or choose File, Save, and give the new module a name, such as modTestQuery.
12.	Open the Debug Window and delete any existing entries. Type ? nilTestQuery(), press Enter, and then close the Debug Window. Your query result set appears in the datasheet (see Figure 27.17). Figure 27.17. The Datasheet view of the query result set created by the nilTestQuery function.