1	Access 2002 Programmability Features Ken Getz MCW Technologies, LLC keng@mcwtech.com
2	Me.About Senior Consultant with MCW Technologies Technical editor for Access-VB-SQL Advisor Contributing editor for Microsoft Office and VBA Developer Author of several developer’s books on Access, VB, and VBA VB6 and Access video training for AppDev (www.appdev.com) www.developershandbook.com E-mail: keng@mcwtech.com
3	Materials in this Session Derived from examples in Access 2002 Developer’s Handbook Paul Litwin, Ken Getz, and Mike Gunderloy Sybex, 2001
4	Agenda Lots of Little Features Using the FileDialog Object Working with PivotChart/Table Views Managing Printer Settings Round-Tripping to Access 2000
5	New Events MouseWheel event Allows you to trap and handle the mouse wheel Tons of new events for PivotTable and PivotChart views Undo event (Forms and controls) Access 2000 added Dirty event Access 2002 adds Undo event (and Dirty event for some controls) Try frmCustomer
6	Report Additions Can open reports hidden, finally! OpenArgs applies to reports Reports handled more like forms Modal, popup, borderstyle AutoCenter and AutoSize
7	Combo/List Boxes AddItem and RemoveItem methods Finally, no more list-filling callback functions Must set RowSourceType to "Value List" Works only for unbound controls No Sorted property, however Try frmTestFileDialog
8	Adding an Item Call AddItem method Multiple columns? Put ";" or "," between column values What if your data includes those?
9	Removing an Item Call RemoveItem method Pass in index of item to be removed Can also pass in value of row to be removed
10	Clearing the List No Clear method How do you clear the list? Clear the RowSource property:
11	Agenda Lots of Little Features Using the FileDialog Object Working with PivotChart/Table Views Managing Printer Settings Round-Tripping to Access 2000
12	Office’s File Dialog Windows provides its own file open/save common dialog This isn’t it! The Office team provides its own wrapper around the common dialog Undocumented how to use it in previous versions of Access
13	Office’s FileDialog Object Now available as part of the MSO.DLL ActiveX component Set a reference to Microsoft Office 10.0 Object Library Use Tools\|References It’s a strange object! Try frmTestDialog
14	Why is FileDialog Strange? Cannot instantiate a new instance Only a single instance allowed The FileDialog object requires a parameter when you reference it Must specify the type of dialog you want: File picker, Folder picker, Open, Save As Choose from one of four constants
15	Using FileDialog Call the Show method Returns True if you select a file or files Returns False if you cancel it Investigate SelectedItems property Returns a collection of selected items Contains strings—one per selected file or folder SelectedItems not cleared until the next time you call Show method
16	Using FileDialog
17	FileDialog Properties DialogType (read-only) InitialView ButtonName Title AllowMultiselect InitialFileName Specify initially selected file, or initial folder
18	Specifying Filters Can specify one or more filters for the “Files of Type” combo box Filters property allows you to create collection of FileDialogFilter objects You supply two properties for each: Description Extensions Can specify FilterIndex property to select initial filter
19	Working with Filters Count property Add, Clear, Delete, Item methods
20	After the Show Method Iterate through the SelectedItems Collection of strings From the demo:
21	What's Next? FileDialog object extremely limited Very few options Unless you need Office FileDialog's specific features, use Windows FileOpen dialog box?
22	Agenda Lots of Little Features Using the FileDialog Object Working with PivotChart/Table Views Managing Printer Settings Round-Tripping to Access 2000
23	New Form Views PivotTable and PivotChart added as form views Get rid of Microsoft Chart! Much like using the PivotTable and Chart controls from Office Web Components, in Access 2000 But much easier – all the data binding is done for you Binding those controls to forms was quite tricky
24	Using the New Views PivotTable and PivotChart views take up the full form Want other controls too? Use subform containing new view, hosted on main form containing other controls See frmPivotTableObjects
25	Programming the New Views Much like programming the OWC controls All examples using OWCs work with little change Object models changed slightly for new version Lots of new features added
26	Working with Charts Use ChartSpace property of a form Charts collection provides reference to one or more charts on the form
27	Chart Events Unlike Microsoft Chart… PivotChart exposes full event model Can run code as mouse clicks or moves over elements Try frmChartEvents Move mouse over points Click on legend elements
28	What Is That Object? How do you know what kind of object the mouse is over? Legend? Points? Title? Use RangeFromPoint method in MouseMove event Returns the range the mouse was over Use TypeName to determine its type
29	Determining Types Use code like this to know what you moved over or clicked on:
30	Working with Chart Properties Tons of properties to work with Check object browser for more details See frmDemoPieChart for example
31	Working with PivotTable Full control over every UI feature Can manage data Can create and manage layout See frmPivotTableObjects
32	Not in All Views… None of these programmatic features work unless the view is correct Check CurrentView property of form New constants added to support PivotTable view and PivotChart view Only use new programmatic features if the view is correct
33	Agenda Lots of Little Features Using the FileDialog Object Working with PivotChart/Table Views Managing Printer Settings Round-Tripping to Access 2000
34	Finally, a Printers Collection! After four versions… No more API calls to muck with printers Application.Printers provides collection of Printer objects Based on VB6's Printer object With a few twists
35	Retrieve Printer Information If you want to fill a list box with a list of all available printers:
36	Set Access' Default Printer Set the Application object's Printer property to the selected printer
37	Putting it Back To set Access' default printer to be Windows' default printer Set Application.Printer to Nothing Try frmSelectDefaultPrinter
38	Working with Objects Forms and reports also support a Printer object Used when you indicate you want to print the object to a specific printer Can set the Printer property of a form or report to a specific printer, then print Better to have object print to default printer, and change Access' default instead Example later…
39	Printer Object Properties Print Layout properties: Bottom/Left/Top/RightMargin ItemSizeHeight/Width Row/ColumnSpacing ItemsAcross, ItemLayout DefaultSize, DataOnly Try frmPrintLayout
40	Printer Object Properties Printer Name properties: DeviceName DriverName Port Try frmSetPrintDestination
41	Printer Object Properties Print Job Properties: ColorMode Copies Duplex Orientation PaperBin PaperSize PrintQuality Try frmDevMode
42	Setting Print Destination Two choices for individual objects: Change object's output destination Difficult to do, in general Change Access' default printer Requires that object be set up to print to default printer
43	Change Object's Printer Steps: Open object Set new print destination Make sure object is selected DoCmd.PrintOut or DoCmd.OpenReport Doesn't require opening in Design view (so it works in MDE/ADE) Requires handling object twice Can't do the work from the Open event
44	Change Object's Printer
45	Change Access' Printer Steps: Set Access' Printer object to new destination Print object Set Access' Printer object to nothing
46	Change Access' Printer
47	Change Access's Printer Will only work if object is set up to print to default printer Investigate the UseDefaultPrinter property of an object Can set the property at runtime to override saved behavior But that leaves you handling the object twice again
48	Printer Capabilities? How do you know if a printer: Supports color? Supports duplex? Supports a specific paper size? And so on… Access doesn't provide this info Requires API See frmDevCaps for demo
49	Agenda Lots of Little Features Using the FileDialog Object Working with PivotChart/Table Views Managing Printer Settings Round-Tripping to Access 2000
50	Round Trips to Access 2000 Should work, in theory Stay in Access 2000 file format, and changes are OK What if you use programmatic features that don't work in Access 2000? Not so OK
51	Determine the Version Need to be able to determine the version of Access that's running Access 2002 provides Application.Version Not supported in Access 2000 Use Syscmd instead:
52	Using Access 2002 Objects Can't use Printer object in Access 2000 Can't use new methods/properties of objects in Access 2000 In either case, code won't compile The answer: use late binding It's ugly, but it works
53	Using Late Binding Instead of this: Use this:
54	Using Late Binding Instead of this: Use this:
55	Branch Based on Version Use the version number to call new code (late bound) or old code:
56	What to Take Away? Lots of new programmability features Check object browser when you install Check new events, properties, and methods of existing objects Look for new objects Don't forget the Office type library, as well Lots of good stuff in there!
57	Questions? Please don’t forget your evals Access 2002 Programmability Features Ken Getz keng@mcwtech.com