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  
 

News      User Comments     History     Notify Me

1/2/2007
Access 301 Handbook
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.






Microsoft Access 301
Course Handbook Supplement

By Richard Rost


Published By
Amicron Computing
PO Box 1308, Amherst NY 14226 USA
www.AccessLearningZone.com


First Printing 7/6/2006
Copyright 2006 by Amicron Computing
All Rights Reserved


Welcome

Welcome to Microsoft Access 301.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 301. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.

We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.


Table of Contents

Welcome 2
Table of Contents 2
Lesson 1: Create OrderT, OrderDetailT 4
Lesson 2: Enter Order Information, OrderDetailQ, SalesTaxRate, and SalesTax 6
Lesson 3: Sales Rep, Order Form, and Your First VBA Code 11
Lesson 4: Order Subform 16
Lesson 5: Subform Totals (SUM), Default Values, and Open Orders Button 19
Review 23




Introduction

Welcome to Microsoft Access 301, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.




Objectives

· Starting our order entry system
· Design an order table and order detail table
· Design an order form and subform
· Introduction to VBA (Visual Basic for Access) Coding

This class follows Microsoft Access 223. I strongly recommend that you take my 100 level classes and 200 level classes before beginning Access 301.

We will be using Windows XP in this class, but the lessons in this class are really common to all versions of Windows.

In case you don't want to build a database yourself, you can get a copy of our database at http://www.AccessLearningZone.come/Access/301. If you installed off of our CD-ROMs, or downloaded the tutorial package from our website, you can also look in the help folder in your Program Files for this course.

Lesson 1: Create OrderT, OrderDetailT

In this lesson, we're going to create our order table and order detail table. We're going to begin by building an order entry system in our PC Resale database.

When you think of an order entry system, I want you to think of a standard invoice. If you've never seen an invoice before, this is what one looks like. You've got customer information, order information, item or product information, and some totals (plus some legal mumbo-jumbo on the bottom). That's the kind of information that we'll need for our order table.




Let's begin by creating our orders table. Go to Tables - New - and then Design View. We're going to start off with OrderID as our auto number. We'll add CustomerID as a Number, and OrderDate with a default value of today (written as "=date()"). We can add a DueDate with a default value of "Date()+10" and an IsPaid field with a Yes/No value. We can also add some Notes as a memo field and that should be good enough for now.




Let's save this as OrderT.

Now let's build our order details table. Go to Tables - New - and then Design View. Let's start off with our OrderDetailID as our AutoNumber. We need an OrderID in here, a Description, and some Notes. We can also add a Quantity field (with a decimal field size and default value of 1), a UnitPrice field.




We can go ahead and save this as OrderDetailT.


Lesson 2: Enter Order Information, OrderDetailQ, SalesTaxRate, and SalesTax

In this lesson, we're going to enter in some order information into our tables, we're going to create an OrderDetailQ to do some calculations, and we're going to create a field to do our sales tax rates and sales tax for each customer.

First, we're going to open up both tables side by side. Open up the order table (OrderT) and the order details table (OrderDetailsT).

Let's put two customers in here with some orders.




















Let's close these tables down and make a supporting query. Go to Query - New - Design View. We can now open up our OrderDetailT table.




We can then bring in all its fields.













We need to create a new field for the extended price: ExtPrice:Quantity*UnitPrice.



We can save this as OrderDetailQ. Run it and make sure that it looks correct.




We might want to round off the extended price to the nearest penny and to that, we need to add the Round function.




We might also want to format the extended price as a currency. We can right-click in the Design View to get to Field Properties and change the Format to Currency.









Now let's add a sales tax rate field to these tables. Let's open up our OrderT table and add a SalesTaxRate field as a Decimal and a 4-digit scale.




Now highlight the SalesTaxRate field and copy it. Close the order table and open up the order detail table. Now you can paste that right in.














Save that and close it. We can also drop it in our customer table (CustomerT).




Now we can go into our OrderDetailQ query and figure out the sales tax with SalesTax:ExtPrice*SalesTaxRate.




When we run it, we can put some sales tax rates in here. Notice how the sales tax is automatically calculated.





Lesson 3: Sales Rep, Order Form, and Your First VBA Code

In lesson 3, we're going to add a sales rep to our order table, start building our order form, and we're going to see our first Visual Basic for Applications code.

The next thing I'd like to put on our order table is some way to track our sales rep. Open the order table and add a SalesRepID field. We need a TechRepID field as well.




Let's close down the order table now and make an order form. Click Forms - New - Design View, and select the OrderT table.

Bring up the field list and bring over all the fields.




Save this as OrderF.










On our contact form, we have a customer combo box that we can "steal" - so we can just double-click on it in Design View and copy it. Back on our order form, we can get rid the existing CustomerID field and paste in the customer combo box that we copied from the contact form.




We can also copy the SalesRepID field from the customer form and paste onto our order form as well. Now we can get rid of the existing sales rep and tech rep fields and replace it with the SalesRepID field that we copied.

Double-click it and change the name to SalesRepCombo. Now copy and paste that field to make the Tech Rep field. Double-click it to change the control source to TechRepID and change its name to TechRepCombo.

If we take a quick peek at it in Form View (after a little formatting), it should look like this:




Let's take a look at the customer field in Design View. The event for this field says to open the current customer macro when the field is double-clicked. And the macro says to open up the customer form. So instead of using a macro, let's use VBA code to open up the CustomerID.











Bring up the properties for the CustomerID field and click the Event tab. Delete the event in the On Dbl Click line. Hit the Builder button next to it (with three dots).




Select Code Builder. This guy opens up Microsoft Visual Basic:




All you need to know right now is that everything in-between the Private Sub and the End Sub will run when you double click your customer combo box. So for right now, click between them and hit the tab key to indent your code. Then type in the following:




This tells Access to pop up a message box that says, "Hello There," when I double-click the customer combo box. Save it and close it
Notice what's in the properties window now. It says Event Procedure. That means there some VBA code in there that's going to run on that double-click event.




Let's close that, save the form, and go to Form View. Double-click on the combo box and you'll see your message box.




Let's go back to Form Design View and back into the properties for this combo box. Click on Event Procedure, and the Builder button.

I want to open up the customer form, so delete everything between the Private Sub and the End Sub, and replace it with the following:




Save it and move it out of the way. Save the form and then view it.





Now double-click on the customer combo box.




Let's close this and fix it so that when the customer combo box is clicked, it opens up the current customer, not just any customer. Return to your VBA window. We need a way to specify which customer to open. We need to add a parameter, so add the following to your existing code:




Save your work, and flip back to the form view. Double-click on the customer combo box and you'll see that it opens up the currently displayed customer.


Lesson 4: Order Subform

In lesson 4, we're going to build the order subform, which is going to go inside of our order form. First, let's go into Queries and open up our OrderDetailQ query. I want to add the extended price and the sales tax together in another field and call it LineTotal. That will be ExtPrice+SalesTax. And I'll format that as a currency in the field's properties so we get the following:




Save that and then go to Forms - New - Design View, and then select the OrderDetailQ table. Bring over all the fields and do a little formatting. Since this needs to be a continuous form, change its default value from Single Forms to Continuous Forms. And we can save this as our OrderDetailF. Open it up and you should see the following:















Now let's stick this order subform into the main order form. Open up OrderF in Design View. Open your toolbox and click the subform button.




Drop it on the form, delete its label, and open up the properties for the subform. Give it a name of OrderSubform and link it to the OrderDetailF source object. Close that and you'll see that it's now on the main order form.









Save it and take a look in Form View. Not too bad. Let's go back to Design View.




Let's close this and open up the OrderDetailF form in design mode. Bring up its properties can change the Scroll Bars to Vertical Only, and turn the Navigation Buttons off. Save it. Next, grab the OrderIDs and move them all the way to the right and then hide them. Right-click both of them and set their Visible properties to No. Then we can squeeze the notes field between the Description and Quantity fields.

Save the changes and open up the order form. As you can see, it looks a lot better!





Lesson 5: Subform Totals (SUM), Default Values, and Open Orders Button

In this lesson, we're going to work with Subform Totals (SUM), Default Values, and an Open Orders Button.

On the bottom of the OrderF form, I'd like to put some totals for each of the ExtPrice, SalesTax, and LineTotal columns. So copy these fields and paste them in the footer of the form. Next, open up the properties for the extended price and change its name to OrderSubtotal. Set the Control Source equal to the sum of all the extended prices above.




Change the properties name for the SalesTax field to SalesTaxTotal and the Control Source to SUM([SalesTax]) - all the sales tax values above. Do the same for the LineTotal field. Change it's name to OrderTotal and set its Control Source to SUM([LineTotal]). Now we can save it and see it in action.










Some formatting and labels on these things would be nice and we can put that on there in Design View.




Next, let's set some default values. In Design View, open up the properties for the sale tax and set its Default Value to [Forms]![OrderF]![SalesTaxRate]. Now close everything down and reopen it. You'll notice that the default tax rate is visible.




Now on the customer form, let's copy and paste the CreditLimit field. Change the label to TaxRate and change the Name and Control Source to SalesTaxRate. This will give us the customer's default tax rate.





Save that and let's make a new button to open up the orders for this area. Go back o the toolbox and drop in a command button. Change the button's label to Show CustomerOrders. Now right-click on the button and select Build Event. Select Code Builder in the Choose Builder window.

Between the Private Sub and End Sub, enter the following:

docmd.OpenForm "OrderF",,,"CustomerID=" & CustomerID

In other words, take the customer id in the current contacts and get its value. Save it, close it down, and open it back up again. Select a customer that has some orders and then click the Show Customer Orders button to see the order.




Now we can get the sales tax for new orders off of the customer form. How about the sales rep? First, let's set a default value for the tax rate and a default value for the sales rep.











Now let's open up the OrderF form in Design View and set the Default Value for the SalesTaxRate (in properties) equal to [Forms]![CustomerF]![SalesTaxRate].




For the default SalesRepID, we can change its Name in properties to SalesRepCombo and close the customer form. Now you can set the default value for SalesRep (on the OrderF form) to [Forms]![CustomerF]![SalesRepCombo].

Since we're going to make the tech rep default to the sales rep, we can do the same thing in the properties for the tech rep proprieties. Save that, close it, and open up the customer form again. Click the Show Customer Orders button and make a new order by clicking on the next arrow on the navigator buttons. Look - it's a beautiful thing!




It automatically grabbed my sales rep, tech rep, and sales tax rate from the customer form.



Review

Let’s take a moment now to review what we covered in class.

· We built an order table, an order detail table, an order query
· We built an order form using default values
· We learned how to do some VBA coding
· We built an order subform


Tell us what you think. Log on to www.AccessLearningZone.com/Survey and take a short survey about this course.


RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!


Take your skills check quiz at www.AccessLearningZone.com/Test. If you pass, you can print out a Certificate of Completion.

What’s next? Visit www.AccessLearningZone.com for our complete list of Windows courses.

Need Help? Visit www.AccessLearningZone.com/TechHelp for Microsoft assistance.

Make sure you’re on our Mailing List. Go to www.AccessLearningZone.com/Mailing for details.

What’s New? Visit www.AccessLearningZone.com/WhatsNew for a list of what’s been added.

Contact Us. If you have any questions, go to www.AccessLearningZone.com/Contact for information on how you can contact us by phone, email, or live online chat.



Don’t forget to visit our Microsoft Access Forum online at: www.AccessLearningZone.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your Access questions, get answers, and tell us what you thought of our class.



This course, handbook, videos, and other materials are copyright 2002-2006 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.

This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:

Amicron Computing
PO Box 1308
Amherst NY 14226 USA
www.AccessLearningZone.com







Permanent Link
Keywords: access handbook
Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 3+2:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

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

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
 

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