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

Microsoft Access 301
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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

Permanent Link
Course Link: Microsoft Access 301
Keywords: access vba sales tax
Post Reply

Comment from Ramona Woitas @ 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
Show Just This Thread        Post Reply
Query not there Comment from SYED KHURRAM @ 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.
Show Just This Thread        Post Reply
Where can I put ProductCode Comment from BEN CHUA @ 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).
Show Just This Thread        Post Reply
Budgeting Comment from steve m @ 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.
Show Just This Thread        Post Reply
Budgeting Comment from steve @ 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.
Show Just This Thread        Post Reply
Comment from Abdel  @ 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.
Show Just This Thread        Post Reply
Comment from Cheryl Hokanson @ 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
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 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 :)
Show Just This Thread        Post Reply
Comment from  eddy geijselaers @ 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
Show Just This Thread        Post Reply
Comment from Willem @ 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
Show Just This Thread        Post Reply
One to many relationships Comment from Janine Harrison @ 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.
Show Just This Thread        Post Reply
Comment from   @ 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.
Show Just This Thread        Post Reply
Comment from  Janine @ 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
Show Just This Thread        Post Reply
My Question from 12/30/09 Comment from Cheri @ 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.
Show Just This Thread        Post Reply
Comment from Aneline @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 12/30/2009
Cheri, here's how I would set it up. I would have a ProductT table with basic information about each product:

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:

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:

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

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:

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.
Show Just This Thread        Post Reply
Comment from Cheri @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from  George @ 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?
Show Just This Thread        Post Reply
Comment from Richard @ 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:


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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from  ammar @ 9/6/2008
how can i link crystal srports with ms access  ?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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).
Show Just This Thread        Post Reply
Comment from PAUL TIBI TENDO @ 8/2/2008
how do you  have  multi-currency  values if you have international orders for different customers in different  countries.
Show Just This Thread        Post Reply
Comment from PAUL TIBI TENDO @ 7/31/2008
how can change  the currency  from us dollars  to other  currencies
Show Just This Thread        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


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 2+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

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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