Access 301 Handbook
By 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
PO Box 1308, Amherst NY 14226 USA
First Printing 7/6/2006
Copyright 2006 by Amicron Computing
All Rights Reserved
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
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
Welcome to Microsoft Access 301, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
· 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.
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:
PO Box 1308
Amherst NY 14226 USA
Keywords: access handbook