This course concentrates on Advanced Form and Report Design.  The course also covers the use of macros and introduces Visual Basic to the user.  The focus is on development.

The course covers ISETT Unit Standard: 116936 and 117927

Duration: 21 hours , suggested 3 day course

Target Group: This course is intended for a delegate who want to develop in MS Access.  More advanced techniques on automation, using macros and a bit of Visual Basic programming is introduced.   Delegates should already be able to create their own queries in MS Access and they must understand the concepts of a database.  Delegates who would like to be assessed for a computer qualification or delegates who wish to obtain Microsoft Office Specialist certification would benefit from attending this course.

Prerequisites: The delegates must a good knowledge of MS Access, using queries, forms and reports.  They must also be able to design their own tables in MS Access.

Method of training: Instructor led, hands-on exercises using a computer. Groups not more than 12 delegates.

Topic 1: Normalisation And Advanced Table Design

         Advanced Indexing In Tables

                             Creating Unique Combinations In Tables (Non Primary Keys)

         Properties For A Table        

Topic 2: Relationships       

         Referential Integrity

         Cascading Update And Cascade Delete Relationships       

Topic 3: Analyse Tools In Access

                    Printing The Specs Of A Table, Query and Macros

Topic 4: Advanced Query Features         

         Updateable Queries  

         Join Types In Queries

         Join Two Copies Of The Same Table In A Query   

         Displaying Unique Values In A Query         

         Handle the Error Messages In A Query      

         Union Queries using SQL

         Using The DLOOKUP Function  To Make Sure That A Query Is Still Updateable

Topic 5:          Advanced Form Design     

          Hyperlink On A Form

         Unbound versus Bound controls     

         Using Tab stops       

         Adding Navigation Buttons And Record Controls On A Form        

         Using Combo Boxes On A Form      

                             Change A Combo Box To Display The Current Record On A Form

         Display The Visual Basic Coding For Command Buttons    

         Understanding And Using Events     

         Opening A Form As A Popup Form  

         Use Visual Basic Coding To Update A Record In A Table   

         Displaying A Total From A Sub Form On A Main Form     

         Visual Basic Commands For Opening And Closing Forms/Reports

         Using A Form To Create A Filter To Print A Specific Record In A  Report

         Using Openargs When Opening Forms       

         Using Options Buttons And Check Boxes On Forms

Topic 6: Reports – Advanced Features  

         Hiding Duplicated Data On A Report

         Placing Information In More Than Column On A Report    

         Print Group Headers In The Same Line As The Details Section    

         Running Totals On Reports   

                             Using Running Totals To Generate Automatic Numbering On Reports     

         Using The DSUM Function On Reports/Forms To Sum Relevant Data     

         Restart Page Numbers On A New Group In A Report       

Topic 7: Autoexec Macro  

Topic 8: Splitting A Database       

Topic 9: Modules     

         Examples of functions that can be used for a specific purpose, using Visual Basic

Topic 10: Protect A Database By Adding A Database Password     

Topic 11: Working With Workgroups (When Access is run in compatibility mode for versions 2003 and before)

         Workgroups And Permissions         

Topic 12: Ms Access Specifications