Courses - Microsoft
Access 325 very strongly recommended|
Inventory, Quantity On Hand, OldValue, Stock Levels, Ship Date, OK To Ship, Lock Paid Orders, AllowEdits, AllowDeletions, AllowAdditions, Refund an Order
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 4/23/2018 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
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
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
2.Refunding an Order
Marking Unpaid in VBA Code
Can't Hide a Control That Has Focus
Pay a Quote Bug
3. Ship Date
Add ShipDate Field
Lock The ShipDate
Show the ShipButton only if needed
Can't Refund a Shipped Order
4. Inventory Levels
Quantity On Hand (QtyOnHand)
Adding Our UserLogon Form To Database
Adding a Log feature
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
Check to see if we have enough inventory on hand
7. Shipping The Order, Part 2
Actually ship the items
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:
You may want to read these articles from the 599CD News: