Access 2007-2019
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 313
Description: Advanced Access
Running Time: 72 minutes
Pre-Requisites: Access 312 very strongly recommended
Previous Lesson: Access 312
Next Lesson: Access 320
Main Topics: Product Category Form, Filter, Zoom, Macro, Convert to VBA, Job Cost, Split DB
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 11/24/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

We'll begin by creating a Product Category Master Form where we can see a list of product categories, and then next to it a list of products in that category. These will both be in subforms on the main Master form. When you click on a category, it will update the other subform and show you just the products in that category. Then we'll add the ProductF form below those, and the product details will be shown down below - three forms in one.

 

Next, I want to be able to select a Category on my order form, and then have the product combo box list filter its results based on that selection by rewriting the SQL RowSource in the combo box.

 

Next we'll see how to automatically Zoom in to fields when they receive the focus (like a notes field) or if you double-click on them. I'll teach you how to create a Macro and then convert it into VBA code so you can figure out all kinds of new stuff.

We'll see how to hide items on our Invoice - like put cost-only products or services on an invoice and hide them from the customer (but still calculate them in our Job Costs). Our invoices are also looking really cool by this point.

 

Finally, we'll split the database intro front-end and back-end files as we did back in Access 307, but this time we'll walk through it in more detail, and I'll discuss the ramifications of sharing your database with other users, and over a local area network.

 


ACCESS 313 - Course Outline

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

7. Review - 4:43


 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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