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


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

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




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 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 9/25/2023 10:30:13 PM.