Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
Courses - Microsoft Access 312
Description: Advanced Access
Running Time: 95 minutes
Pre-Requisites: Access 311 very strongly recommended
Previous Lesson: Access 311
Next Lesson: Access 313
Main Topics: OnGotFocus, Trim, Resizeable Forms, Service Invoice, Lines, BillTo, ShipTo
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.


Order before 4/28/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details


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
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
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



Student Interaction: Microsoft Access 312

Richard on 1/1/2008:  Access 312 covers creating a Secondary Menu (our Main Menu is getting too congested); the OnGotFocus and OnLostFocus events; the TRIM() function; making our CustomerF form resizeable with the click of one button (using VBA code of course); adding more customized fields to our Company Settings table, such as a default SalesTaxRate for the entire database and the legal text for the bottom of our invoices; creating a Service Invoice based on the first invoice we made; how to dynamically draw lines anywhere on a report using VBA code; using a Tab Control to store BillTo/ShipTo info; and making the customer combo box show either the company name or the customer's first and last name if there is no company name.
Terry Hopper on 6/19/2009: Regarding Tab Control.
I have an employee form and I placed everything on tabs. I know you prefer not to use tabs, but I want them on this form. So, is there a way to get all the tabs to show on the width provided I setup in the making of the form?
i.e. cascade instead of scrolling left to right.

Richard Rost on 6/25/2009: Terry, unfortunately the design of the tab control is pretty much fixed by Microsoft. There are third-party ActiveX controls you can get to allow different options with the tab design, but what you see is pretty much what you get with the built-in tabs.
David Leech on 9/28/2009: lol I saw the address in the last name field in that code i was gonna say something last lesson but i figured you'd see it
Perry S on 1/20/2013: Richard
I'm Having an issue that Im not able to locate in the 312 Class. It started whan we modified the "CustomerCombo" on the on the "OrderF". I have tried to find what I've done wrong but I'm just not having any luck. The Error I get is when adding a new order either from the "CustomerF" or from the OrderF. Upon adding a new order I get a Compile Error in VB "Variable not defined". When I Click ok, Public Sub GetCustomerAddress" is highlighted in yellow and the FirstName = is highlighted in blue. I've wached the videos several times but I'm not able to locate my error. Any Ideas

Shallena Ayers on 3/2/2018: I am using Windows 10 and Access 2013. Using an OLE object makes my database super slow to open that page... to the point it is not an option to use an OLE object... Is there a different way to have the logo auto populate on the pages, forms and reports? Or is there something I need to do differently with this version of Windows and Access? Thanks in advance,

Reply from Alex Hedley:

WebBrowser control with the image source used as the src of an img control


You may want to read these articles from the 599CD News:


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP