Access 2007-2013
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 301
Description: Advanced Access
Running Time: 74 minutes
Pre-Requisites: Access 223 very strongly recommended
Previous Lesson: Access 223
Next Lesson: Access 302
Main Topics: Order Entry, Sales, Line Items, Sales Tax, Beginner VBA, OpenForm, Continuous Forms, Subform Totals
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/3/2014 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 301
Advanced Access Development

Order-Entry System, Orders & Line Items, Calculate Sales Tax, Form/Subform, Subform Totals, Beginner VBA, Lots More. 74 Minutes.
 

AC301 Major Topics

  • Order-Entry System
  • Create Order Tables
  • Line Item Information
  • Sales Tax Rate
  • Order Form / Subform
  • Beginner VBA Programming
  • DoCmd.OpenForm
  • Continuous Forms
  • Subform Totals

Access 301 is the first of our "developer level" advanced Access courses. We will build a complete order-entry system (sales, products, inventory, accounts receivable, etc.) We will also learn VBA programming (Visual Basic for Application).

This is the course you've been waiting for! Don't miss it. Our goal is to create a full-featured order-entry system with printable invoices.

 

In this class, we will begin by constructing the Order Table and Order Details Table. The Order Table holds the information about the overall order, whereas the Details contains information about the items (products) in the order (a many-to-one relationship... remember that?)

 

We'll enter some sample order data so you can see how the relationship between OrderT and OrderDetailT is going to work.

 

We'll see how to create a query to calculate our line totals, sales tax rate, actual sales tax due, and more calculations.

 

Next we will create an Order Form. We'll start out by adding Sales Rep and Tech Rep combo boxes to our form - but these are both basically Employees, so we'll bind two different fields to our Employee table.

 

We'll get our introduction to VBA (that's Visual Basic for Applications) which is where the real power behind Microsoft Access is hidden.

 

Next we will create the Order Subform to show the line item details for each order:

 

You will learn how to put Totals in your subform's footer.

 

Next we'll set a default Sales Rep and Tax Rate for each Customer...

 

...and have that information automatically carry over to any new orders that are created for that customer:

 

This course is just the first in our series of creating an order entry system. By the time we're finished, you'll have a fully-functional database that you can use to run any business.

 

Access 301 Outline
 

1. Create Order Tables
Create the OrderT table for data on an Order
Create the OrderDetailT for line item data

2. Order Queries
Enter sample order data
Create OrderDetailQ
Calculate Extended Price (Price * Quantity)
Round() Function
Format as Currency
Field for Sales Tax Rate
Calculate Actual Sales Tax Value

3. Order Form
Create a SalesRepID to Track Salesman
TechRepID for Technical Support
Different IDs for the Same Field Type
Design the Order Form
Choose Builder - Code Builder
Learning VBA (Visual Basic for Applications)
On Dbl Click
Event Procedure
Private Sub
End Sub
DoCmd.OpenForm
OpenForm and show a specific record
Where Condition

4. Order Subform
Create the Order Subform from scratch
Continuous Forms
Subform Control
Turn off Navigation Buttons

5. Subform Totals
Showing the Totals in the Subform Footer
More with SUM() Function
Get Line Item SalesTaxRate from Order Form
Create a Button to Show Orders Only for Current Customer
Sales Tax Rate Per Customer, Per Order
Setting a Default Sales Rep for each Customer

 

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 301

Richard on 1/1/2007:  Access 301 is the first of our "developer level" advanced Access courses. We will build a complete order-entry system (sales, products, inventory, accounts receivable, etc.) We will also learn VBA programming (Visual Basic for Application). Begin Order Entry System, Calculating Sales Tax, Order Form, Beginner VBA (Visual Basic) Programming
PAUL TIBI TENDO on 7/31/2008: how can change the currency from us dollars to other currencies
PAUL TIBI TENDO on 8/2/2008: how do you have multi-currency values if you have international orders for different customers in different countries.
Richard Rost on 8/23/2008: This is something that you have to do in WINDOWS, not Access. Go into your Control Panel and under Regional Settings you should see an option to change your currency type. Same with Date & Time options (European vs. American formats).
Richard Rost on 8/23/2008: Paul, I haven't tackled that one... multi-currency values would be tricky, but you could do it with multiple fields, I suppose.
 ammar on 9/6/2008: how can i link crystal srports with ms access ?
Richard Rost on 9/8/2008: Ammar, I apologize, but I have almost NO experience with Crystal Reports. I haven't used it in YEARS. Why? Because you don't NEED it. Access alone can do just about everything Crystal can. There's really no need for it.
Richard on 10/3/2008: IMPORTANT ADDENDUM: After you make your OPEN ORDERS button, you will notice that if you add a customer and then immediately try to add a new order BEFORE you save the customer record to the table (move off of it, or close and reopen the form) the customer will not show up in the combo box on the order form. This is because the customer doesn't exist in the customer table yet.

You can fix this problem by making sure you SAVE the record to the table first. The easiest way to fix this is to put the following line BEFORE your DoCmd.OpenForm line:

Me.Refresh

This refreshes the current record (saves it to the table) and then when you visit the order form, it's there.

I cover Me.Refresh in detail in Access 305, but I didn't think of this problem at the time I made this class.

 George on 11/17/2009: Great Tutorial!!! gonna get 302,303 and 304. Have a question about the order form though. I have all default values based on other fields in other forms in the top portion of the order form. But when I tab down to the subform to actually put in entries, The autonumber field for the Order ID doesn't execute. Won't even do so with a field that I may choose from a dropdown. It only works if I actually type in one of the upper level fields in that form. Is there a way to get around this?
Richard Rost on 11/17/2009: George, the records in the subform are dependent upon there being a record in the main form. If you're specifying default values, that's great, but your record still has no VALUES in it until you enter that first bit of data. Then all of the default values become real values. You can check to see if the ID in the parent form IsNull and then throw up a warning message (or even set a value manually) but there has to be something in the parent form in order to get that value to make the relationship with the subform.
Cheri on 12/30/2009: I have a question, Richard. I am trying to create a purchase order system. I have an Item table with basic information about products and an ItemDetail table with information about sizing and prices per size. This is a separate ItemDetail table because not all products have these size attributes. When I go to create the Purchase Order Form, I am not sure how to set it up so that I can choose first the Item, and then be able to choose the size (and then have the price populate from there).

Thanks for your help.

Richard Rost on 12/30/2009: Cheri, here's how I would set it up. I would have a ProductT table with basic information about each product:

ProductT
--------
ProductID (Autonumber), Description (Text)
1, T-Shirt
2, Long Sleeve Shirt
3, Jeans
4, Shorts

Now, I'm assuming that your price is based on the SIZE, but the color doesn't effect the price. So I would have a ProductSizeT table, with records for each Product item:

ProductSizeT
------------
ProductSizeID (Autonumber), ProductID (Long), Size (Text), Price (Currency)
1, 1, S, 1.99
2, 1, M, 2.99
3, 1, L, 3.99
4, 1, XL, 4.99
5, 2, M, 2.99
6, 2, L, 3.99
7, 3, L, 5.99
8, 3, XL, 9.99
9, 4, One Size Fits All, 9.99

So you can see now how each Product item has the available sizes listed, with their prices (four for T-shirt, 2 each for Long Sleeve and Jeans, and one for Shorts).

Now you can also have a table for the available colors, but since this doesn't effect the price, it's just something else to select:

ProductColorT
-------------
ProductColorID (Autonumber), ProductID (Long), Color (Text)
1, 1, Red
2, 1, Blue
3, 1, Green
4, 2, Red
5, 3, Black
etc.

None of this is really new for students who have taken my Access 200-level classes. I cover this kind of stuff in AC201 on.

Now comes the interesting part. You need a way for the user to select an item, then pick the size and color from the available items FOR THAT PRODUCT. This will involve creating combo boxes that have data in them that is refreshed when the product changes.

I would make my standard OrderT order table, like I do in Access 301 (customer data, order date, etc.) and then for the OrderDetailT you're going to need to store all of the above information:

OrderDetailT
------------
OrderDetailID (Autonumber), OrderID (Long), ProductID (Long), ProductSizeID (Long), ProductColorID (Long), Description (Text), Price (Currency)

You'll make a series of combo boxes on your OrderDetailF form that will allow you to pick each of the necessary items. You'll pick a product first, and then using the AfterUpdate event for the product, you'll REQUERY the results in the Size and Color combo boxes to show ONLY the results for THAT product that you picked.

I cover something almost exactly like this in Access 313 where we create a Product Category so you can pick a category first and then it will show only the products in that category

Give this a try, follow the example in Access 313 first, and if you still have questions, let me know. This would make a good followup video for a future lesson.

Aneline on 5/6/2010: I know how to sum up total in a report, but when I try to sum up percentage, I get error. Please help. Thank you.

Reply from Richard Rost:

I would need to know more about what you're doing in order to help you with this. Explain your setup to me.

Cheri on 8/3/2010: Hi Richard. I have a follow-up question to my previous question and your answer from 12/30/09. I think I can understand/figure out the requerying that you were talking about (I purchased and watched all of the relevant lessons, I think). But I am stumped on trying to figure out how to have the tables work if only some of my items have sizes. For example:

Item, Price, Size
Tape Player, $4.99, no sizes
Jacket, Small, $3.99
Jacket, Medium, $4.99
Jacket, Large, $5.99
Radio, $5.99, no sizes

Where do I put the sizes to avoid redundancy? Basically, I am trying to create a purchase order system for my inventory, but don't understand how to set it up when only some items have the size attribute, and then knowing where/how to pull the price when I create the Order Detail continuous form for line items to put in the OrderF. Do I need to set up a query or something to pull all that together?

I hope this makes sense!

Thank you so much.

Reply from Richard Rost:

I'd need to know more about how you want to set your tables up. For example, if you just want to display the sizes that are available, just make a text box that will contain "S,M,L,XL". However, if you want to have different prices for different sizes, for example, then you would either need a second (sub) table, OR just make multiple entries for each size.

 Janine on 9/22/2010: You are a fantastic tutor, have been on several courses before for Access never understood, you have made it very enjoyable and I want to learn more. Thank you
  on 10/14/2010: In minute 4.00 of this 301-Video2 tutorial you mention that you are going to talk about a way to force invoices to have a secuencial number wich involves some VBA Programing. Did you finally talk about this in a class? Wich one? I am stuck with this in my databese and need to solve it right away. Great tutorials, all of them.

Reply from Richard Rost:

I believe I talked about this in Access 306 with the DMAX function. Here's a free TIP that shows you how to do it.

Janine Harrison on 1/9/2011: Please accept my ignorance, but in the sample database you build throughout the courses, you use queries to build relationships and do not use the relationship button so no links are shown on the tables,or forms. Am I missing something? Do you not use this in any of the courses,i.e after the intermediate stage. I am very new to Access and am working through all your classes, but am a little confused.

Reply from Richard Rost:

Janine, I only use Global Relationships if I need to enforce Referential Integrity in my databases. Most of the time, I don't bother... but I do cover it in later courses. See this thread in the Access Forum for my reasons why.

Willem on 5/26/2011: How do I create a form and email the information on the current form without an attachments.
The info needs to go from the form to the subject line or message body

 eddy geijselaers on 6/2/2011: Hi, am I the only one who is unable to download the intro on 301? The other clips where downloadable.
Grtz eddy

Alex Hedley on 6/2/2011: If you downloaded it through the 599CD player program and you're having problems log into your account through a web browser, go to your courses page and download the lesson (.exe) from there, (think there are 2 links, different servers) then install from that and it should work fine
Sometimes it's just an Internet connection problem etc bit this method usually works fine :)

Cheryl Hokanson on 11/29/2011: 302 lesson 2,00:34
When I try to open the tables side by side they take the place of each other and aren't seperate. What do you suggest? Cheryl

Abdel  on 6/7/2012: Hey There!
What if my prices in the orderdetailQ are from another table? (i.e. fixed according to maxprice)

Reply from Richard Rost:

Join together the two tables using a relationship.

steve on 9/21/2012: Richard, I've watched the 300 series videos a few times and have a question. If I had a budget in place for my order(s), (($500 for supplies) ($100 for mailing), and ($100 for shipping cost)), how would you set up a table and relationships for this? OrderT, OrderDetailsT, and BudgetT I am assuming. So on the particular order I want to spend $50 on supplies (one budget amount) but there is a $5 dollar shipping cost charge (another budget amount), how would you set this up? It sounds crazy to seperate the two (normally it would fall in the same), but for my company we seperate them. Could you get away with a yes/no box and later define yes=shipping budget?

Reply from Richard Rost:

What I have done in the past is to set up a Budget Category table and then place each ITEM in a category (office supplies, shipping, computer hardware, etc.) then you can calculate an exact cost against your budget for each category.

steve m on 9/21/2012: Good, that is what I currently have set up. The main OrderT is linked to fk_BudgetID which works fine, but linking a secondary budget is creating a problem. Do you think my solution of a yes/no (shipping) box in the OrderDetailT is a good route for handling the cases of shippings? Where would you put the expression to show this amount later on because if shipping is (true), you would not add to total.
For example, Total = Sum(Subtotals), Shipping = Shipping Box, Final Total = Total+Shipping
Or do you think there is an easier way to pull shipping totals? Either way, shipping would need to be a line item in this case, with my line item range being 1-10.
Thank you!

Reply from Richard Rost:

Yes if you need to do it this way, having shipping as a line item is fine. I thought of building my database that way in class, but opted not to. Shipping as a line item also works if you have partial shipments because you might have multiple shipping charges.

You could do two totals in your form footer - one for shipping items and one for non-shipping items. You could even make "shipping" a category like we talked about earlier and just add up items in that category.

BEN CHUA on 4/23/2013: Where can I put ProductCode if I have a code on each product? Where should I record this value in OrderDetailT or OrderT table?
If this does not make sense, Should I create a ProductT, where I can assign a ProductCode for each product?

Reply from Richard Rost:

Yes, that would go in the OrderDetailT. I believe this exact lesson is coming up soon (Access 302 in fact).

SYED KHURRAM on 6/28/2013: Hi Richard,
Need some help. As i told you before I have already created my CustomerT, OrderT, OrderdetailT and my OrderDetailQ with some calculations. I checked the query it is functioning good. Now I want to create a form. In my existing fields my query is not there how can I bring it to my existing fields. You told me the directions are in Experts 2010 1 I just bought it could not figure it out please help.Thanks Syed

Reply from Richard Rost:

Make sure you set the form's RECORD SOURCE property to the query. Then all of the fields in that query should show up there.

Ramona Woitas on 11/17/2013: Hi Richard, I need some help. I am stumped and not sure what I did or did not do.

I am adding the sub form (InvoiceDetailF) to my main form (InvoiceF). I have followed along with the video but when I view my InvoiceF after the sub form is brought in, it doesn't show all 5 line items like yours does.

What really has me confused is that the InvoiceDetailF DOES show me the 5 line item details.

My InvoiceF is based on InvoiceT
My InvoiceDetailF is based on InvoiceDetailQ and my InvoiceSubForm is based on InvoiceDetailF.

Also if the above is correct, then why can't the sub form be based on the InvoiceDetailQ?

Please Help! Time Index 8:49

Ramona on 11/17/2013: Richard! I apologize. I am a dope. I deleted the InvoiceID records in question. It works fine now. thanks. Sorry to bother you!
 

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
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