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 328
Description: Advanced Access Recordsets
Running Time: 81 minutes
Pre-Requisites: Access 327 very strongly recommended
Previous Lesson: Access 327
Next Lesson: Access 329
Main Topics: Reorder Levels, Vendor Info, Purchase Orders, Triple State Check Boxes
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'll begin by adding reorder levels and vendor information to our product table, so we know when we should order more of a product, and from whom to order it.

 

We'll add this information to our Product List form, so we can quickly see at a glance what products need to be ordered. A little Conditional Formatting makes those items stand out.

 

We'll create a Purchase Order table to track items that are On Order. Next we'll make an "Order More" button on our product list form.

 

The system will prompt us for how many items to order.

 

The items then get added to the Purchase Order table. We'll build a form where we can review all of the items that are to be ordered, filtered by vendor.

 

We'll finish up the purchase order, including a printable report, in the next class, Access 329.

 


Access 328 - Course Outline

1. Reorder Levels
Add Fields to Product Table
Reorder Level
Vendor Information
Quantity On Order
Vendor Table
Add To Product Form
Query to show Products Understocked

2. Purchase Orders
Products Understocked Form
Purchase Order Table
Purchase Order Details Table

3. Order More Button
Create Order More Products Sub
InputBox to Ask How Many
Convert string to number: CLng function

4. Make Purchase Order, Part 1
Create the Make Purchase Order Code
Creating a PO with a Recordset
Get the assigned Autonumber for a record
Check if there is a PO for this vendor

5. Make Purchase Order, Part 2
Check if this item is already on a PO
Use recordsets to create the PO and details
Verify table data

6. Purchase Order Form, Part 1
Create Purchase Order Details Subform
Create an unbound ListBox to show all POs
OnClick event to show only items on selected PO
Bind the form to Purchase Order Table
Show Notes

7. Purchase Order Form, Part 2
Check Box to Filter Open/Closed POs
Check Box to Filter Received/Not POs
Tri-State CheckBoxes (Triple State)
QuickLinks to Set the Checkbox Values


 

 

Student Interaction: Microsoft Access 328

Richard on 1/1/2008:  Reorder levels, Purchase orders
Ray McCain on 10/23/2009: I would like to see the material on partial shipments/orders (both out and in)
Ray McCain on 10/25/2009: The following sub routine produces two errors:

Private Sub Form_Open(Cancel As Integer)

PODetailUpdate

End Sub

Private Sub POList_AfterUpdate()

PODetailUpdate

End Sub

Private Sub PODetailUpdate()

If IsNull(POList) Then
'no PO selected - show no records
PurchaseOrderDetailsF.Form.RecordSource = "Select * From PurchaseOrderDetailsF Where PurchaseOrderID=-1"
Else
'a PO selected, show just those records
PurchaseOrderDetailsF.Form.RecordSource = "Select * From PurchaseOrderDetailsF Where PurchaseOrderID=" & POList
End If


End Sub

When the sub runs in the after update event, the following error is reported:
The recordsource 'Select * From PurchaseOrderDetailsF Where PurchaseOrderID=1' specified on this form or report does not exist

When the sub routine runs from the OnOpen Event, the following error is reported:
The recordsource 'Select * From PurchaseOrderDetailsF Where PurchaseOrderID=-1' specified on this form or report does not exist.

What is my problem?

Richard Rost on 10/26/2009: First, try referencing just:

PurchaseOrderDetailsF.RecordSource

Second, you can't use a FORM as the source of data. That would need to be a TABLE or QUERY:

SELECT * FROM PurchaseOrderDetailsT WHERE ...


Ray McCain on 10/27/2009: I am looking at a compile error in buildpolist().

When the second occurrence of "WhereStr=WhereStr" is used and following " AND ", the first WhereStr is selected and the error message "Expected: End of Statement"

How do I overcome this error?

Private Sub BuildPOList()

Dim S As String
Dim WhereStr As String

S = "Select PurchaseOrderID, VendorName, PODate From PurchaseOrderQ"
WhereStr = ""

If ShowOpenPOs = True Then
WhereStr = "IsOpen=true"
ShowOpenPOsLabel.Caption = "Open POs"
ElseIf ShowOpenPOs = False Then
WhereStr = "IsOpen=false"
ShowOpenPOsLabel.Caption = "Closed POs"
Else
ShowOpenPOsLabel.Caption = "Open && Closed POs"
End If



(Compile error occurs at the beginning of the second "WhereStr=WhereStr") (When the second "WhereStr=WhereStr" is
omitted, as in the elseif statement, below, the error does not occur)


.................
Compile Error
Expected: end of
statement
.................
If ShowReceivedPOs = True Then ________
If WhereStr <> "" Then WhereStr = wherstr & " AND " WhereStr=WhereStr & "PartsReceived = True"
--------
_________________
.................
ShowReceivedPOsLabel.Caption = "Received POs"
ElseIf ShowReceivedPOs = False Then
If WhereStr <> "" Then WhereStr = wherstr & " AND " & "PartsReceived = False" (I omitted the second use of WhereStr)
(and did not get the error in this statement)
ShowReceivedPOsLabel.Caption = "UnReceived POs"
Else
ShowReceivedPOsLabel.Caption = "Received && Not"
End If

Richard Rost on 10/27/2009: Ray, you can't have ANYTHING after the "THEN" or else VB assumes it's a SINGLE LINE command. For example, this is fine:

If X=A Then MsgBox "B"

But you can't say this:

IF X=A Then MsgBox "B"
MsgBox "A"
End If

You would have to say:

IF X=A Then
MsgBox "B"
MsgBox "A"
End If

So it looks like VB is looking for the END OF STATEMENT because you've got a command after the THEN in your IF/THEN block. Just move that down to the next line and you'll be fine.

Ray McCain on 11/4/2009: The value for PODID always returns as 0, so that the else statement never adds to an existing PO.
I tried removing the nz operator just to see if it would run in this format and was told that I was using an invalid use of null.

Every time I order more product, it creates a new PO, rather than choosing between creating a new PO and adding to an existing one.

PODID = Nz(DLookup("PurchaseOrderDetailsID", "PurchaseOrderDetailsT", "PurchaseOrderID=" & POID & " and ProductID=" & ProductID), 0)

I must have done something wrong to come up with this result

Richard Rost on 11/6/2009: Ray, your statement look correct, but you must have done something else to cause that problem. Without seeing your database, it's impossible to tell. Try starting again from the beginning.
Ray McCain on 11/10/2009: PurchaseOrderF

Check boxes do not continue to function

(Normal function)
When the form first opens, Open PO's is checked and shows all open PO's
Checking Closed PO's does not show any PO's as none have been closed
Checking Open & Closed PO's shows all PO's

(Normal function)
When Received PO's is checked, it shows nothing
When UnReceived PO's is checked, it shows nothing
When Received and Not is checked, it shows nothing

(Abnormal function)
When Open PO's is checked, it shows nothing
When Closed PO's is checked, it shows nothing
When Open & Closed PO's is checked, it shows all PO's

This response is repeated when the filters are used. Clicking on
(1)Show PO's to be sent, (2)Show PO's that are out waiting on parts, or (3)Show Received PO's
all show the same abnormal function described above.

Can you help me understand this procedure. I have started from the beginning of AC328 on four
occassions, and have run into the same problem in each instance.

 eddy geijselaers on 9/16/2011: Hi Rick,

Almost on the last lesson so far I like to ask you (as you asked to do) to make an lesson on "partial order shipping and returns". For this is quite normal to happen. As well as (if you are on the subject now) multiple vendors for any article. So there could be several "open orders" to several vendors who could suply the article we need.


Thanks in advance and looking forward to the next AC lesson eddy peanut geijselaers

Benjamin Chua on 12/5/2011: Richard,

Class Access 328 TimeLog 8:12

In this code, where does it say the QtyNeeded will be reduce if QtyToOrder > 0?

Benjamin Chua on 12/5/2011: Access 328 Time 8:18

Nevermind, previous Question it was covered in the Query. Did I find it correct? QtyNeed: ReOrderLevel - QtyOnHand -QtyOnOrder

Ben Chua on 12/5/2011: ditto. yeah this is True it will happen.
Benjamin Chua on 12/6/2011: Lesson 5 on Access 328

After this Lesson, I could not run the Order More button any longer.

I search on the accesslearningzone website plus the forum and could not find related issue.

Getting Error:
The Expression On click you entered as the event property setting produced the following error: User-defined type not defined.

Jim on 9/5/2012: Richard,

Is there an easy way to handle having more than one vendor for a product? Could I have a many to many realtionship between product and vendor, then populate a combo box with vendor selection when I make the Purchase Order?

Reply from Richard Rost:

Yep. That's how you do it. I cover this exact example in my Relationship Seminar.

Jim on 9/20/2012: Richard,

Please add my name to the list of those that want to see partial receipts and shipments. I would be happy to formally "vote" for it if the class is put up in that manner. Thanks.

Jim on 10/10/2012: Richard,

If I have multiple vendors for items in stock and I have a junction table with that info, how would I go about selecting the vendor with the lowest price? Would that happen in the query with a dlookup? or is there an easier way to do this?

Reply from Richard Rost:

You can use a DMIN to find the lowest price, then DLOOKUP to get the rest of the info

LowestPrice: DMIN("UnitPrice","VendorProductJunctionT","ProductID=" & MyProductID)

Now that you have the lowest price, just DLOOKUP the product ID and from that you can get the vendor ID. Of course, if two vendors have identical prices you'll get one at random. I suppose you could specify a priority order for vendors and then sort based on that. :)

Jim on 10/10/2012: Richard,

A sort of generic question for you. I have worked may way through these lessons and put what I have learned to good use in my Ambulance Service database. I just bought and went through your seminar on relationships and learned much more. I am anticpating taking the SQL seminar and was wondering if knowing SQL would replace the need to use datasets or is there a need to use both disciplines? Are recordsets a precurser to using SQL?

Reply from Richard Rost:

They compliment each other pretty well. You can learn one or both and use each of them quite well together, in fact.

If anything, I should have made a comprehensive SQL series BEFORE recordsets. I personally think that recordsets are MUCH more powerful than SQL, however SQL is good for short, quick operations (like quickly deleting, adding, or editing some records). And so many things in Access are based on SQL (listboxes, combo boxes, subforms, etc.) that it really helps to fully understand how the SQL language works.

HOWEEVER that being said, if you have anything COMPLEX to do, recordsets are the way to go. A lot of what I taught in AC320-329 probably COULD have been done with SQL, but it was necessary to show you simple recordsets in order to build up to more complex ones.

Learning both will make you a very well-rounded developer... and I'm not just saying that so you buy more lessons. However, if I had to pick one over the other, I'd probably go with recordsets myself. I use them a lot more often.

Jim on 10/12/2012: Richard,

Thanks as always for your insight. I am however now completely addicted so you'll just have to tolerate me over the upcoming years...Seriously I appreciate your classes and the time you take to interact with us all. I can't imagine all of the time and effort it takes...

Reply from Richard Rost:

It's what I do. I love my job. I love helping all of you learn Access. I wish I had MORE time for fun projects like these.

Jim on 10/23/2012: Richard,

I noticed something about the code we wrote in BuildPOList sub that created an issue on the Purchase Order Form. The listbox was originally designed to have the poid as item 0, the podate as item 1 and the vendor name as item 2. When we wrote the BuildPOList code, the order of the look up was vendorname then podate. Not a show stopper but the columns were sized wrong and that is where I noticed the issue. I reversed the order in the SQL statement in BuildPOList and the issue was resolved. Obviously not a show stopper but it proves to me that attention to minute detail is a must and it took me a while to figure out what was going on. Thanks for teaching me all that you have.

THopper on 2/10/2013: I too want to see received partial SO's & PO's.
James Gray on 5/2/2014: Rick,

I was just going through these lessons again as reenforcement learning and have a question about SQL versus Recordsets. If we use SQL for creating a new PO, can we still get the assigned autonumber or is that only available during the recordset sequence? The insert into statement seems to process the addtion without a chance to grab onto the newly assigned ID right?

Reply from Richard Rost:

SQL will not give the ID. You'd need a recordset for that. You could use SQL, then immediately use DMAX to get the ID, but that's less reliable, especially in a multi-user environment.

Robert Whishaw on 1/6/2016: I seem to have run into the same problems as RayA McCain but I've managed to solve the error messages & have a new problem.
The 2 issues that I found where:
1) there was no end if at the end of the nested elseif block
2) the video cut out the " & after the AND in the ReceivedPOs if statement. It should look like:
wherestr = wherestr & " AND " & wherestr = wherestr & "POReceived= true"

Now for my current problem:
When I select the openpos or receivedpos checkboxes the list box doesn't get filtered. Both it & the PODetail subform are blank. I've slightly altered the VBA code for my design purposes but the basis is the same.


Private Sub BuildPOList()

Dim s As String
Dim wherestr As String

s = "SELECT PurchaseOrderID, VendorName, PODate FROM PurchaseOrderQ"
wherestr = ""

If OpenPOs = True Then
wherestr = "IsOpen = True"
OpenPOLabel.Caption = "Open PO's"
ElseIf OpenPOs = False Then
wherestr = "IsOpen = False"
OpenPOLabel.Caption = "Closed PO's"
End If

If ReceivedPOs = True Then
If wherestr <> "" Then
wherestr = wherestr & " AND " & wherestr = wherestr & "POReceived= true"
ReceivedPOLabel.Caption = "Received PO's"
End If
ElseIf ReceivedPOs = False Then
If wherestr <> "" Then
wherestr = wherestr & " AND " & wherestr = wherestr & "POReceived= false"
ReceivedPOLabel.Caption = "Unreceived PO's"
End If
End If

If wherestr <> "" Then
s = s & " WHERE " & wherestr
End If

s = s & " ORDER BY PODate"

POList.RowSource = s
POList = POList.ItemData(0)
PODetailUpdate

If OpenPOs = True Then
PurchaseOrderDetailF.Locked = False
Else
PurchaseOrderDetailF.Locked = True
End If

End Sub


Any help would be much appreciated.
I'm loving these inventory management classes!
Thanks

Reply from Alex Hedley:

I'd need to check into this as it's been a few years since I've watch this.

One thing to check might be
PODetailUpdate

 

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