Course List - Access 301

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.

Add Your Questions or Comments