Customize an Application with Visual Basic Callahan Chapter 2 Visual Basic for Applications VBA n The native development language for MS Office q n Access Word Excel Outlook PowerPoint VBA can q q q q q q q q q q use complex logic structures loops branching statements use variables and constants utilize functions and methods use recordsets to display process insert update delete records use transaction processing create manipulate delete database objects trap and handle errors create custom functions make calls to Windows services use automation to control other applications 1 Properties n Attributes which define an object s appearance and behavior q n all objects have properties Most properties can be set at q Design Time n q n n n use Property Sheet to set a selected object s properties Run Time code in a VBA procedure can change a property s value e g Me AllowEdits True Examples q q q q q form s AllowEdits property text box s ControlSource property label s Caption property command button s Enabled property command button s OnClick property Events n n n A state condition or occurrence on with a certain object for which you can define a response Can be caused by a user action a VB statement or triggered by the operating system Events provide an opportunity for your application to do something q n n run a macro or a Visual Basic procedure in response to an event Access has a rich event model that allows your application to respond to hundreds of events Examples q q q a command button s Click event a form s Current event a form s AfterUpdate event 2 Methods n n n Actions that can be taken on or performed by an object A function or sub that is part of an object s definition Examples q q q a text box s SetFocus method DoCmd s GoToRecord method a form s Close method Modules n Module q n an object that stores executable VBA code procedures Procedure q q contains a series of statements that performs processing or calculation two types of procedures n Function procedure q n performs processing and returns a value Sub procedure q performs processing but does not return a value 3 Anatomy of a Module General Declarations section n for declaring variables constants options that all procedures in that module can see use Option Explicit statement q q all variables used in the module must be declared before being used Tools Options Editor Require Variable Declaration n n q q adds Option Explicit statement to any new module does not affect existing modules Event handler procedures n automatically executed when the event occurs q User defined procedures n functions and subs q Types of Modules Standard Module n q q q are listed in Navigation Pane s Module group includes Function and Sub procedures that you want to be available throughout your database code in a standard module has a global scope n n Form Report Module q each form report can have own embedded code module n n q q n the code can be accessed by any object in the database are not listed in Navigation Pane hidden behind form report are listed in the VBE Project Explorer Pane contains code for handling form control events can contain Subs or Functions that relate to the form report Class Module q q q a module that contains the definition of an object a class module s procedures define object s properties and methods are listed in Navigation Pane s Module group 4 Viewing the Visual Basic Code Behind a Command Button n One way to view a command button s code 1 2 n Another method 1 2 n right click the command button in Design View click Build Event select the object in Design View click event property s Build button VBE code window Overview q q q q q q q Object combo box Proc combo box keywords in blue comments in green errors in red syntax check Intellisense Getting Help n select keyword press F1 Viewing the Visual Basic Code Behind a Command Button n Sub Private Sub AddRecord Click On Error GoTo Err AddRecord Click DoCmd GoToRecord acNewRec n End Sub Exit AddRecord Click Exit Sub Err AddRecord Click MsgBox Err Description Resume Exit AddRecord Click n Error Handling q End Sub Callahan Chapter 7 5 Viewing the Visual Basic Code Behind a Command Button Object n a combination of code and data that can be treated as a unit e g a form a control q q Method n Arguments n information passed to a procedure a constant variable or expression why do we need two commas q q q Constant n a named item that retains a constant value q Constant Private Sub AddRecord Click On Error GoTo Err AddRecord Click an action that can be taken on or performed by an object q Method Object DoCmd GoToRecord acNewRec Exit AddRecord Click Exit Sub Err AddRecord Click MsgBox Err Description Resume Exit AddRecord Click End Sub Arguments Modifying a Command Button Created Using the Wizard n What was the button s hotkey n What functionality does a hotkey provide n How define the hotkey n Practice Time q Artie s List database frmRestaurant n n n define a hotkey to each existing command button test that each hotkey works save frmRestaurant 6 Making a Form Read Only by Default Allow Edits property n a form property that controls whether or not the form can be used to change field values in existing records q when AllowEdits is set to No q n n the Ribbon s Save Record button is disabled Shift Enter has no effect Practice Time n Artie s List frmRestaurant Set AllowEdits to No Test that it no longer allows edits q q q Creating a Command Button without a Wizard n n n Disabled Control Wizards added Edit Record button Build Event Procedure Manually typed its Click event procedure Private Sub EditRecord Click Make the Contacts form editable Me AllowEdits True FirstName SetFocus Move to the FirstName field End Sub n Practice Time q q q q q Artie s List frmRestaurant Disable Control Wizards place cmdEditRecord button align space Rename the Restaurant text box as txtRestaurant Build event procedure to allow edits and setfocus to txtRestaurant Test and save frmRestaurant 7 Create a Command Button to Save the Current Record n Control Wizard created event procedure code to handle command button s Click event Private Sub SaveRecord Click On Error GoTo Err SaveRecord Click DoCmd RunCommand acCmdSaveRecord Exit SaveRecord Click Exit Sub Err SaveRecord Click MsgBox Err Description Resume Exit SaveRecord Click End Sub n We added cmdSaveRecord to Artie s List frmRestaurant in Callahan Chapter 1 WebCast q Practice Time test to be sure it works since we ll be enhancing it later Creating Event