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 311
Description: Advanced Access
Running Time: 86 minutes
Pre-Requisites: Access 310 very strongly recommended
Previous Lesson: Access 310
Next Lesson: Access 312
Main Topics: Profit, Loss, Unit Cost, Dynamic SQL, Format Function, SQL
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

 

Access 311 covers adding cost to our database so we can track net profit or loss for our sales. We'll also build a custom sales report and sales form for reporting. Finally, we'll create a form that has dynamically generated SQL to control what records and sorting are in the form.

First, I'd like to be able to figure out how much profit I'm making on each order by knowing the cost of my labors and materials. We'll start out by adding UnitCost info to our OrderDetailT and Order Subform. We'll add subtotals to the form as needed.

 

Next, we'll create a SalesReport query and report to show each order, what our cost is on that order, and what our net profit is.

 

Using our Sorting & Grouping levels and the Format() function, we'll create a custom format and breakdown for all of our sales by month.

 

We'll then create a simple form to set the limits (begin date and end date) for our sales report. Using the Forms ! FormName ! Field notation and the Between keyword to limit our sales query / report based on this information.

 

Next comes something that will add a whole new dimension to your form design... creating forms with a dynamic SQL statement controlling their records. In this case, we'll make our Order List form able to show quotes, invoices, paid invoices, unpaid invoices, and both quotes and invoices all by just selecting the option from a combo box. Our VBA code will construct an SQL statement which will update the records.

 

We'll also create a combo box to change which field our records are sorted by (company, name, amount due, order total, date, etc.) and a Sort A-to-Z or Z-to-A button to control ascending/descending sorts. This might sound easy, but we have to do it all using VBA code and events - take nothing for granted!

 

 


ACCESS 311 - Course Outline

0. Introduction - 2:42

1. Unit Cost on Orders - 13:16
Being able to calculate profit by knowing item costs
Adding UnitCost info to OrderDetailT
Creating an ExtendedCost field (UnitCost * Quantity)
Adding UnitCost and ExtCost to our OrderDetailF
Adding calculated totals to footer
Adding Cost fields to our Product Table
Get the product cost when a product is selected from ComboBox

2. Sales Report - 12:07
Creating a Sales Report with Cost & Profit
Add cost information to OrderListQ
Create the SalesReport Query and Report
Format() function to force numbers to show as currency

3. Sales Report & Form - 13:49
Adding totals to our Sales Report
Remember you can't put SUMs in your Page Footer
Create a Report Footer
Sort our Sales by Date - Sorting & Grouping Level
Group Header & Footer ON
Group On: Month
Format() Function: Format(OrderDate,"yyyy mmmm")
Criteria form to set begin date and end date for sales report
The Between keyword (remember me?)
[Date] is no good. Use Date()

4. Dynamic SQL Form 1 - 10:10
Clean up Customer List Form
Added Browse Customers and Add New Customer Buttons
DoCmd.GoToRecord acNewRec
Clean up Order List
Create an OrderList2Q so we can make more calculations
Add AmountDue information to OrderList form
Discuss what the Dynamic SQL Form is going to do

5. Dynamic SQL Form 2 - 14:44
Create a combo box with a list of options:
 - Show Invoices & Quotes
 - Show Quotes Only
 - Show Invoices Only
 - Show Paid Invoices Only
 - Show Unpaid Invoices Only
These are the options we want to see in our Order List Form
Set the default value for the combo box
Create an unbound text box to hold our SQL statement
Review of SQL Statements
SELECT Fields FROM Table WHERE Criteria ORDER BY Field
Create the BuildSQLStatement sub
Creating your own Private Sub
Set the form RecordSource equal to our SQL statement
Me.RecordSource = MySQL
Using VBA Code to build the SQL statement based on selection
Using a SELECT CASE statement instead of IF THEN statements
Creating another combo box for sorting:
 - Sort by Order Date
 - Sort by Company Name
 - Sort by Last Name
 - Sort by Order Total
 - Sort by Amount Due
Add another SELECT CASE to handle the ORDER BY clause

6. Dynamic SQL Form 3 - 17:07
Checking our Sort Combo Filter
Put a call to BuildSQLStatement in the SortCombo build event
Put a call to BuildSQLStatement in the form OnOpen event
DEFINITION - right click on a function or sub name to jump
Create our own Sort A to Z (ascending / descending) button
Capturing an image on the screen with Print-Screen
Drop that capture into Windows Paint
Cut out the buttons you want to "borrow"
Paste the buttons into your form.
Change the border style (raised, sunken)
Change the button names: SortAZ, SortZA
Make SortAZ visible, SortZA not visible
If the SortZA button is visible, add "DESC" to SQL Statement
Make OnClick events for each of these buttons
Change Enabled = YES for these buttons
"You can't hide a control that has the focus"
Shift the focus with DoCmd.GoToControl
Hide your SQL text box
Add buttons to browse orders, add new order
Change SortAZ and SortZA buttons to IMAGES not OLE objects

7. Review - 2:26


 

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