Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
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.


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




The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/24/2024 5:07:59 PM. PLT: 0s