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 302
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 12/6/2006
Copyright 2006 by Amicron Computing
All Rights Reserved
Welcome to Microsoft Access 302.
This handbook is designed to be a supplement to the full AccessLearningZone video course for Microsoft Access 302. 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
ProductT & ProductF 4
Product Combo 9
Getting Data From Combo Box to Form 14
Welcome to Microsoft Access 302, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Objectives for today’s class:
Build a product table and a product form
Build a product combo box on an order form
Use columns to put data into an order form
Learn how to get data by using the DLOOKUP function
Learn more Visual Basic commands
The class follows Microsoft Access 301. 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 at http://www.AccessLearningZone.com/Access/302. You will also find them installed in the HELP folder of this course if you installed the course onto your computer.
ProductT & ProductF
In this lesson, we’re going to begin by building our product table and a quick product form to go with it. I’m going to open my before 302.mdb database:
Here we are at the main menu of our PC Resale database. What we’re going to focus on today is adding a product table to our system.
Wouldn’t it be nice if I could have a drop down box on our existing order form that shows a complete list of products?
We could take a product from a list, and hit “Go,” and it would add all this information in here for us. So let’s go to our database window and go to Tables. We’ll create a new product table that will store and list all our products. Go to Tables and hit New. Select Design View.
The first thing any table should have is its own ID. We can add a Description field, Notes field, UnitPrice field, IsTaxable field, SalesTaxOverride field (as a decimal number field with a default value of null - and a scale of 4).
Let’s go ahead and save this table as ProductT and let Access define a key for us. Now we can put some products in under Table View.
If you’ll remember, the OrderDetailT table lists all the details for each order. So the fields should match, but I’m missing my ProductID. I’d like my ProductID to show in the OrderDetailT table as well. So let’s add that to the OrderDetailT table.
I’m going to insert it after the OrderID field.
Right-click and select Insert Rows. Then type in ProductID as a Number field.
Let’s go ahead and save that. There's one more thing I’d like to add to my product table, and that’s a picture (an OLE Object).
Now let’s save that and throw together a real quick Columnar AutoForm for the ProductT table.
Nice and fast!
I just want to make a few quick changes to the form in Design View. I’m going to left-justify all the fields, resize the fields, and change a couple of colors. Then we can save it as our ProductF form, close it, and take a peek at it.
In this lesson, we’re going to begin building the Product Combo Box so we can pick our list of products out of the product table and store that information from the Order Details form. Let’s open our OrderDetailF form.
In Design View, we can stick it in the form footer. Let’s open up the ToolBox, turn the Wizard on, and then drop a Combo Box onto the form’s footer.
We’re going to look up the values in a table or a query. And we’re going to get these values from the ProductT table. Bringing the fields over to the combo box, notice that the Notes field and the TaxOverride field is missing.
We need another way to get that information in there but for now, let’s just add the ProductID, the Description, and the UnitPrice.
Now the way we have this set up, I have to bind my data using VBA code. So we’re going to select “Remember the value for later use” and use a button to retrieve information into the order form.
In the properties for this combo box, we can set the name to ProductCombo. Then I’ll sort the information in the combo box through the Row Source by sorting the Description field.
Back in Design View, I’ll get rid of the combo box label and save it. Let’s take a peek and see if it works.
There’s my list of products!
But if I click on one of the products, nothing happens. What I need now is a button. From the Tool Box, I’m going to drop a command button right next to the combo box.
I’m going to change that label to something more descriptive like “Add Product.” And then I’m going to give it a good name in its properties, like AddProductButton. Next, I’m going to right-click on the button and select Build Event. This will let me control what the button does.
The Choose Builder window pops up. We’re going to pick Code Builder.
The Visual Basic window pops up.
Between Private Sub and End Sub, I’m going to type MsgBox ProductCombo.Column(1) This will show me the Product ID and value inside the column number on a message box.
Now that we know how to get information, we can put in onto the order form.
Getting Data From Combo Box to Form
In this lesson, we’re going to get that data out of the combo box and onto our Order SubForm. We can do that by setting the boxes on the Order SubForm equal to whatever is in the combo box. Back in our VB code, we’ll type the following between the Private Sub and End Sub.
In Form View, let’s see what happens. I’m going to work in the first row, click on Add Product, and watch Access add the item in the combo box to the row I’m in.
If I’m on a blank, new record (row) and I hit Add Product, it adds the product!
But I don’t want to change a record, I want to add a record. So let’s go back to the code window and insert the following above our current line of code:
That code basically just means go to a new record on the current form. Then, set the description equal to the value in the product combo box. After we save it and watching it work, it’s exactly what we want. Only problem is, it’s not pulling in the UnitPrice or any of the other information. That’s easy to add, though.
We know where the UnitPrice is, right. We can type that into the button’s code and it will add the product’s price in the appropriate column as well.
Let’s do ProductID next. We need to add the ProductID to the form first, so I’ll copy the OrderID and then paste it onto the form with the other IDs.
The properties for that ID is Text22. In it’s properties window, we need to change its Control Source to ProductID and its Name to the same thing.
Now let’s add it to our code above the description code.
Working good so far. Now we’ve got all the stuff from the table into the Order SubForm.
DLOOKUP is a function that we’re going to use to look up values from a table or query. We can’t necessarily put all the data that we want to get onto our order form into our combo box – especially when we’re dealing with hundreds or thousands of products. Instead, we need a function that searches for a product, and then puts it onto the order form.
Go ahead and close the order form (if you have it open) and go back into the CustomerF form. Click Show Customer Orders.
On the bottom, we have our Order Total. If I add a product, the total doesn't change. Why? See the little pencil all the way to the left? Technically, I’m still editing this record.
When I move off that record, the Order Total will update. If you’re using this database as a POS system, this could be a problem. So we need to force Access to refresh this information. Let’s bring up our VB window and add the following line under the UnitPrice code.
This will refresh the Order SubForm and update the Order Total. Now let’s tackle some of the more bigger problems.
DLOOKUP is short for Domain Lookup. A domain is a table or query and DLOOKUP requires the answers to What, Where, and How. Back in the code window, let’s DLOOKUP some information. Let’s look up notes. In the code window, type in:
Note = DLOOKUP(“Notes”,”ProductT”,”ProductID=” & ProductCombo)
This says, look up the notes field, from the product table, with a product ID that’s a value from the product combo box, and then put all that information in the Notes field. Let’s save it and see if it works. And it does!
There’s one more bit of information we need. We need to find out what is taxable and what tax rate to use. We need to figure out what the sales tax rate is first. Now, the default value for the sales tax rate is the sales tax rate of the order.
There are two instances where I have to change that. The first instance is if the product is not taxable. The second instance is if there’s a sales tax override. Let’s tackle the first problem first.
In the code window, I’m going to create my own variable because there’s no field that stores this information. Right under the Private Sub, I’m going to enter the following.
Boolean is a Visual Basic word for a yes or no value. Dim means create a variable called IsTaxable as a yes or no value. This variable only exists inside of this private sub – this button.
Beneath the Notes code, enter the following:
IsTaxable = DLOOKUP(“IsTaxable”,”ProductT”,”ProductID=” & ProductCombo)
Remember, there isn’t a field on the form for me to put this on. That’s why we created a field with the DIM variable. For the next step, type:
If IsTaxable is true, we don’t have to worry about it because the sales tax rate is automatically set for us on the form.
When we run the database, Access inserts tax when appropriate, and inserts 0 when tax is not needed.
One more piece of the puzzle: our tax override for different tax rates (thank you state government). We’ll do this by looking up our sales tax override.
First, let’s fix Access so that it doesn’t have a cow whenever it tries to look up something that doesn’t exist. This fix is error checking. The first line of our code will now be:
This tells Access to keep working even if it finds an error in our code. Now let’s add another local variable (DIM).
And now let’s create another LOOKUP beneath the IsTaxable LOOKUP.
SalesTaxOverride = DLOOKUP(“SalesTaxOverride”,”ProductT”,”ProductID=” & ProductCombo)
Now we can add the following:
If Not Isnull(SalesTaxOverride) Then SalesTaxRate = SalesTaxOverride
This tells Access to set the SalesTaxRate to the SalesTaxOverride only if the SalesTaxOverride is not null (if it isn’t zero).
When we add products to the database, Access will insert the correct tax when appropriate, or insert zero when appropriate regardless of the default tax set for the form.
Let’s take a moment now to review what we covered in class.
· We built a product table
· We built a simple product form using a wizard
· We made a working product combo box on our order subform and a command button to go with it
· We used the columns of that combo box for some of the items
· We used the DLOOKUP function for some of the other items
· We also learned some new VB stuff: Comments, Dim, IF/Then, and Error
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 Access 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, 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: