Course List - Access 329 AllExperts

Student Interaction: Microsoft Access 329

Richard on 1/1/2008: Purchase order report, Emailing POs to vendors, sending bulk email from Access to Outlook

Terry�Hopper on 8/31/2009: I believe all I need to do to receive partial orders is to have a 'Yes/No' box next to the part being modified as 'Partial' or 'Not' correct? And write the code accordingly, correct? That should make the lesson simpler as opposed to doing an entire lesson on partial received items. Just a thought. I will try this.

Richard Rost on 4/12/2010: Out of the hundreds of students who have taken this course, only ONE has found this bug - and it's a good one. If you RECEIVE parts from a vendor, the Quantity On Hand in the Products table is never updated! Here's the updated code for the button (PartsReceivedButton) that fixes the problem. Just replace all of the code in the button with this: ' -------------------------------------------------------- If IsNull(POList) Then Exit Sub If MsgBox("Are you sure you want to mark this " & _ "purchase order PARTS RECEIVED?", _ vbYesNoCancel) <> vbYes Then Exit Sub ' --------- MISSING BEGIN ----------------------- Dim db As Database Dim rs1 As Recordset, rs2 As Recordset Set db = CurrentDb() Set rs1 = db.OpenRecordset("SELECT * FROM PurchaseOrderDetailsT WHERE PurchaseOrderID=" & POList, dbOpenSnapshot) rs1.MoveFirst While Not rs1.EOF Set rs2 = db.OpenRecordset("SELECT * FROM ProductT WHERE ProductID=" & rs1!ProductID, dbOpenDynaset) rs2.Edit rs2!QtyOnHand = rs2!QtyOnHand + rs1!Quantity rs2.Update rs2.Close rs1.MoveNext Wend rs1.Close Set rs1 = Nothing Set rs2 = Nothing Set db = Nothing ' --------- MISSING END ------------------------- Dim S As String S = "UPDATE PurchaseOrderT SET PartsReceived=TRUE " & _ "WHERE PurchaseOrderID=" & POList DoCmd.RunSQL S BuildPOList ' -------------------------------------------------------- As you can see, everything between the "MISSING BEGIN" and "MISSING END" was just added by me. You need to create a Recordset loop to go through each item in the PODetails table and increase that product ID's quantity on hand. I can't believe it has taken a year and a half for someone to catch this problem. This course was released in October of 2008. Wow. Anyhow, thanks to Darl H. for catching this... and sorry it took me so long to post a solution.

Add Your Questions or Comments