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