Access 2007-2016
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 326
Description: Advanced Access Recordsets
Running Time: 84 minutes
Pre-Requisites: Access 325 very strongly recommended
Previous Lesson: Access 325
Next Lesson: Access 327
Main Topics: Inventory, Quantity On Hand, OldValue, Stock Levels, Ship Date, OK To Ship, Lock Paid Orders, AllowEdits, AllowDeletions, AllowAdditions, Refund an Order
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/27/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

We will be going back to our Order Form in this class. We'll begin by setting the whole order as LOCKED as soon as it's marked PAID. This way no further changes can be made to it.

 

Next we'll set up a REFUND button so that someone can take a paid order and mark it NOT paid, but at least its a separate step (and you can optionally put a password on it).

 

Next we'll add a ShipDate field and "Ship Order" button to the invoice. Click on the button, and the invoice will be marked SHIPPED.

 

Next we're going to start tracking inventory levels. We'll add a Quantity On Hand field to our product table and form.

 

We'll add a note in our System Log any time a user changes an inventory level so we can see EXACTLY where our products are going.

 

Next we'll examine a problem we have where if we add the same product multiple times, we get multiple line item entries in the order.

 

We'll write some VBA code so that if you click on Add Product multiple times for the same product, it just increments the Quantity field. This is a much nicer result.

 

Next we'll start working on the process of shipping an order. First, we'll check stock levels. If we can't ship an order because we don't have enough inventory, the database will yell at you.

 

Now we'll write the code to actually remove the items from inventory once they're shipped. This all happens behind the scenes, but it updates your product table.

 

 


Access 326 - Course Outline

1. Locking Orders When Paid
Pull Databases Back Together
Lock the Order if it's Paid
Me.AllowEdits
Me.AllowDeletions
Me.AllowAdditions
OrderSubform.Form.AllowEdits
OrderSubform.Form.AllowDeletions
OrderSubform.Form.AllowAdditions

2.Refunding an Order
Refund Button
Marking Unpaid in VBA Code
Can't Hide a Control That Has Focus
Pay a Quote Bug
Me.Undo

3. Ship Date
Add ShipDate Field
Lock The ShipDate
Show the ShipButton only if needed
ShipOrder Button
Can't Refund a Shipped Order

4. Inventory Levels
Quantity On Hand (QtyOnHand)
Adding Our UserLogon Form To Database
Adding a Log feature
Field.OldValue
Tracking which employee changed inventory level

5. Items Already On Order
Add QtyOnHand to Product Combo on OrderDetailF
Increment Quantity if already on order
Warning if no product picked
Create a recordset to increment quantity
Compile error: ByRef argument type mismatch

6. Shipping The Order, Part 1
OKToShip Function
Check to see if we have enough inventory on hand

7. Shipping The Order, Part 2
Optional Parameters
ChangeInventory function
Actually ship the items


 

 

Student Interaction: Microsoft Access 326

Richard on 1/1/2008:  Locking orders when PAID, Refunding an order, Ship Date, Inventory Levels, On Order, Shipping the Order
T-Bone on 8/31/2009: I will need to know how to do a partial shipment when there is only 2 in stock but customer needs 5 of that product.
Richard Rost on 8/31/2009: A little later in Access 329 we'll cover creating Purchase Orders. I'll show a technique for receiving only partial shipments from vendors. The same technique can be applied to making partial shipments to your customers.
Ray McCain on 10/2/2009: The user logon will work properly as a separate database. When I import it into Access 326, I have a problem. When the user name and password are both used, there is a type mismatch error. The user name and password are being read correctly, but then treated as though the username was null. The following statement turns yellow when the debug runs Set rs = db.OpenRecordset("Select * from UserT where Username='" & UserName & "' and Password='" & Password & "'"). How can this statement work properly in one database, and then not work when it is imported?
Richard Rost on 10/4/2009: Ray, the only thing I can think of is that you have a REFERENCE error. Do you have DAO listed in the references BEFORE ADO? Aside from that, I don't know what it could be.
Ray McCain on 10/5/2009: At your suggestion, I checked the references. I did not have ADO objects library check and only had DAO library checked. I did find that Active X data object 2.1 library was listed before DAO 3.6 object library. I removed the active X data object library and then added it back. This put it in an order after the DAO object library. With that change, everything in the LogonF works property.

I was using the after 326 folder that I created from the student folder, and it came with the reference librars as shown above. I am surprised that I am the only one to come up with a problem.

Thanks for your suggestion.

Ron on 1/13/2010: hi - Ray's problem happens to me sometimes too. Access thinks that the username is null. I can't figure out why it happens sometimes and not other times. I try to display the username with a messagebox and it shows nothing. Maybe it has something to do with importing the stuff from one database to another? It happens to me in Access 2007.
Sopheak Ke on 8/17/2011: Hello from Cambodia
My name is Sopheak. I am a new beginner of access programming. Please do not mind me if I ask you with a wrong question. My idea I just want you to show and help me how to use MS access VBA like accounting system: General Ledger, T-Account DR/CR, Balance sheet, Income statement or cash flow, and inventory with invoice sale/purchase, SO or PO and a very special need for me is REPORT summary and detail like for inventory report filter by from DATE and to DATE(beginning Stock(Qty) with value, Purchase stock(Qty) with value, Sold stock(Qty) with value, and Ending stock(Qty) with value.... and more).
I hope you will smile before writing to me and I strongly do hope you can surely help with this question.
Please please please tell me if you don't hesitate to show me your kindness. I love your PRODUCTS.
From me, Sopheak (Asian People).
Bye Bye. God bless your business. Thanks.

 Jim Gray on 4/9/2012: Richard,
Would it be fairly easy to use set up code to log any changes made to specific inventory numbers? eg. shipping, purchase order reciepts, manual adjustments, etc. Basically the same type code used on a user level but attached to inventory items instead? I am thinking it would be good info for tracking variances and how they happened. Thanks as always for your great classes.

Reply from Richard Rost:

Probably the easiest thing to do would be to create a ModifiedDate field in your table. Whenever a record is changed or added, set the ModifiedDate. This way you could go through and look at all of the recently updated records.

Richard Lanoue on 9/8/2014: on Course 326 Lesson 6, yes I would like to see partial orders system.
Robert Whishaw on 12/17/2015: Due to the fact that the form is locked when an item is paid how can we exclude the "Ship Order" button from becoming locked?
I don't know how/why your ship order button didn't get locked down like everything else.

Reply from Alex Hedley:

I'm sure you can set the .locked property in VBA so you could just have that in your open event

Khalid Ahmed Sheikh on 11/27/2016: sir i need type of form please help me solve this problem

Reply from Alex Hedley:

What help?

 

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