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"