Share this Page URL

Chapter 17. Using Visual Basic to Manage... > Using Visual Basic to Work with Data... - Pg. 593

Using Visual Basic to Manage Data 4. 593 Place the code that builds the SQL statement in a Select Case statement (or an If ...Then...Else state- ment) so that the SQL statement's WHERE criteria changes depending on values selected in the form. For example, the Select Case statement could correspond to the options in an option group. Using the OpenReport method of the DoCmd object, open the report in Design view and assign the SQL statement (you would probably use a string variable to represent the SQL statement) to the report's RecordSource property. Close and save the report using DoCmd.Close , and then use the OpenReport method again to display the report in the Preview window. 5. 6. Quick Check Q. In the previous procedure, why do you need to open a report in Design view first? A. You need to open a report in Design view to set its RecordSource property. You can't set this property when a report is open in Preview view. Q. Do the controls on the form used to provide the criteria included in the WHERE clause need to be bound to data fields? A. No, the form's controls should not be bound. The controls on the report should be bound (through the control's Control Source property) to the fields whose data is displayed in the controls. Using Visual Basic to Work with Data in Office Applications In Chapter 5 we introduced Office Automation. By using Automation, you can write Visual Basic code in Access that performs operations in another Microsoft Office application. (You can also use Automation within Microsoft Word, Excel, or Outlook, for example, to work with data in Access or another Office application.) The example of Automation we provided in Chapter 5 described how to use the Outlook object model to create and send an e-mail message from Access. In this section, we'll look at three other examples of how you can use Visual Basic to work with the data you've stored in an Access database to serve information needs you might have in another Office application. We'll describe how to add a contact to Outlook from Access; how to create and open an Excel spreadsheet, populating the spreadsheet with data from the database; and how to use data displayed in a form as the basis of a PowerPoint presentation. To perform complex oper- ations within another application, you need to know the ins and outs of that application's object model. The examples we present, although not extensive, show you the basics of working with the object models in Outlook, Excel, and PowerPoint and show that you can carry out useful operations with relatively little code. Visual Basic's IntelliSense feature and the Visual Basic online help provide good assistance in identifying the objects, properties, and methods you need to work with. Binding to Objects and Setting References in Code When you write code that relies on an object from a different application's object model (or type library, which is the term used in the References dialog box in the Visual Basic Editor), Visual Basic resolves whether the object is of a known type and whether the object's methods and properties have been used correctly. Visual Basic makes these determinations (the process is known as binding) when the code runs ( late binding ) or when the code is compiled ( early binding ). Late binding can cause code to execute more slowly because Visual Basic is required to look up an object and its methods and properties whenever it runs a line of code that refers to the object. To use early binding, you must have a reference in the Visual Basic project to the type library you're using (for example, Microsoft Outlook 11.0 Object Library) and declare an object variable as a specific object type (for example, ContactItem for an Outlook contact) rather than using the generic type Object. Because late binding doesn't require a reference to a type library, using it has an advantage if your code is designed to work with a different version of an application (an earlier version of Microsoft Outlook or Excel, for example) or in a situation in which you aren't aware of which references have been set (when you are running the code on a different user's computer, for example).