By Richard Rost 14 years ago
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 303
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 12/6/2006
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to Microsoft Access 303 – Access Developer Level 3.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 303. 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 4
Lesson 2 15
Lesson 3 19
Lesson 4 25
Lesson 5 29
Welcome to Microsoft Access 303, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Build an Order List Query and an Order List Form
· Learn more about the DoCmd.OpenForm command
· Learn why and how to duplicate address data in an order form
· Learn about the On Dbl Click event and the AfterUpdate Event
· See how and why to create your own subroutines
· Learn about Public v. Private Subroutines
· Learn about calling subs on other forms
The class follows Microsoft Access 101 - 302. I strongly recommend that you watch all previous classes before you start with this class. We will be using Windows XP in this class, but the lessons in this class are really common to all versions of Windows.
You will find a copy of the database files used in today’s class installed in the HELP folder of this course if you installed the course onto your computer.
In our first lesson, we’re going to fix problems with new orders, and create an order list query and order list form. First, I want to create a button here to open up a list of orders in an Order List form.
In our Database Window, I’m going to create a new query in Design View and bring in our OrderT table and CustomerT Table.
Next, I’m going to bring in the OrderID, OrderDate, IsPaid, CompanyName, and CompanyID fields.
I’d like to bring in the OrderTotal field as well but it’s not here. Why not? Well, the OrderT table holds all the general information about the order. But all the pricing information is kept in the OrderDetailT table. So we have to add all that up first. Let’s try that here. We’ve already built an OrderDetailQ query which gives us a LineTotal value so we can use that. Let’s add in the OrderDetailQ query and bring in the LineTotal field.
When we run it, we get the following:
We need another order or two to see how this works. So let’s go back and open up our CustomerF form and go to Show Customer Orders.
Go down to the bottom to add a new record. I’m going to add a couple of products to this order with the drop down combo box and the Add Product button.
Okay. With a couple of new orders, we can now go to a different customer and add some more the same way. But in doing so, I notice that I’m adding products to an order before the order technically existed. In other words, I’m adding products to an order that doesn’t have an OrderID. Let’s fix that right quick. Let’s make a change to the Add Product button.
In Design Mode, right-click the Add Product button and select Build Event. All we really have to do is pop up a warning message that says, “There is no order yet! OrderID is null.” So I’ll throw that in beneath the SalesTaxOverride code in the Visual Basic window.
Let’s save the changes to both the OrderDetailF form and query, but name the query as OrderListQ.
Let’s take a look and see if this works. I’ll go to a blank new record and try to put a product on it. Now it yells at me.
Now what can I do at this point? All you really have to do is change some of the data in the values up top. Anything you do up there will create the record (OrderID) for you. So I’ll put “Joe” in as our Sales Rep. That will create an order record for us. This is a good way to force our users to pick a sales representative for each record!
So while we’re here... let’s add the following code to our button.
This will force our users to pick a sales representative and create a record at the same time. But if I look at my OrderDetailT table, I’ve got a bunch of products listed without orders because the orders are blank. We can just delete all those. The only legitimate order is “1.”
We can clean up the OrderT table the same way as well.
Now that takes care of that problem! Back to creating our query, we can add a couple of orders.
Re-running our OrderListQ query, I’ve got all the fields I want.
To get a concise listing of my orders, let’s try using the Summary function on this. Remember the Aggregate Summary Function in Access 221 with this little guy right here?
All these fields should be “Group By” except the LineTotal field, which should be “Sum”.
Everything looks pretty good in query view. We can use this to make our Order List.
Let’s sort this by the OrderDate field real quick while we’re thinking about it.
I’ll save this as my OrderListQ query and make an OrderList form out of this. Go to Forms – and then New, pick AutoForm: Tabular – and then add the OrderListQ query we just made.
This lines everything up for me.
In Design View, I’ll clean this up just a little bit and make the CustomerID field invisible. This way we can still use it without having to see it. Then I’ll rename SumOfLineTotal to Order Total and format the SumOfLineTotal as Currency.
I’ll save this as my OrderListF form, and it looks a lot better.
Now when I double-click on an OrderID, I’d like Access to open up that order. If I double-click on the company name, I’d like Access to open up that company. So here’s what I’m going to do.
In Design View, right-click on the OrderID field and select its Properties.
Go to the Event tab. Next to the On Dbl Click line, I’ll click the ellipses button (Builder Button).
Then I’ll select Code Builder and hit OK.
Continuing on from the last lesson, I’ll type in the following between the Subs of my VBA form.
So we’re saying here, open the OrderF form where the OrderID is equal to the OrderID. Let’s save it and see this in action. I’ll double-click on OrderID 10 and it works!
Now let’s do the same thing with the company name. In Design View, right-click to open the CompanyName properties. Select the Code Builder on Double Click in the Event tab. Now we’re in a different sub inside the same form module. Then type in:
Docmd.OpenForm “CustomerF”,,,”CustomerID=” & CustomerID
Save it and run it. When I double-click on a company, it should open up the company’s form. And it does!
It’s helpful to add color to the fields that can be double-clicked to open up those forms.
Let’s close that and put a button for it on our PC Resale menu. (I’m just going to copy the Open Contact Form button.)
Change the button’s label to Open Order List. From the Tools menu, select Options.
Come over to the Forms/Reports tab and check “Always use event procedures” so we won’t always be asked to choose a builder.
So now when I right-click the button and select Build Event, I’ll go straight to the Visual Basic window. Next, and type in the following:
Save it and give it try. There it is!
In this lesson, we’re going to teach you why and when you want to store duplicate data in your database. We’re going to add a separate “Ship to” address, add some address fields, and see how the AfterUpdate event works for a textbox.
If you want to track the order history for customers who changed addresses for example, you will want duplicate data stored in multiple tables, like in “Bill To” and “Ship To” records. Then when you create the order, you can pull that information out of the customer form. Let’s do that.
The first thing I want to do is open up the CustomerT table and add some more fields. I’m going to copy the existing Address, City, State, Zip, and Country fields, and paste them at the bottom.
Then I’ll change the names of those fields to include “ShipTo.”
Then I’ll highlight the new fields and drag them up next to the previous Address, City, State, Zip, and Country fields.
Now we want to put these in our OrderT table as well. I’m going to copy both sets of fields, open up my OrderT table, and drop them in there right at the bottom.
Let’s save both tables. In our CustomerF Form, I’m going to slide down some fields to make room.
Then I’m going to copy the existing address information and paste it underneath.
I’ll change the first address to “Bill To Address” and the second address to “Ship To Address.”
Now we’ve got to change some stuff in each one of these fields. Open up the Properties, go to the All tab and change the Control Source to “ShipToAddress”. In fact, do this for all the “ShipTo” fields.
Save it and take a look.
Now instead of having the user have to type in this information twice, I’m going to sneak in a little event. So when I change the “Bill To” address, the “Ship To” address will update itself automatically if it’s null. I’m going to go to Properties for the “Bill To” address and go to the Event tab. Then I’ll click the builder button next to the After Update line. In the new Private Sub, I’ll enter the following:
Save it and let’s see it work. I changed the value in the “Bill To Address” field and the “Ship To Address” field updated itself with the same thing!
Now I just have to put a similar event in for the other four fields here.
For the city field:
For the State field:
For the Zip field:
For the Country field:
And we can now see it in action:
May as well adjust the tab order while we’re at it. Select Tab Order from the View Menu. Highlight all the new fields in the Tab Order window and drag them right under “Country”. Hit OK and we’re done.
Now I’ve got to get all this stuff on my Order form.
In this lesson, we’re going to put the address fields on the order form, create our own subroutines, learn the difference between public and private subs, call subroutines on other forms, and open forms in “Add-Data” mode.
So let’s take all the addresses from the CustomerF form and put them on the OrderF form. Highlight them and then copy them. Open up the OrderF form and insert (paste) the fields onto the OrderF form.
One thing I’d like to point out is that the Visual Basic code does not follow the field, but I’m not going to be bothered with that right now because we’re going to get the information from the CustomerF form anyway.
What I need to do is look up all the address information from the customer record, and pull it in when a customer is selected from the Customer field. Right-click on the CustomerID field to get it’s Properties. Next to the After Update line, click the Builder button. In the Visual Basic window, type in the following between the CustomerCombo_AfterUpdate() subs:
Address = dlookup (“Address”,”CustomerT”,”CustomerID=” & CustomerCombo)
City = dlookup (“City”,”CustomerT”,”CustomerID=” & CustomerCombo)
State = dlookup (“State”,”CustomerT”,”CustomerID=” & CustomerCombo)
Zip = dlookup (“Zip”,”CustomerT”,”CustomerID=” & CustomerCombo)
Country = dlookup (“Country”,”CustomerT”,”CustomerID=” & CustomerCombo)
ShipToAddress = dlookup (“ShipToAddress”,”CustomerT”,”CustomerID=” & CustomerCombo)
ShipToCity = dlookup (“ShipToCity”,”CustomerT”,”CustomerID=” & CustomerCombo)
ShipToState = dlookup (“ShipToState”,”CustomerT”,”CustomerID=” & CustomerCombo)
ShipToZip = dlookup (“ShipToZip”,”CustomerT”,”CustomerID=” & CustomerCombo)
ShipToCountry = dlookup (“ShipToCountry”,”CustomerT”,”CustomerID=” & CustomerCombo)
Let's set the SalesRepID too while we’re at it.
SalesRepID = dlookup (“SalesRepID”,”CustomerT”,”CustomerID=” & CustomerCombo)
Save it and open up the CustomerF form. I pick a customer and all the address information loads!
Now what about adding a brand new record? Let’s add a new button on the CustomerF form (just copy and paste the “Show Customer Orders” button) and label it “Add New Order”.
Right-click the new button to build an event. For that button, type in:
This will put me in an “add a new record” mode and it will automatically get some address information for me. To do that, I need to cut all the data out of the CustomerCombo_AfterUpdate() private subroutine and put it into a public subroutine so that this form can access it. And then I’m going to name this public subroutine, “GetCustomerAddress().”
A public subroutine is a routine that other forms can use. Now I need to tell the private CustomerCombo_AfterUpdate() sub to use the public sub. I can do that by typing in the following:
We can save that now and come over to the “Add New Order” button. In its subroutine, I’m going to add the following:
That’s how you call a subroutine in another form. Let’s see how it works. When I click the “Add New Order” button, Access opened up this form in “data add” mode and it automatically retrieved the customer’s address information. This will work for new orders of each customer.
In this lesson, we’re going to see how to get the sales tax rate into our orders and we’re going to see what happens with missing records due to our join type in a query.
To pull in the sales tax rate for a customer (like the way we pulled in address information for a customer), we can add the following to the public subroutine we created earlier.
SalesTaxRate = dlookup (“SalesTaxRate”,”CustomerT”,”CustomerID=” & CustomerCombo)
Now when I create a new order and select a customer, it will bring in the SalesTaxRate.
Next, in our OrderListQ query, we have some joins that say, “Only show me the records that are equal and matching.” If I’m missing any records, they won’t show. We need to change our join type to an outer join in case we want to see an order even if there’s no customer attached to it.
In this case, we want to select “Include ALL records from ‘OrderT’ and only those records from ‘CustomerT’ where the joined fields are equal” (for the first join).
We also want to select “Include ALL records from ‘OrderT’ and only those records from ‘OrderDetailQ’ where the joined fields are equal” (for the second join).
When we save it, we can see some 0 orders in the system.
Let’s take a moment now to review what we covered in class.
· We built an Order List query and Order List form
· We learned more about the different options of the DoCmd.OpenForm
· We learned why and how to duplicate address data
· We learned more about the On Dbl Click and AfterUpdate events
· We learned about creating our own subroutines
· We learned the difference between Public and Private subs
· We learned how to call subs on other forms
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 Windows 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, 2003, 2004 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
You may want to read these articles from the 599CD News: