MS-Access / Getting Started

---

Assigning an Object Variable-Set Statement

Use the Set statement to assign an object or object reference to an object variable.

Syntax

Set objectvariablename = [New] objectreference

Notes

As noted earlier, you can use object variables to simplify name references. Also, using an object variable is less time-consuming than using a fully qualified name. At run time, Visual Basic must always parse a qualified name to first determine the type of object and then determine which object or property you want. If you use an object variable, you have already defined the type of object and established a direct pointer to it, so Visual Basic can quickly go to that object. This is especially important if you plan to reference, for example, many controls on a form. If you create a form variable first and then assign the variable to point to the form, referencing controls on the form via the form variable is much simpler and faster than using a fully qualified name for each control.

You must first declare objectvariablename using a Dim, Private, Public, or Static statement. The object types you can declare include AccessObject, Application, ADOX.Catalog, ADOX.Column, ADODB.Command, ADOX.Command, ADODB.Connection, DAO.Container, Control, DAO.Database, DAO.Document, ADODB.Error, DAO.Error, ADODB.Field, DAO. Field, DAO.Field2, Form, ADOX.Group, DAO.Group, ADOX.Index, DAO.Index, ADOX.Key, ADODB.Parameter, DAO.Parameter, ADOX.Procedure, ADODB.Property, ADOX.Property, DAO.Property, DAO.QueryDef, ADODB.Recordset, DAO.Recordset, DAO.Recordset2, DAO. Relation, Report, ADOX.Table, DAO.TableDef, ADOX.User, DAO.User, ADOX.View, and DAO. Workspace object. You can also declare a variable as the generic Object data type and set it to any object (similar to the Variant data type). In addition, you can declare a variable as an instance of the class defined by a class module. The object type must be compatible with the object type of objectreference. You can use another object variable in an objectreference statement to qualify an object at a lower level. (See the examples that follow.) You can also use an object method to create a new object in a collection and assign that object to an object variable. For example, it's common to use the OpenRecordset method of a QueryDef or TableDef object to create a new Recordset object. See the example in the next section, "Object Methods."

An object variable is a reference to an object, not a copy of the object. You can assign more than one object variable to point to the same object and change a property of the object. When you do that, all variables referencing the object will reflect the change as well. The one exception is that several Recordset variables can refer to the same recordset, but each can have its own Bookmark property pointing to different rows in the recordset. If you want to create a new instance of an object, include the New keyword.

Examples

To create a variable reference to the current database, enter the following:

Dim dbMyDB As DAO.Database
Set dbMyDB = CurrentDb

To create a variable reference to the tblContacts table in the current database using the dbMyDB variable defined earlier, enter the following:

Dim tblMyTable As DAO.TableDef
Set tblMyTable = dbMyDB![tblContacts]

Notice that you do not need to explicitly reference the TableDefs collection of the database, as in dbMyDB.TableDefs![tblContacts] or dbMyDB.TableDefs("tblContacts"), because TableDefs is the default collection of the database. Visual Basic assumes that [tblContacts] refers to the name of an object in the default collection of the database.

To create a variable reference to the Notes field in the tblContacts table using the tblMy- Table variable defined earlier, enter the following:

Dim fldMyField As DAO.Field
Set fldMyField = tblMyTable![Notes]

Again, you do not need to include a specific reference to the Fields collection of the TableDef object, as in tblMyTable.Fields![Notes], because Fields is the default collection. To create a variable reference to the catalog for the current database, enter the following:

Dim catThisDB As New ADOX.Catalog
catThisDB.ActiveConnection = CurrentProject.Connection

Note that you must use the New keyword because there's no way to open an existing catalog without first establishing a connection to it. You open a catalog by declaring it as a new object and assigning a Connection object to its ActiveConnection property. The example earlier takes advantage of the existence of the Application.CurrentProject.Connection property rather than first setting a Connection object. If you already have another Catalog object open, you can create a copy of it by using

Dim catCopy As ADOX.Catalog
Set catCopy = catThisDB

To create a variable reference to the tblContacts table in the current database using the cat- ThisDB variable defined earlier, enter the following:

Dim tblMyTable As ADOX.Table
Set tblMyTable = catThisDB![tblContacts]

Notice that you do not need to explicitly reference the Tables collection of the database, as in catThisDB.Tables![ tblContacts] or catThisDB.Tables("tblContacts"), because Tables is the default collection of the catalog. Visual Basic assumes that [tblContacts] refers to the name of an object in the default collection of the catalog.

To create a variable reference to the Notes column in the tblContacts table using the tblMy- Table variable defined earlier, enter the following:

Dim colMyColumn As ADOX.Column
Set colMyColumn = tblMyTable![Notes]

Again, you do not need to explicitly reference the Columns collection of the Table object, as in tblMyTable.Columns![Notes], because the Columns collection is the default collection of a Table object.