Access Developer 1

00. Intro (4:14) 01. Intro to Access VBA (8:42) What is VBA Visual Basic for Applications Visual Studio Visual Basic .NET Benefits over Macros 02. Our First VBA Code (12:51) Hello World Turn off Project Explorer Turn off Properties Window Turn off Immediate Window MsgBox Command Parameters Optional Parameters Multiple Parameters Keyword Statement Procedure Subroutine Function Module 03. DoCmd.OpenForm (12:46) Open Form Command Arguments List Where Condition Open Specific Record Duplicate Code Preview of Creating Subs 04. Subs with Multiple Commands (5:59) Archive Contacts Button Docmd.SetWarnings Docmd.OpenQuery MsgBox Command 05. Add Product Button (12:01) If Then Statement End If Else, ElseIf Docmd.GoToControl Docmd.GoToRecord acNewRec SetValue in VBA 06. AfterUpdate Event (10:19) Add New Customer Button After Update Event Always Use Event Procedures Boolean Values TRUE is Assumed 07. Review (3:53)

Access Developer 2

00. Intro (2:20) 01. Calculator 1 (20:05) Naming Buttons String Concatenation VAL() Function Change Button Name, Edit Code Add, Subtract Buttons Multiply, Divide Buttons Square Root, Exponents Nth Root Debugger Divide by Zero Error On Error Resume Next If Then Else Exit Sub 02. Calculator 2 (12:59) Create Private Sub Parameters to a Sub Right Click > Definition for Sub Select Case Statement End Select Proper Indenting Tab / Shift Tab Multiple Lines 03. Commission Form 1 (27:00) Sales Rep Combo Box Commission Rate in Columns AfterUpdate Event to Copy Rate Docmd.GoToControl Combo.DropDown Calculate Commission Button Create a Function Return a Value Boolean Values Subs vs. Functions 04. Commission Form 2 (15:57) Comments VB Line Continuation _ MsgBox vbInformation Warning if Sale Amount $0 DoCmd.RunSQL INSERT INTO Statement Set to Null after logging 05. Review (4:18)

Access Developer 3

00. Intro (3:07) 01. Variables (22:12) Docmd.OpenTable DLOOKUP Exit Sub Line Continuation Character Variables DIM ID As Long On Error Resume Next On Error Goto 0 Initializing Variables MsgBox as a Function vbYesNoCancel vbYes, vbNo, vbCancel 02. Random Numbers (9:33) Rnd Randomize Int() Const - Constants 03. For Next Loops (21:47) Looping Math Quiz Set NumLoops For Next InputBox Format Function, Percent Format Function, 0.0% Data Types in Access VBA Boolean Integer, Long Integer Single, Double Currency Date, Date Time String Variant 04. Quick Add Sales Rep (21:10) Add Link on Commission Form InputBox First, Last, Com Rate DCount, DMax, CDbl Run Another Objects Event 05. Review (5:43)

Access Developer 4

00. Intro (3:09) 01. Add Notes to Order Details (22:15) DLOOKUP Long Text Memo Notes Field in OrderDetailT Add to OrderDetailF Make Size Change in OnGotFocus Height, Width Properties Change Detail Section Height Variable Scope - Sub vs Form Form Scope Variable to hold Height, Width OnDblClick Event to Grow 02. Commissions by Order Amount 1 (22:33) Modify CommissionT Base Commission on Order Employee Commission Table Set up Brackets for Commission Ranges Fix Sales Rep Combo Box Query to Link Orders, Commissions, Cust Order Picker Combo Box 03. Commissions by Order Amount 2 (43:50) Change Order Combo to List Box SQL Statement to Load List Limit by Sales Rep Query Employees w Unpaid Commissions Limit Employee Combo to Unpaid Only Lookup Order Total for Selected Order Look that up in Commission Table Log to Table with SQL INSERT INTO 04. Review (4:06)

Access Developer 5

00. Intro (3:58) 01. Pay Commissions 1 (15:21) Add Commission Check ID to Com Table Create Commission Check Table Commission Select Query Commission Check Employee Query Commission Check Form Combo Box to Select Employee 02. Pay Commissions 2 (20:38) Commission List Box Is Selected Copy Between Two List Boxes Drag and Drop Discussion Click and Drag Create ComList2 SQL UPDATE to Select Items Totals for each List Box DSUM Function 03. Pay Commissions 3 (20:17) MsgBox vbYesNoCancel vbYes SQL INSERT Check into Table Add EmployeeID to CommissionT Database Theory v Practice Update CommissionT for Changes 04. Printing Checks 1 (15:23) Convert Currency or Number to English SpellNumber Function Create a Global Module Look up Largest Check Number in Table Create New Check Number 05. Printing Checks 2 (20:30) IsPrinted Create Check Report Blank Laser Printer Checks Process Current Check Print All Checks Mark Printed DoCmd.OpenReport acViewPreview OnNoData Report Event Mark ALL Commissions Selected 06. Review (4:27)

Access Developer 6

00. Intro (4:36) 01. Converting Macros to VBA (29:41) Quotation or Invoice in Report Examine old Macro from Advanced 2 Label.Caption Property Detail_Format Section Report Detail Build Event TRUE assumed in IF statements VB Editor Auto Capitalization vbRed, vbBlue ForeColor Property Quote or Invoice Toggle Button Global Module for IsQuotationCode Enabled, Locked Properties IsPaid Password Code to VBA Convert Macros to VBA 02. Enhanced Timer Form (16:35) OnTimer Event Timer Interval One Second Ticks Me.TimerInterval IsPaused Checkbox Checkbox to Control Events Firing Specifying Timer Interval Text Box 03. Cascading Combo Boxes (9:47) Dynamic Combo Boxes Control Wizard Not Starting Product Category SQL Rowsource Select Category, Products Filtered 04. Automated Backups (34:26) Trusted Locations Backing Up Critical Tables You Should Have Separate Backup Use This to Backup Critical Data DoCmd.TransferDatabase Backup With Timestamp Current Date Time Field Last Run Field Run Every X Minutes or Hours 86400 Seconds FileCopy Permission Denied CopyFileA Windows API Replace Function 05. Review (3:44)

Access Developer 7

00. Intro (3:24) 01. Partial Payments 1 (30:54) Create PaymentT Create PaymentMethodT Create PaymentF SUM in Form Footer PaymentTotal DSUM Payment Total Popup, Modal Forms Refresh Subforms Forms!FName!Subform.Form!Field Notation 02. Partial Payments 2 (13:36) vbExclamation vbCritical vbInformation Don't allow Payment if PAID Don't allow Payment if QUOTATION Add Payments to Invoice Report If No Payments, Handle NULL Value NZ Function 03. Partial Payments 3 (22:29) Add Horizontal Line over Payments Hide Payments, Due if Null or Zero CTRL-Y to Delete Line of Code Visible Property Section Header Build Event Code Can Grow, Can Shrink for Section Header Can Grow, Can Shrink for Text Boxes Labels Don't Shrink IsQuotation Not In Subform Duplicate Fields in Both Tables Specified Field Could Refer Refer To More Than One Table Add IsQuotation to OrderDetailQ 04. Review (2:35)

Access Developer 8

00. Intro (3:30) 01. Payment Mods 1 (23:48) Default to Amount Due Combo DropDown Method Add Amount Due to PaymentF Refresh Amount Due Refresh Total Payments On Unload Event Mark Order Paid Check for Overpayments 02. Payment Mods 2 (27:25) Don't Allow Edits if Paid Me.AllowAdditions Me.AllowEdits Me.AllowDeletions Manager Override Password to Edit Create a Public Global Function IsManager Function Returning a Value from a Function Replace Password Elsewhere Globally Search All Code Search Code Entire Project 03. Search As You Type (23:11) Create Search Query On Key Press Event KeyPress KeyAscii What is ASCII? CHR Function OnChange Event SelStart Len, Nz 04. Review (2:32)

Access Developer 9

00. Intro (6:31) 01. Miscellaneous (27:19) Add Search Form to Main Menu Open Customer from Search Form Show/Hide Manager Button If Paid Delete A Payment Problem Form Current Event from Top Menus Mark Unpaid if Payment Deleted Lock or Unlock if Paid Unpaid Enabled v. Locked Properties Properties of Subforms 02. Test Taker 1 (38:48) Build Tables Table Relationships Enforce Referential Integrity Multiple Choice v. Fill In Blank 03. Test Taker 2 (20:07) Create Forms for Data Entry Department, Class, Test Forms 04. Test Taker 3 (19:00) Question Form Set Caption On Open Form with a Subform DefaultView Continuous Forms Problem 05. Test Taker 4 (37:41) Begin Taking Test Combos to Select Student, Test Disable on Selections Begin Test Button Check for Questions on Test Create Test INSERT INTO SQL Statement Create a TestResult Record DMAX to Look Up ID 06. Review (2:42)

Access Developer 10

00. Intro (2:51) 01. Test Taker 5 (22:09) Figure Out Which Question to Ask TestResultQ Show Unanswered Questions for Test QuestionsCurrentTestQ AnswersCurrentTestQ MissingAnswerQ Set Order Default to Max +1 02. Test Taker 6 (9:40) Get the Question Fields to Display Q&As Add Scroll Bars to Text Box Display Next Question Sub Required Order Value Validation Rule >0 03. Test Taker 7 (36:26) Answer Given Text Box Answer List Box Determine if Multiple Choice Display Text or List Box Check Answer for Correctness NZ with False StrComp() if Case Sensitive 04. Test Taker 8 (15:31) Calculate Grade Store in TestResultT ROUND Function 05. Review (3:30)

Access Developer 11

00. Intro (5:39) 01. Test Taker 9 (32:21) Hide Begin Test Button Display Question 1 of X Take This Test Button on TestF DoCmd.Close acForm Options acSaveYes, acSaveNo, acSavePrompt IsLoaded Check if Form Is Loaded CurrentProject.AllForms Cascade Close Child Forms OnCurrent Close and Reopen Child 02. Test Taker 10 (38:44) Tracking Test Start End Times Answer Start End Times Display Timer on Form Timer Interval RGB Function and Colors Max Time to Take Test Calculate Elapsed Time DateDiff 03. Universal Dialog Box 1 (23:52) Custom Prompt Enhanced MsgBox  OK Button Modal, Popup Create a Global Public Module uDialog Sub PromptLabel.Caption Property uDialog.Title Property of Form Optional Parameters BackColor RGB With / End With FontSize Parameter 04. Review (3:54)

Access Developer 12

00. Intro (5:01) 01. Universal Dialog Box 2 (21:28) OK, Cancel, Yes, No Buttons Turn Sub into Function Global Variables While Loops While Wend DoEvents Return Function Value 02. Custom Form Positioning (23:49) Height and Width of Forms Twips = Inches * 1440 Twentieth of an Inch Point Design Time Width of Form Me.Width Runtime Width of Form Me.InsideWidth OnResize Event WindowHeight, WindowWidth - Read Only Me.Detail.Height Me.InsideHeight There is no OnMoved event You can use a timer event OnTimer Twips Round to 15 Me.Move Docmd.MoveSize Center Form On Screen AutoCenter 03. Universal Dialog Box 3 (35:14) AutoCenter Not exactly in the center of the screen uDialog Left, Top, Height, Width Size of Controls in Form Resize Prompt Label on Form Resize Border Style Sizable, Dialog Move Buttons as Form is Resized Button Top, Left One or Two Buttons Visible 04. External String Manager (21:59) Multi-Language Database Don't Design Multiple Forms or Databases Different Prompts on Forms and Reports Create User Settings Table For Multiple Users See Security Seminar Create String Table Dynamic Labels on Customer Form Label Caption Property Dynamic Labels on Customer Report Create External Module GetString Function Use GetString as a MsgBox String 05. Review (2:31)

Access Developer 13

00. Intro (3:32) 01. Search Between Dates (26:01) Search Records Between Dates Begin Date, End Date SQL To Requery Form Combo Box for Before, After, Between Select Case Visible and SetFocus Limit To List Allow Value List Edits 02. Tuition Levels 1 (32:27) Tuition for a Private School Same as Products With Multiple Options Billing on Grade Level Add Tuition Discounts Add Discounts Per Child Multi Field Indexing Composite Key Composite Index Create Pricing Form to Add Schedule 03. Tuition Levels 2 (47:58) On Dbl Click to Add Tuition to Order OpenForm, GoToControl GoToRecord, Set .Form!Pricing Pricing Wizard Form Show Only Tuition Levels for Grade Aggregate Query Requery, SetFocus, DropDown Change to List Boxes Variable Pricing Check if Form Open On Error Resume Next On Error Goto 0 04. Conclusion (2:23)