Access 312 is all about making our database more user friendly.

We will start out making a new Secondary Menu because the Main Menu is getting a little cluttered. We'll make a bunch of new buttons and have fun. We'll create a Notepad field on the Main Menu. We'll learn about two new events: OnGotFocus and OnLostFocus, and a new function called Trim.

Next, we'll clean up our Customer form and make it resizeable so that the user can just click on one little button to make the form wide or narrow depending on how much data they want to see.

We will then make a couple of customized fields for our database. We'll make a default SalesTaxRate for the entire company - so new customers all inherit this tax rate (instead of having to type in 8% for each new customer - or hard-coding a default value in the forms). We'll also make the legal text on the bottom of our invoices easily changeable from the Company Settings form. This is all in a quest to make our database more user friendly.

Next, we're going to make a Service Invoice. Our Invoice we have right now is really laid out for selling products. What if you're a service business (or do both products and services). You might want an invoice in a different format. We'll tackle that issue.

One of the neatest things I'm going to show you is how to actually draw lines on your report at exact coordinates using VBA code!

Finally, we'll add some other user-friendly enhancements that have been suggested by customers over the past couple of months. We'll make a tab control for the BillTo and ShipTo fields. We'll make the Customer selection combo box show either a company name or the customer's first and last name if a company doesn't exist. Plus we'll make a lot of other little tweaks to make our database easy to use.

ACCESS 312 - Course Outline

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

7. Review - 4:07