Chapter 8

1. Chapter 8 Object-Based Programming in VBA

2. Chapter Objectives • Declare and use object variables • Create procedures that use built-in form methods • Find and manipulate data with recordset objects • Execute parameter queries in VBA • Communicate with other applications with Automation objects

3. Chapter Objectives • Find methods through the Object Browser • Create class modules • Open multiple form instances with user-defined objects • Describe the features of a well-designed, object-based application

4. The World According to Methods • Access uses objects and properties because people understand and use them in their daily lives and thus can easily apply the concepts to programming • Method • Procedure or action that belongs to an object

5. Overview of Access Methods • Access methods perform actions Figure 8-1 NewDegreeType procedure

6. Overview of Access Methods Figure 8-1 NewDegreeType procedure (continued)

7. Overview of Access Methods • Facts about methods: • RunCommand method • Similar to RunCommand macro action • DoCmd.RunCommand acCmdUndo erases the user’s most recent entry from the form • Open method • When applied to a recordset object, the recordset can access data in the table or query that is specified in the Source argument

8. Overview of Access Methods • Facts about methods (continued): • AddNew method • Attached to a recordset object • Causes a recordset to create space for a new record • Update method • Causes the recordset to write the new record to the database • Close method • Removes the reference to the recordset

9. Object Variables • To invoke a method, the associated object must be referenced • Dot and bang notations • Allow the identification of specific forms, reports, controls, tables, or fields • Can be used when invoking a method

10. Object Variables • Dot and bang notation disadvantages: • Can be long • Not very efficient when used repeatedly • Object variables • Variables used in VBA procedures that contain a pointer to the item of interest • Efficient reference to Access application objects • Must be used to reference DAO and ADO objects

11. Declaring and Setting Object Variables • Declaration statements declare variables as String, Long, Integer, and so on • VBA statements are similar to those found in many other procedural languages • In VBA, data types used in declaration statements can also include objects

12. Another Technique for Setting Object Variables • Declare a control object variable and then set the object variable to the control that currently has the focus • Control is identified by the ActiveControl property of the Screen object • Technique is used when a procedure can work with whatever control has the focus

13. Built-in Methods Related to Microsoft Access Objects • Microsoft Access objects, including forms, reports, and applications, support many built-in methods • Some objects, including forms, reports, and independent class modules, support the creation of custom methods

14. DoCmd Methods • DoCmd objects • Never manipulated by its methods, but those methods frequently manipulate another object • DoCmd methods • Exist for almost every action that can be selected from a macro’s action column • Name matches a macro action • Arguments usually correspond to the macro’s action arguments

15. DoCmd Methods Table 8-1 Commonly used DoCmd methods

16. DoCmd Methods Table 8-1 Commonly used DoCmd methods (continued)

17. DoCmd Methods Table 8-1 Commonly used DoCmd methods (continued)

18. The RunCommand Method and Its Intrinsic Constants • Action performed by the RunCommand method depends on the value of its command argument • Intrinsic constants is used to specify that value • Exist for almost all of the controls listed in the Access built-in menu bars

19. The RunCommand Method and Its Intrinsic Constants Figure 8-2 Deleting a record from frmStudentQDrops

20. Form and Control Methods • Data shown on a form might not be current because: • The recordset generated for the form may not reflect recent changes to the database • Refreshing a recordset refers to the process of updating the fields of all records currently in the recordset • Requerying a form completely regenerates the recordset

21. Form and Control Methods • Data on a form may not be current because (continued): • A calculated control may not recalculate its expression after the value of a control used in expression changes • Calculated control should be recalculated • The form may know the correct values to display, but because Access is executing other procedures, it does not have time to display correct values • Form needs repainting

22. Form and Control Methods Figure 8-3 frmCurrentStudents and txtQDrops

23. Methods that Update Displayed Data • Access automatically refreshes, recalculates, and repaints forms at Access-defined intervals • Refresh interval • Specified in the database’s Option window

24. Methods that Update Displayed Data • Refresh method • To refresh the recordset • Recalc method • To cause a recalculation of the form’s calculated controls • Repaint method • To force the system to repaint the screen • Requery method • To force Access to regenerate a recordset

25. Methods that Update Displayed Data Figure 8-4 Refreshing and requerying a form

26. Methods that Update Displayed Data Table 8-2 Form and control methods

27. Methods that Update Displayed Data Table 8-2 Form and control methods (continued)

28. Report, Module, and Application Methods • Application object • Frequently used in Access applications • Methods of the Application object: • Echo Method • Used in conjunction with DoCmd’s HourGlass method • Turns screen updating off while several changes occur to the form

29. Report, Module, and Application Methods Table 8-3 Commonly used Application object methods

30. ADO and DAO Methods • Methods related to ActiveX Data Objects (ADO) and Data Access Objects (DAO) perform many operations including: • Connecting to the database • Executing queries • Updating data not currently displayed in form • ADO is replacing DAO as the standard method for manipulating data in Microsoft products

31. Methods of the Connection Object • Almost all procedures that use ADO objects require aConnection object • Identifies and opens the database that contains the tables and queries that are used within the procedure • Establishes a connection to a database that will be manipulated in VBA code

32. Methods of the Connection Object • Methods of a Connection Object: • Close method • Shuts down the pathway to the database • Execute method • Can be used to execute queries

33. Executing Queries in VBA Through the Command Object • The Execute method associated with the Command object in VBA causes a query to run Figure 8-6 qryDeleteDeniedProspects

34. Query Parameters • Frequently, action queries triggered in VBA code use VBA parameters • Advantage of parameters is that the query will retrieve or update different data depending on the parameters’ values • To execute a parameter query in VBA, the values of the parameters must be supplied

35. Opening Recordsets • Programmers can use recordsets to locate and update data • Must declare and set a recordset object variable • Must open a pathway to the table or query that contains the data through the Open method • Recordset objects can be created from other recordset objects through the Clone method

36. Finding Records in a Recordset • Bookmark property of a recordset • A string expression that uniquely identifies the current record • AbsolutePosition property • A long integer that contains the ordinal position of the current record • MoveFirst, MoveLast, MovePrevious, and MoveNext methods • Used for simple movements to new records

37. Finding Records in a Recordset • Move method • Used in conjunction with a long integer to move the specified number of records forward • Seek method • Used to search a recordset through the primary key or indexes • Filter property • Similar to a form’s filter property

38. Editing, Deleting, and Updating Data in a Recordset • Programmers frequently use recordsets to edit, delete, and update records • Recordset methods specify how the data are to be updated • Recordsets must be opened with a LockType that permit updating of the database

39. Using DAO Recordsets • Methods of DAO recordset • FindFirst, FindNext, FindPrevious, and FindLast • Similar to ADO’s Find method • AddNew, Update, and Delete • Almost identical to their ADO counterparts • Edit • Must be executed before values in an existing record are changed

40. Using DAO Recordsets • RecordsetClone property of a form • Returns a pointer to the recordset • NoMatch property • Used to indicate whether the previous Find method failed to find a match

41. ADO and DAO Methods – Some Final Thoughts • CompactDatabase method • Associated with the JetEngine object • Makes the database smaller • Append method • Places newly created objects in a collection • Delete method of a collection object • Removes an object from the collection

42. The Object Browser • The Object Browser is available in the Visual Basic Editor (VBE) Figure 8-8 Object Browser for VBA Collection Add method

43. Automation Object Variables and Methods • GetObject function • Returns a pointer to objects in another Windows application called the source application • Automation object variable • Any variable that references an object contained in another application • CreateObject function • Used to create new applications in Access

44. Object-Based Approach to Application Development • The object-based philosophy of Access provides some guidance as to how an application should be organized • Many objects make up an Access application • Most possess the ability to test or manipulate their own features

45. Object-Based Approach to Application Development • Other objects such as collections and forms possess abilities that perform certain operations on themselves • Procedures outside the object need to know only which methods are available and what the methods do • A benefit of locating methods with objects that they manipulate is that many objects can use the same method

46. Object-Based Approach to Application Development • “Rules of thumb” for designing Access applications: • Organize procedures based on the object being manipulated • Try to build objects that can be reused • Build Objects that are not highly coupled with other objects

47. Opening Multiple Instancesof a Form • User-defined object • Class object that can be used to create other objects • Class object • A model of an object • Instances of the class object • Objects that are based on a class

48. Creating and Using Independent Class Modules • Independent class modules • Contain sub procedures and functions • Can also contain Let and Get property procedures • Treated as an object • Class modules can also invoke events • Because a class module is an object, instances of the object can be created

49. Chapter Summary • In VBA objects and properties are manipulated and created by methods • Access supplies built-in methods • You can create your own methods • Objects can be identified through the dot and bang notations • Object variables shortens notation • Object variables can be declared as any recognized ADO, DAO, or Microsoft Access object type

50. Chapter Summary • Form objects are associated with several built-in methods • Requery tells form to regenerate its recordset • Recalc, Refresh, and Repaint ensure that Access is displaying the correct data on a form • DoCmd object offers many methods for manipulating data on a form