Access 2000-2003

Access 101

1. Introduction Welcome to Access 101 Important concepts 2. Terminology What is a database Database components (tables, queries, etc.) Describe each component and it's function 3. Planning Your Database Planning your database on paper Plan for needed tables Plan for needed fields in each table 4. Getting Started Starting Access Parts of the interface Creating a blank new database 5. Customer Table, Part 1 Creating a customer table using design view Creating the FirstName field 6. Customer Table, Part 2 Brief description of basic data types (text, memo, etc.) 7. Customer Table, Part 3 Adding additional fields to your table (phone, address. etc.) Brief description of basic field sizes (long int, decimal) 8. Customer Table, Part 4 Creating a CustomerID Autonumber Moving fields in order Saving your table Setting the primary key 9. Entering Data Adding data to your table Deleting records 10. Customer Query Building a query in design view Adding fields to your query Sorting your query (LastName, FirstName) Moving fields in your query Using criteria (e.g. show all customers from NY) 11. Customer Form Building a form using design view Selecting a data source Adding fields to your form using the field list Lining up your controls Switching to Form View and back to Design View Using navigation buttons to navigate records Moving controls with the Hand Selecting a block of controls Deleting controls Trick to add all controls from the field list to the form Saving your form 12. Form Formatting Changing colors of your form and objects Deleting a label, keeping the text box Editing your label text Resizing a text box Moving a block of controls together Moving a label independent of it's text box Resizing your form Object borders and special effects Deleting a record from the form 13. Mailing Labels Using the mailing label wizard Desigining a prototype label Report Print Preview Zooming in and out

Access 102

1. Welcome Objectives Pre-requisites Versions used How to learn 2. Review How to find sample database files Review topics from Access 101 3. Field Properties Text field sizes Number field sizes Format property Date formats Text formats Number formats Input masks Required property Default value Validation rule Validation text Indexing 4. Search Sort Filter Find & Replace Sorting Filtering data Both tables and forms 5. Queries Multiple OR criteria (NY or CA) AND from different fields (NY and Active) AND across, OR down Saving a query with a different name OR from different fields (NY or 200 credit) Between keyword Date criteria (>#1/1/1998#) Wildcards LIKE keyword Parameter Query 6. Forms Creating an Employee table Using OLE Objects Creating the employee form Adding a picture field to your form Resizing your form Intro to object properties picture size mode: stretch, zoom, clip The toolbox Combo box with static data (M/F) Using the combo box wizard. Change to: Combo box into a List Box Selecting and copying a record Tab Order Form fields: align left Zoom with shift-F2 Command button: Close Form 7. Reports Query for people not missing address data Is Not Null: AND condition People who are missing address data Is Null: OR condition New Mailing Labels Building a report with design view Building a report based on a query Page header & footer 8. Compact & Repair Why compact your database? How to compact & repair 9. Review Review topics Skills check What's Next?

Access 103

l. Introduction Objectives Pre-requisites Versions used How to learn Included files 2. Main Menu, Part 1 Creating a form with no data Unbound form instead of a Switchboard Command button to open a form and show all records Form properties Form caption Record selectors Navigation buttons Scroll bars 3. Main Menu, Part 2 Form background color Label control Label properties (font, size, bold, effects, etc.) Resizing a label Putting an image on a command button Button properties Send to back / bring to front Format painter Setting a database startup form Application title Hiding the database window Unhiding your database window Creating a shortcut to your database on your desktop 4. Customer List Form Query: sorted list of customers Building a form based on a query Lining fields up horizontally Continuous forms v. single form Optimizing form space Form header & footer Using the rulerbars to select across/down Cleaning up your labels Command button to open a form and show specific records Opening the selected customer record Filtered results in your form Removing a filter Autoform: Tabular Autoform: Columnar 5. Tracking Lead Sources Adding a lead source field to the customer table Combo box with static list of lead sources Making at table to store a dynamic list of lead sources Creating a combo box based on a table Adding new valves to your table Updating our Main Menu with the new buttons Using the Form Wizard to create a quick form Create a form for lead sources using the wizard Renaming your form Create a button to close the database 6. Review Review topics Skills check What's next?

Access 104

l. Introduction Welcome Objectives Pre-Requisites Versions Sample Database Files 2. Contact Table What is a "Contact" Using the customer notes field for contacts Limitations Creating the Contact Duplicate data problem Setting the default date with the Now() function 3. Contact Query Listing contacts by date Show only a specific date between two dates Between keyword Let the user enter the dates with a parameter Creating Full Name field by merging two fields String concatenation Formatting a query field Format property 4. Contact Report Using the page header Selecting objects using the ruler bar Notice an & disappears in label && in Label Adding a line object Keeping your line straight Text boxes with large amounts of data are truncated Can Grow / Can Shrink textbox properties Can Grow / Can Shrink section properties Sorting and Grouping Sorting in the report Group Header & Footer Keeping a group together on a page Report header / footer Adding page numbers to your reports Showing total number of pages 5. Miscellaneous Put contact report on Main Menu Control Tip Text 6. Review Review topics Skills check What's next?

Access 201

1. Introduction 2. Relational Database (RDB) Concepts Why are RDBs important One-to-one relationships One-to-many relationships Many-to-one relationships Many-to-many relationships Normalization 3. Relational Contacts Table Making our contact management relational Inserting a CustomerID in ContactT Removing duplicated fields What is a Foreign Key? 4. Contact Query Manually creating query relationships Deleting relationships String concatenation Dealing with duplicated fields Correcting our contact report Manual formatting of the phone field 5. Contact Form Constructing a contact form Using a combo box to pick the customer Manually editing combo box properties 6. Miscellaneous Add contact form button to main menu Global relationships between tables Creating and deleting links between fields 7. Review

Access 202

0. Introduction 1. Contact Button Command button to open contacts for current customer Find specific data to display Viewing filtered results 2. Setting Values Across Two Forms Using Forms!FormName!Field notation Get CustomerID for Combo Box on ContactF Setting the Tab Stop property Skipping fields in the Tab Order 3. Sorted Customer Combo Creating a sorted query with customer info Manually editing the Row Source property Modifying the Column Count property Column Widths property Column Heads property SQL Primer Modifying the row source with an SQL statement List Rows and List Width properties Lesson 4. Callbacks Adding callback and date to table/form Creating a callback query Creating a callback form Buttons on the form to open customer/contact Lesson 5. Contacts Subform Learning about subforms Creating the contact subform Using the subform control and wizard Format > Send to Back Hiding text boxes with the Visible property Lesson 6. Miscellaneous Editing our Main Menu Replacing button images with text Coloring text on buttons Phone AutoDialer Lesson 7. Review

Access 203

0. Introduction 1. Creating the Extended Customer Table Building the table Creating fields Hyperlink field for Web Site 2. Using Option Groups Option Groups Option Buttons List Box Creating a Customer Type Table 3. Toggle Buttons Toggle Buttons Making a Check Box > Change To Toggle Button Creating a Toggle Button manually Changing Toggle Button Properties Changing Option Group Colors &Caption trick 4. ActiveX Calendar Combo Box for Sales Rep Combo Box for Service Tech Picture OLE Field Birthday ActiveX Control 5. Tab Control Linking CustomerF to CustExtF Tab Control 6. Object Controls Bound Object Pasting v. Linking Bound Objects Unbound Object Image Control Boxes & Lines Page Breaks Force a 1-to-1 Relationship

Access 204

0. Introduction  1. Contact Letter Query Build a query that shows only the current contact Getting the ContactID from the form into our query 2. Contact Letter Report Designing our Contact Letter Report Placing the Print As Letter button Dirty records - data needs to be saved 3. Macro Basics Constructing basic macros Beep macro action OpenForm macro action OpenTable macro action MsgBox macro action On Dbl Click event OpenForm WHERE condition 4. Refresh Macro RunCommand, Refresh macro action OpenReport macro action Creating a new button using the macro 5. Formatting the Letter Creating new name and address fields String concatenation in a new text box Formatting as a long date Format > Vertical Spacing > Increase Report width, page width, margins Placing a logo in the page header Making a multi-line label

Access 205

1. Past Due Customers Add fields to CustomerT: AmountDue, AmountDueDate Create a past due query Wilcard Field (*) in queries 2. Letter Table & Report Copy letter to work with new query for collection letters Create a Letter table to hold your letter text Create a Letter form Bringing data from an unbound form field into a report 3. More Letter Report Force new page after section in reports Create a greeting line Place AmountDue and AmountDueDate in letter report The difference between DefaultValue and ControlSource Place report date in unbound text box on LetterF 4. Generic Letter Report Making the letter writer generic so we can send to anyone Add IncludeInMailing field to CustomerT Create a Mailing List query Create a generic letter report Form design tricks with rectangles Create a button to open generic letter report Add IncludeInMailing field to customer list form Add IncludeInMailing field to customer form Update main menu with button to letter form 5. Post Cards Create a blank report to use for post cards Columns in reports Sizing the post cards properly Copying data fields and labels from other reports

Access 206

Lesson 1. Setting up Task Tables Create TaskT table Create PriorityT table Create CategoryT table Create RecurringT table Lesson 2. Setting up the Task Form Create a TaskF simple form Create combo boxes for Priority, Category, Recurring Modify the Row Source for each to Sort the boxes Create a macro to open each table when combo box dbl-clicked On Dbl Click event Macro Names (grouping macros) Macro to open supporting tables Lesson 3. Task List Form Creating a Task List continuous form Lesson 4. Synch Task List to Task Form Creating a macro to open a specific form Setting the OnCurrent event in the Task List Form GoToRecord macro command (macro action) GoToControl macro command Creating a command button without the wizard Assigning a macro to a command button SetValue macro command Using the Expression Builder Creating buttons to set the due date to today Lesson 5. More Date Buttons, Requery Button Button to set due date to tomorrow Button to set due date plus one, minus one day Learn about adding fractions of a day Button to set due date to "later today" Creating a second bound date field with different format Creating a button to requery your form results

Access 207

Lesson 1. Task Query Creating a TaskQ query Create a TaskViewDate form field to limit the date Base the data in the query on the date on the form Run our requery macro in the On Open event Run our requery macro in the AfterUpdate for the date Copy date reset buttons from TaskF to TaskListF Lesson 2. Recurring Tasks Learn about Macro Conditions (groups) Conditions are IF/THEN statements for Macros If the Completed box is clicked on a recurring macro increment date Deal with weekly, monthly, and other tasks Allow Edits form property Allow Deletions form property Allow Additions form property Locked control property Lesson 3. Choose to Hide Future Tasks Create a Show All Tasks check box If the check box is unchecked, hide tasks by date If checked, show all of the tasks regardless of date Use the IIF() function to determine if task in future Use the NOT keyword to negate values Complex IIF() function with multiple conditions Nested statements using AND and OR clauses Feeding query results into a second query Task2Q Lesson 4. Filtering Results Manually filtering using built-in Access filters Filter By Selection, Remove Filter/Sort Creating our own filter text and combo boxes Adding * values to our supporting tables Review of the LIKE keyword in queries Manually editing the Column Count and Column Width Lesson 5. Miscellaneous Dealing with missing data Tasks without a category won't show. Fix this. Compact and Repair Database Add Tasks button to Main Menu

Access 220

1. Sales Log Table Create SalesLogT Default SaleDate to =Date() Setup PaymentMethodT 2. Sales Log Query Calculated Query Fields Multiply Two Fields Together Calculate Extended Price Mathematical Operators Add, Subtract, Multiply, Divide Integer Division Exponents Modulo (Modulus) IIF Function Determine If Taxable Calculate Order Total Query Field Properties Round Function Rounding Field Values 3. Sales Log Form, Part 1 Copy Customer Combo from ContactF Bring in Calculated Fields from Query Continuous Forms Form Header and Footer Move Labels to Header Horizontal Line 4. Sales Log Form, Part 2 Create an Order Total in the Form Footer SUM Function in Footer Field Totals with Filters On Show Sales for Specific Customer Open a Form and Show Specific Records 5. Query Join Types Only include rows where the joined fields from both tables are equal Inner Join Include ALL records from Table1 and matching fields from Table2 Outer Join Show ALL Customers and Sales if they have them Show ALL Sales and Customers if listed 6. Global Relationships Enforce Referential Integrity Cascade Delete Tools > Relationships Why I don't often use Global Relationships

Access 221

1. Aggregate Queries Summary Queries Totals Queries Group Different Records Together Sums, Averages, Counts Examples in Excel (for understanding) Sorting in Excel Data > Subtotals in Excel Grouping in Excel 2. Build an Aggregate Query List of Unique Customer Names Only Essentially Eliminate Duplicates Totals Button in Query Design Show Total Amount Due by Customer Max Value Count of Records Average (Avg) of Amounts 3. Sales Totals By State Group by State First Record Unique Phone List of Customers Sales Totals by State Add a Date Criteria Add Parameters 4. Most Recent Contact Date When was the last time you contacted a customer? Show all customers and contacts if they exist Outer Join on CustomerID Show Min of DateTime Oldest Contact Date for Each Customer Count of Contacts for Each Customer Renaming Query Fields Limit by State with Criteria In() Function In("TX","NY") 5. Sales Totals by State Report Page Header & Footer Sorting & Grouping Levels Group Footer Visible Report Section Footer SUM Total in Group Footer Visible Property - Hide Report Fields, Labels 6. Employee Time Sheets Table to Track Time In, Time Out Join WorkLogT to EmployeeT Calculate Hours Worked SUM Up Hours Worked per Employee CDate Function - Convert To Date 7. Work Log Form Employee Combo Box Hours Worked SUM Total in Form Footer Filter By Employee Button On Main Menu 8. Time Sheet Form Parameter Form to Select Employee and Date Range Display Time Sheet Based on Parameters Entered Forms!FormName!FieldName Get EmployeeID From Time Sheet Form Run Query from Command Button Wizard 9. Time Sheet Report Employee Data in Group Header Hourly Data in Detail Section SUM Hours Worked in Employee Footer Command Button to Preview Report "Print Timesheet" Button Enter Hourly Pay Calculate Amount Earned

Access 222

1. Intro to Action Queries Different Types of Action Queries What do Action Queries Do? Update Query Append Query Delete Query Make Table Query Always Back Up Your Database! Always Back Up Your Tables! 2. Update Query Change a company name to a different one for multiple records Build an Update Query with the Query Designer Update To Criteria 3. Another Update Query Change company name only for companies in NY Query to update all uncompleted Tasks to today's date 4. More Update Queries Change all states to CAPS (ny to NY) Change all names to CAPS (joe to JOE) UCase() Function LCase() Function Proper Case with StrConv() Function 5. Yet Another Update Query Length of a string: LEN() Function Add an area code to phone numbers that are only 7 digits long Change email domains from @xyz.com to @abc.com INSTR() Function Left() Function Disecting the username from the email domain 6. Append Queries Create a query to archive your old contacts Copy all records older than 1 year to an archive table 7. Delete Query Delete those archived records from the last lesson Cascade deletes and global relationships Cascade update related fields Viewing related records in a table Delete query also deleting all RELATED records 8. Make Table Query Making a backup of a table with a Make-Table Query AutoBackup Query Exporting just a specific set of data 9. Automated Queries Make a form to automate archiving contacts Specify archive date in a form field Macro with OpenQuery command Command Button to Run Macro Record Selectors to No Navigations Buttons to No Turning Off Warnings Permanently Options > Confirm > Document Deletions, Action Queries Turning Off Warnings Temporarily SetWarnings Macro Command MsgBox Macro Command

Access 223

1. Crosstab Queries, Part 1 What is a Crosstab Query? Similar to an Excel PivotTable Format() Function "yy-mmm" Sales Totals by Month Sales Totals by State by Month 2. Crosstab Queries, Part 2 Create a Crosstab Query Column Heading Row Heading Value Adding Date Criteria Defined Query Parameters Breaking Down by Year Specify Column Headings 3. Crosstab Query Wizard Using the Wizard to Build a Crosstab Query 4. Other Query Wizards Simple Query Wizard Find Duplicates Query Wizard Find Unmatched Query Wizard Finding duplicate customer records Find customers that have no contacts 5. Query Properties Showing the Top X Values (eg Top 10) Showing the Top X% Values (eg Top 10%) Unique Values Unique Records 6. Beginner SQL SELECT field FROM table Build a Query in the Designer Switching to SQL View Multiple Fields Removing Clutter that Access Adds to SQL INNER JOIN WHERE clause Placing SQL statements in combo box Row Source property ORDER BY clause ORDER BY DESC UNION Query - can only be done with SQL 7. Query Tips & Tricks IN() Function Cartesian Product Count(*) Function Medium Date Format

Access 301

1. Create Order Tables Create the OrderT table for data on an Order Create the OrderDetailT for line item data 2. Order Queries Enter sample order data Create OrderDetailQ Calculate Extended Price (Price * Quantity) Round() Function Format as Currency Field for Sales Tax Rate Calculate Actual Sales Tax Value 3. Order Form Create a SalesRepID to Track Salesman TechRepID for Technical Support Different IDs for the Same Field Type Design the Order Form Choose Builder - Code Builder Learning VBA (Visual Basic for Applications) On Dbl Click Event Procedure Private Sub End Sub DoCmd.OpenForm OpenForm and show a specific record Where Condition 4. Order Subform Create the Order Subform from scratch Continuous Forms Subform Control Turn off Navigation Buttons 5. Subform Totals Showing the Totals in the Subform Footer More with SUM() Function Get Line Item SalesTaxRate from Order Form Create a Button to Show Orders Only for Current Customer Sales Tax Rate Per Customer, Per Order Setting a Default Sales Rep for each Customer

Access 302

1. Product Information Backup Access 301 Database Create a Product Table Non Taxable Products Sales Tax Override Put ProductID in Order Details table 2. Product Combo Box Create a Product Combo Remember the value for later use Put Combo in OrderDetails Footer Command Button to Add Product Get Data from Combo Box Columns Combo.Column() property 3. Get Data From Combo Box ProductCombo.Column() Move to a new record in VB code DoCmd.GoToRecord Accessing data from multiple columns 4. DLOOKUP What is DLOOKUP When should you use DLOOKUP DLOOKUP to get values from tables Me.Refresh DLOOKUP Order Tax Rate from CustomerT DIM a variable in VB Basic Error Handling On Error Resume Next

Access 303

1. Order List Form Create a Form to Show All Orders Deal with adding a product when no order exists Force user to select a sales rep Create OrderListQ with LineTotal 2. On Double Click Double-click on Customer to open Customer record On Dbl Click Event Always Use Event Procedures Command Button to OpenForm Without Wizard Double Click Event to Open Specific Order 3. Bill To & Ship To Address Why Store Duplicate Data Bill To / Ship To Addresses for Customers / Orders Automatically copy one to the other if blank Fix tab order 4. Subroutines DLOOKUP Both Addresses on Order Form Add New Order Button acFormAdd Create Your Own Subroutine Private v. Public Subroutines Calling Public Subs from a Different Form 5. Missing Records DLOOKUP SalesTaxRate for Orders Fix Joins in OrderListQ Show All Orders even if Missing a Customer

Access 304

1. Quotation or Invoice Is this a quote or an invoice? IsQuote field Add Browse All Orders button AfterUpdate Event Event when a field is updated Change button color and caption Access numbered color codes 2. On Current Event Event when user moves between records Add IsQuote to Order List Filter By Selection Add sum of orders to order list footer 3. Printable Invoice, Part 1 Create a Master Invoice Query InvoiceQ Sorting & Grouping Levels Keep all Order information together Group By OrderID 4. Printable Invoice, Part 2 Arrange controls in Detail section Force New Page After Section property Invoice Footer Totals 5. Printable Invoice, Part 3 File > Page Setup > Margins Resize invoice layout Quote or Invoice Label Dynamically change label in Build Event Create page header Add company logo to top of invoice Stretch, Zoom, and Clip modes for graphics 6. Printable Invoice, Part 4 IsPaid Label Hiding fields on a report Can Grow, Can Shrink Properties Create a Print Button on the Order Form Printing just a single invoice

Access 305

1. Accounts Receivable Query Aged Accounts Receivable Create a Query with OrderTotals Add OrderDate to tell Age of Order Use IIF Function to Add Aging Current LessThan30 LessThan60 MoreThan60 Add DaysOld Field 2. Accounts Receivable Report Create the report Format as Currency Hide $0.00 values with VBA Code The Visible Property Put SUMs in the Report Footer Create an AR Total 3. Percent Discount on Orders, Part 1 Discounts as a dollar amount Percentage Discount Add PercentDiscount to OrderT Add PercentDiscount to OrderDetailT Adjust SalesTax to figure Discount Link the Discount default to OrderF 4. Percent Discount on Orders, Part 2 Add PercentDiscount to CustomerTable Add PercentDiscount to CustomerForm Add to AfterUpdate event CustomerCombo Update DiscountRate on Line Items if Form Changes Create an Update Query to change Rate Create an AfterUpdate event to run Update Query MsgBox to Get a Response vbYesNo, vbYes, vbNo Line Continuation Character in VB Code Dim variables Option Explicit 5. BeforeUpdate The Cancel parameter of BeforeUpdate OldValue property vbYesNoCancel

Access 306

Employee Commission Rates based on single rate, based on different rate per employee, based on order amount. Nested IIF Functions. CommissionLookupT, paying employees commission based on order amount and separate rates for each employee. DMax. Using DMax with DLookup to pull a commission rate based on employee and largest order amount. (Note DMin is same as DMax but looks up the smallest value) Commission Form. Me.Requery on opening a form (getting no records because underlying query is based on a checkbox on the form). Commission Report to group commission amounts by employee. Update query to mark all commissions paid and track date paid. Using InputBox to ask for a password when a button is clicked. Employee PayRateT. Calculating employee pay rates based on employee and dates worked. Another DMax, DLookup example. Calculating regular and overtime hours and total pay rates.

Access 307

Lesson 1. Custom Search Button Docmd.FindRecord Docmd.GoToControl InputBox Custom Sub with Parameter Lesson 2. Dynamic Combo Boxes Part 1 Combo boxes that control (filter) other combo boxes Form_BeforeUpdate Cancelling BeforeUpdate Verifying user data before allowing them to leave a record Lesson 3. Dynamic Combo Boxes Part 2 ComboBox.Requery Subform Link Master ID Lesson 4. Simple Database Security Set Database password Open Database Exclusive Access Unset password Converting a database between versions Creating an MDE file Lesson 5. Splitting a Database Database Splitter Wizard Linking Tables Linked Table Manager

Access 308

Lesson 1. - Working with External Data - ODBC (Open DataBase Connectivity) - Importing & Exporting Data - Linking v. Importing - Examples Lesson 2. - Exporting to another Access database - Creating a Blank Sales Rep database - Exporting Customer Table - Exporting to an Excel Spreadsheet file - Creating a query to limit our data - Exporting the query data to Excel - Using the OfficeLink button - Analyze It with Microsoft Excel - Publish It with Microsoft Word Lesson 3. - Export Text: Comma Delimited - Export Text: Fixed Width - Create EXport Specification - Create a Macro to Export Text - TransferText Action - Create a Macro to Export to Excel - TransferSpreadsheet Action Lesson 4. - Import From Another Database - Appending Records To Existing Table - Matching Field Names (Append Query) - Creating Values For Unmatched Fields - Import From an Excel Spreadsheet - Import Wizard Lesson 5. - Importing Delimited Text File - Importing Fixed Width Text File - Fields That Are Directly Adjacent - Linking To Tables in Another Database - Linking To A Spreadsheet - Linking To A Named Range in Excel - Linking To A Text File Lesson 6. - Splitting Single Name Field into Two - INSTR Function - LEFT and RIGHT Functions - LEN Function

Access 309

1. Relationships Review One to Many Many to Many Cross Reference Tables DriverXVehicleT 2. Cross Reference Table Creating the Many-To-Many Relationship Making a Cross Reference Table Creating a Group Table for our Customers CustomerXGroupT CustomerXGroupQ 3. Group Form Creating the Group Subform Many To Many Form 4. Converting Numbers into Dates Got a field with dates like 990105 Convert with an Update Query to 1/5/99 LEFT, RIGHT, MID functions Update Query String Concatenation Calculated Fields 5. Addendum on Numbers to Dates What if you have dates with 5 digits? Using LEFT and LEN functions CDate() to convert text to dates

Access 310

0. Introduction - 4:33 1. Conditional Formatting in Forms - 5:05 Changing form field formatting based on data 2. Resizing a Form with VBA - 6:58 Using Visual Basic code to resize forms when they open Me.InsideHeight, Me.InsideWidth What are Twips 3. Company Settings Table, Part 1 - 11:23 Moving our company logos from forms/reports into table Benefits: easily changed, saves space Creating a company settings table Locked Property Inserting a Bound Object Frame in a report DLOOKUP in a report object control source 4. Company Settings Table, Part 2 - 7:17 Closing one form when another one opens DoCmd.Close OnClose Event When an open record on one form locks another Using DLOOKUP to get other values from company settings Changing the form caption property OnOpen Event for a form Me.Caption 5. Tracking Payments, Part 1 - 21:31 Payment table and form (PaymentT, PaymentF) Button on order form to show payments for current order DoCmd.GoToRecord acNewRec to go to a new record Using DSUM() to add up all of the payments for an order How is DSUM different from DLOOKUP Popup and Modal properties for a form Refreshing the records on a different form Forms!OrderF.Refresh and Me.Refresh Calculating the amount due on an invoice Accessing a value on a subform Forms!OrderF!OrderSubform.Form!OrderTotal 6. Tracking Payments, Part 2 - 15:19 Warning users not to make a payment if invoice is PAID vbExclamation vbYesNo+vbExclamation Creating a new value called MyReply in VBA Code Exit Sub Option Explicit Explicitly declaring variables Using DIM to create your own variables Adding more logic - don't allow a payment if it's a QUOTE Using the BeforeUpdate Event Don't allow a user to change an invoice to a quote if PAID Cancelling a BeforeUpdate Event Don't allow a user to mark a QUOTE as PAID Don't allow a user to put payments on a Quote Refreshing the form record before printing an invoice Putting payments and amount due on the Invoice report: DSUM When [OrderT].[OrderID] doesn't work use [OrderT.OrderID] 7. Tracking Payments, Part 3 - 18:28 Putting payment info on Invoice report Hiding the payments field and label if there are no payments OrderT.OrderID Footer Section Build Event GroupFooter1_Format section Invalid Use of Null What to do if there are no payments? On Error Resume Next If IsNull(Field) Then Hiding a line Creating a "fake" amount due for orders with no payments Getting rid of empty space for invisible fields Labels don't have CanGrow or CanShrink properties Changing labels into text boxes with ChangeTo Making the label caption into a control source Hiding the payments and amount due if the invoice is paid Hiding the amount due if it's a quote 8. Tracking Payments, Part 4 - 12:14 Having the database notify us if enough payments received Do you want to mark this invoice PAID? vbQuestion Notify the user if the customer has overpaid Fixing our accounts receivable report Adding the total payments to accounts receivable: DSUM

Access 311

0. Introduction - 2:42 1. Unit Cost on Orders - 13:16 Being able to calculate profit by knowing item costs Adding UnitCost info to OrderDetailT Creating an ExtendedCost field (UnitCost * Quantity) Adding UnitCost and ExtCost to our OrderDetailF Adding calculated totals to footer Adding Cost fields to our Product Table Get the product cost when a product is selected from ComboBox 2. Sales Report - 12:07 Creating a Sales Report with Cost & Profit Add cost information to OrderListQ Create the SalesReport Query and Report Format() function to force numbers to show as currency 3. Sales Report & Form - 13:49 Adding totals to our Sales Report Remember you can't put SUMs in your Page Footer Create a Report Footer Sort our Sales by Date - Sorting & Grouping Level Group Header & Footer ON Group On: Month Format() Function: Format(OrderDate,"yyyy mmmm") Criteria form to set begin date and end date for sales report The Between keyword (remember me?) [Date] is no good. Use Date() 4. Dynamic SQL Form 1 - 10:10 Clean up Customer List Form Added Browse Customers and Add New Customer Buttons DoCmd.GoToRecord acNewRec Clean up Order List Create an OrderList2Q so we can make more calculations Add AmountDue information to OrderList form Discuss what the Dynamic SQL Form is going to do 5. Dynamic SQL Form 2 - 14:44 Create a combo box with a list of options:  - Show Invoices & Quotes  - Show Quotes Only  - Show Invoices Only  - Show Paid Invoices Only  - Show Unpaid Invoices Only These are the options we want to see in our Order List Form Set the default value for the combo box Create an unbound text box to hold our SQL statement Review of SQL Statements SELECT Fields FROM Table WHERE Criteria ORDER BY Field Create the BuildSQLStatement sub Creating your own Private Sub Set the form RecordSource equal to our SQL statement Me.RecordSource = MySQL Using VBA Code to build the SQL statement based on selection Using a SELECT CASE statement instead of IF THEN statements Creating another combo box for sorting:  - Sort by Order Date  - Sort by Company Name  - Sort by Last Name  - Sort by Order Total  - Sort by Amount Due Add another SELECT CASE to handle the ORDER BY clause 6. Dynamic SQL Form 3 - 17:07 Checking our Sort Combo Filter Put a call to BuildSQLStatement in the SortCombo build event Put a call to BuildSQLStatement in the form OnOpen event DEFINITION - right click on a function or sub name to jump Create our own Sort A to Z (ascending / descending) button Capturing an image on the screen with Print-Screen Drop that capture into Windows Paint Cut out the buttons you want to "borrow" Paste the buttons into your form. Change the border style (raised, sunken) Change the button names: SortAZ, SortZA Make SortAZ visible, SortZA not visible If the SortZA button is visible, add "DESC" to SQL Statement Make OnClick events for each of these buttons Change Enabled = YES for these buttons "You can't hide a control that has the focus" Shift the focus with DoCmd.GoToControl Hide your SQL text box Add buttons to browse orders, add new order Change SortAZ and SortZA buttons to IMAGES not OLE objects

Access 312

0. Introduction - 5:29 1. Secondary Menu - 18:00 Creating a Secondary Main Menu Copy Main Menu to Secondary Menu Issues when copying buttons - code doesn't copy Creating a Notepad field on the main menu Saving Notepad data when you leave the field OnLostFocus, OnGotFocus Me.Refresh Dirty Records (Unsaved Data) Record Cycle: All Records, Current Record, Current Page Make a button to the Secondary Menu Make a button for our Accounts Receivable Report TRIM() Function to remove spaces 2. Resizable Customer Form - 13:11 Get rid of some unnecessary fields on the Customer form Add some new phone fields (home, cell) Move fields we don't use a lot way over to the right Determining the size of your form in Twips NOTE: A Twip is 1/20 of a point, or 1/1440 of an inch Me.InsideHeight, Me.InsideWidth Create a button to resize the form to your specifications Put code to make the form small in the form OnOpen event 3. Customized MyCompany Fields - 10:14 Add fields to the SettingsT Creating a Company DefaultTaxRate Adding InvoiceBottomText Add to Settings form Scale of a Decimal - digits to RIGHT of decimal point Getting a new customer's tax rate using DLOOKUP Using the BeforeInsert event InvoiceBottomText on the Invoice - make it a text box PageFooter Build Event GroupHeader0_Format Use DLOOKUP to get text from SettingsT 4. Creating a Service Invoice, Part 1 - 11:32 Creating a different invoice for services (vs. products) Copy InvoiceR to InvoiceServiceR Start by moving around the fields you want Remember to move VB code from section to section if needed Changing [OrderT].[OrderID] to [OrderT.OrderID] Add Customer FirstName, LastName fields to OrderT Change CustomerCombo.AfterUpdate to get Name fields 5. Creating a Service Invoice, Part 2 - 15:11 More basic field edits Putting a border around a field that grows / shrinks Inside margins for text boxes on reports Left Margin, Top Margin, Right Margin, Bottom Margin Leaving the Description of Job box there even if empty Hiding fields that aren't necessary The problem with vertical lines on reports - they won't grow Creating our own lines with VBA code Detail Section Build Event Detail_Format code Use the Me.Line command to create our own lines Switching from Twips to Inches or Pixels with Me.ScaleMode Creating a diagonal line in code Changing the width of the line with Me.DrawWidth Using Me.DrawStyle to create different types of lines Changing the thickness of a regular line from hairline to 1 6. Customizing The Order Form - 17:06 Adding a Tab Control to the Order Form (BillTo / ShipTo) Make a new command button to print the service invoice Fixing the InvoiceR - some of our changes broke it! What to do with CustomerCombo if there is no company name Show either the company name OR the LastName, FirstName Create the CompanyOrName field If the company is null, show customer's name Fix the OrderListQ - get customer name data from OrderT Adding Sums to OrderList form Changing the default sort of the order list form

Access 313

0. Introduction - 2:56 1. Product Category Master Form, Part 1 - 16:45 Making one subform control another with SQL RecordSource Create the product category table: ProductCategoryT Put a Product Category combo box on the Product Form You can't put a subform in a continuous form Make a second product list Add ProductCategoryID to the Product Table Creating a subform by dragging from the db window Put two subforms side-by-side: Categories & Products Filter the products based on the category I've selected Give good names to each subform Note that these are UNLINKED subforms - not linked to parent Change OnCurrent event for Category subform Set the RecordSource of the Product Subform to SQL Forms!FormName!SubformName.Form.Property Showing all records - include "*" record and handle it in VB Dim a String of characters (variable) Sorting the Category List 2. Product Category Master Form, Part 2 - 8:48 Lock the product subform AllowEdits, AllowDeletions, AllowAdditions: set all to NO Do the same trick and show the product form on the bottom Set the OnCurrent event for the product list to show product Adding a ProductCategoryCombo to the product form Set the combo's default value equal to the current category Add an "Add Product" button Adding Refresh buttons 3. Product Category Combo on Order Form - 6:59 Copy the ProductCategoryCombo from ProductF to OrderDetailF When you pick a category, rewrite the SQL of the ProductCombo ProductCategoryCombo AfterUpdate Event: Set the ProductCombo.RowSource = SQL Statement Deal with the "*" to show all records - special IF/THEN Automatically open the ProductCombo when a category is picked ProductCombo.DropDown 4. Auto Zoom to Notes Field - 6:07 Automatically zoom in when focus moves to notes field OnGotFocus for Notes textbox DoCmd.RunCommand acCmdZoomBox Discussion of RunCommand options in macros Converting Macros to Modules Create a macro and save it as a module to see VBA code Editing the tab order, removing tab stops Move the Zoom feature to the On Dbl Click event 5. Hidden Items on Invoice - 15:18 Items on invoices that you don't want the customer to see Add ShowOnInvoice field to ProductT, OrderDetailT Purpose of this to calculate job costs secretly To hide item on invoice report, CANCEL the build section Add ShowOnInvoice checkbox to detail section, make invisible Go into BuildEvent If Not ShowOnInvoice Then Cancel = 1, Exit the sub Remember to add the same code to our other invoice report 6. Splitting the Database - 11:12 Compact the database Split the database in two - covered in Access 307 Where your BACKEND database is located Creating a new folder for your database - C:\Database Move both database files to new folder Open front-end database and it will ask for table locations Load up the Linked Table Manager and point to Back End Talk about setup on a network using a mapped drive or UNC path

Access 320

1. Recordset Basics What is a Recordset? Why use Recordsets? Example Uses For Recordsets Turn Off VBA Project Explorer Tools > References What is a Library? Microsoft DAO 3.6 Object Library Microsoft ActiveX Data Objects 2.1 Library DAO vs. ADO, Brief Discussion 2. Our First Recordset Create a Database Object Create a Recordset Object OpenRecordSet db.Name, rs.Name Closing Our Objects Freeing Memory (Set to Nothing) Creating a Status Box Display Field Data: FirstName When to use ! vs . Ways to Refer to Fields rs!Fieldname rs("Fieldname") rs(Index) rs![First Name] Create Unbound Text Boxes Store RS Data Into Text Boxes 3. Moving Around, Part 1 MoveNext MovePrevious MoveFirst MoveLast Move X EOF End of File BOF Beginning of File Endless Loops Create a Counter Dim Variables Outside of Subs Variable Scope Opening Recordset With Form Opened Closing Recordset When Form Closed 4. Moving Around, Part 2 Buttons to Move Around Recordset Dealing with Hitting EOF/BOF RecordCount Property 5. Finding Records, Part 1 FindFirst Types of Recordsets dbOpenTable dbOpenDynaset dbOpenSnapshot dbOpenForwardOnly NoMatch 6. Finding Records, Part 2 Wildcard Search Dynamic SQL For FindFirst Do Loop FindFirst FindPrevious

Access 321

1. Customer ListBox, Part 1 Open Recordset with SQL SQL WHERE Statement Adding Items to a ListBox AddItem Row Source Type: Value List Access 2000 Users: No AddItem 2. Customer ListBox, Part 2 Add the ID Column Widths Clearing Your List Box ListCount RemoveItem Combo of Unique States 3. Edit Records Editing One Record rs.Edit rs.Update Editing Multiple Records Loop Through And Edit 4. Multi-Select List Boxes Simple v. Extended Loop Through Items With ListCount .ItemData(x) .Column(1,Row) .Selected Property For Each Object Collections 5. Adding & Deleting Records Adding a Record AddNew Sorting Our Customer List Deleting Records rs.Delete Always Have an Are You Sure? Prompt 6. Execute db.Execute INSERT INTO SQL Command

Access 322

1. Recordset Tips, Part 1 Recordset Options - dbAppendOnly - dbDenyWrite - dbDenyRead Record Locking Options - dbPessimistic - dbOptimistic RecordCount Not Working - dbOpenTable: OK - dbOpenDynaset SQL: OK - dbOpenDynaset Table: NOT OK - dbOpenSnapShot: NOT OK 2. Recordset Tips, Part 2 Seek Method Must Be Indexed Field Limitations of Seek CancelUpdate Method 3. Opening a Different Database OpenDatabase Method Reading One Value From Multi-Select Box OnDoubleClick - Which Is Selected? Reading Records From A Different Database OpenDatabase 4. Fields Collection Displaying All Field Values Showing Field Properties Fields Collection Type of Data Using the FieldName as a Variable rs(FieldNameVar) 5. Project: User Logon, Part 1 Why I Don't Like Access User Level Security Create a User table Create a Logon form Password type Input Mask Recordset to Check Password Default Command Button 6. Project: User Logon, Part 2 Create a Log table LogIt function With, End With Make LogIt a Global Sub in a Module Global Variables Global CurrentUserID, CurrentUsername Cancel a Form OnOpen Event

Access 323

1. ADO ActiveX Data Objects, Part 1 Benefits of ADO v. DAO Create a Database Connection CurrentProject.Connection Provider Recordset Types - adOpenDynamic - adOpenForwardOnly - adOpenKeyset - adOpenStatic - adOpenUnspecified Lock Types - adLockReadOnly - adLockPessimistic - adLockOptimistic 2. ADO ActiveX Data Objects, Part 2 Loop Through Records (EOF) Editing Records Default is a READ ONLY Lock Type AddNew to Add a New Record Delete to Delete a Record ADO Connection Strings Connect to SQL Server Connect to Oracle Using Both ADO and DAO Together 3. Student Test Taker, Part 1 Question Table Answer Table Teacher Q/A Form Student Table Response Table 4. Student Test Taker, Part 2 Test Taker Form Student Combo Box Question Text Box Answer List Box Take Test Button Disable the Student Combo Disable the Test Button Check to Make Sure Student Picked Auto DropDown the Student Combo DMin Function to Find Next QuestionID Nz Function To Return Zero on Null 5. Student Test Taker, Part 3 Load List Box with Answers RecordSet for Answers List.AddItem Button to Save Results Save With RecordSet to ResponseT Move To Next Question Lock the Next Button When Test Done 6. Student Test Taker, Part 4 Query To Show All Student Answers Different Links Give Different Results How Multiple Relationships Interact Report Grouped By Student Grouping Options Button in Wizard Summary Options Button Calculate Student Average Count of Records Total Correct Hiding the Detail Section

Access 324

1. Loan Calculator, Part 1 Calculating Loan Amortizations Enter In Data For Loan Price Of House Down Payment Interest Rate Term of Loan Calculate Amount Financed Calculate Monthly Payment Using the PMT Function PMT in Excel Calculate Total Payments Calculate Interest Paid 2. Loan Calculator, Part 2 Build the Loan Form Build the Payment Form Use a RecordSet to Create Payments DateAdd Function Me.Requery to See Subform Updates 3. Loan Calculator, Part 3 Payment Total SUM Label to Filter Payments Show Current Payments Show All Payments Me.Filter Me.FilterOn Turning the Filter On and Off Check For Existing Payments DLookup Any Existing Payments Delete Query to Erase Schedule 4. Loan Calculator, Part 4 RecordSet to Add Manual Payments Change RecordSource Better Than Filter 5. Loan Calculator, Part 5 Fixing the Missing Penny Fraction 6. On Not In List Event, Part 1 Customer Table State Table - Storing Text Add Different State - Not In Table AdSource Table - Storing a Number Limit To List Property 7. On Not In List Event, Part 2 NotInList Event NewData Parameter Response Parameter acDataErrDisplay acDataErrContinue acDataErrAdded Ask User If They Want To Add Data Recordset to Add Value To Table Using InputBox for Additional Fields

Access 325

1. Compare Two Tables, Part 1 Synchronizing Two Tables Discuss Replication Detecting Changed Records Exit For 2. Compare Two Tables, Part 2 ListBox to See Different Records Customer SubForms Change SubForm Data in VBA Subform.SourceObject Subform.Form.RecordSource 3. Compare Two Tables, Part 3 Importing Changes Detecting New Records Find Unmatched Query Wizard What Does Unmatched Wizard Do? Change RowSource of ListBox Importing New Records 4. Student Attendence, Part 1 Tables to Track Students & Days Absent Multi-Select Listbox to Pick Students Recordset to Add Absent Days Table to Track Days Off (Holidays, etc.) Aggregate Query to Count Absent Days Global Module Public Function DaysInSchoolYear 5. Student Attendence, Part 2 DLOOKUP Start / End of School Year Loop Through All Days Count School Days Exclude Weekends, Days Off View > Immediate Window Checking Variables Testing Function Return Values 6. Student Attendence, Part 3 Count of Absent Days Query Show Days In Class (Subtract From Total) Show Percentage Placing Start / End School Year in Table Showing Days Attended up to Any Date Form Cycle: Current Record Only Saving Data To Table (Refresh) AfterUpdate Button to Display Student Query

Access 326

1. Locking Orders When Paid Pull Databases Back Together Lock the Order if it's Paid Me.AllowEdits Me.AllowDeletions Me.AllowAdditions OrderSubform.Form.AllowEdits OrderSubform.Form.AllowDeletions OrderSubform.Form.AllowAdditions 2.Refunding an Order Refund Button Marking Unpaid in VBA Code Can't Hide a Control That Has Focus Pay a Quote Bug Me.Undo 3. Ship Date Add ShipDate Field Lock The ShipDate Show the ShipButton only if needed ShipOrder Button Can't Refund a Shipped Order 4. Inventory Levels Quantity On Hand (QtyOnHand) Adding Our UserLogon Form To Database Adding a Log feature Field.OldValue Tracking which employee changed inventory level 5. Items Already On Order Add QtyOnHand to Product Combo on OrderDetailF Increment Quantity if already on order Warning if no product picked Create a recordset to increment quantity Compile error: ByRef argument type mismatch 6. Shipping The Order, Part 1 OKToShip Function Check to see if we have enough inventory on hand 7. Shipping The Order, Part 2 Optional Parameters ChangeInventory function Actually ship the items

Access 327

1. Shipped Orders Add ShipDate to Order List Show Shipped/Unshipped Orders Sort by ShipDate Create a Packing Slip PackingSlipButton 2. Processing Returns Processing Cancelled Shipments Modify OkToShip Function to Handle Adding Inventory 3. Items Not In Product Table Shipping Orders With Products Not In Database Notify user of manual item Add "manual item" to packing slip Ask user if they want to go into negative inventory 4. Shipping Form Create ShipHowT for Shipping Methods Add Shipping Info to OrderT Create Shipping Form Modal, Popup Form linked to OrderT 5. Tracking On The Web, Part 1 Figuring Out UPS Tracking Links How are Hyperlinks Stored in Access Tables Building a Hyperlink 6. Tracking On The Web, Part 2 Following a Hyperlink in VBA Code Automatically Launching your Web Browser US Mail and FedEx Tracking URLs

Access 328

1. Reorder Levels Add Fields to Product Table Reorder Level Vendor Information Quantity On Order Vendor Table Add To Product Form Query to show Products Understocked 2. Purchase Orders Products Understocked Form Purchase Order Table Purchase Order Details Table 3. Order More Button Create Order More Products Sub InputBox to Ask How Many Convert string to number: CLng function 4. Make Purchase Order, Part 1 Create the Make Purchase Order Code Creating a PO with a Recordset Get the assigned Autonumber for a record Check if there is a PO for this vendor 5. Make Purchase Order, Part 2 Check if this item is already on a PO Use recordsets to create the PO and details Verify table data 6. Purchase Order Form, Part 1 Create Purchase Order Details Subform Create an unbound ListBox to show all POs OnClick event to show only items on selected PO Bind the form to Purchase Order Table Show Notes 7. Purchase Order Form, Part 2 Check Box to Filter Open/Closed POs Check Box to Filter Received/Not POs Tri-State CheckBoxes (Triple State) QuickLinks to Set the Checkbox Values

Access 329

1. Purchase Order Report, Part 1 Printable Purchase Order Grab MyCompany Info from InvoiceR PO Information Line Item Information SUM of Items Quantities COUNT of Line Items WhereStr to WhereBox Print Selected PO 2. Purchase Order Report, Part 2 Print All Listed POs NoData event if no purchase orders 3. Closing POs, Receiving Parts Part 1 Mark PO as Sent to Vendor UPDATE Query With Just SQL UPDATE Table SET Fields WHERE Condition Mark PO as Parts Received 4. Closing POs, Receiving Parts Part 2 Hiding Unnecessary Buttons in Code "Sent to Vendor" label on PO 5. Emailing Purchase Orders, Part 1 File > Send To > Mail Recipient as Attachment HTML Format Snapshot Format Microsoft Access Snapshot Viewer Text Format DoCmd.SendObject acFormatHTML acFormatTXT acFormatSNP acFormatRTF acFormatXLS 6. Emailing Purchase Orders, Part 2 PO Report not Formatted Good for Email Also Sends EVERY PO Making a Unique PurchaseOrderEmailR 7. Sending Bulk Email to Customers Add buttons for new forms to our menus Add IncludeInEmails field to CustomerT Bulk eMail List Recordset to Send Bulk Email acSendNoObject Microsoft Outlook Email Security "Feature"