As I said in the original question... I did download the database and it gives me the same error when both check boxes are in the null position. And, I did read that post you mentioned... That code did not work either but I did figure out that I needed to add a line to the rs section to take the items out of quantity needed and that fixed that issue.
Hello Richard, I am using Access 2013. I am getting an error message when I choose the null value for both check boxes in the purchase order form. I thought maybe it was my code but i downloaded your database and it gives me the same error so i am thinking it must be the version i am using vs the version that you used at the time. What can I do to the code to fix this? Here is the code that is highlighted in yellow when i click to debug: Private Sub PODetailUpdate()
If IsNull(POList) Then ' no PO selected - show no records Me.RecordSource = "SELECT * FROM PurchaseOrderT WHERE PurchaseOrderID=-1" PurchaseOrderDetailsF.Form.RecordSource = _ "SELECT * FROM PurchaseOrderDetailsT WHERE PurchaseOrderID=-1"
ALSO... The code i copy and pasted from above to get the inventory to update after receiving the items does not work. I do not get any error messages ... but the inventory does not update... Thanks for your help! Shallena
Reply from Alex Hedley:
Does the sample db work on your machine?
There's a comment in the SI that explains the bug. Bug in Access 329 Code It's always useful to read the other student comments.
I couldn't help but notice that the video doesn't cover the updating of "qtyonhand" units in ProductT, only how to update the purchase order from unreceived to received which is quite simple. What kind of VBA recordset code do we need to update the "qtyonhand"?
I'd also really like to learn how to receive a partial order please.
Perhaps an addendum to your code to increase on hand quantities after parts received...shouldn't we add a line of code to decrease the on order quantity in the inventory table by the same amount? I just added a line: "rs2!QtyOnOrder=rs2!QtyonOrder-rs1!Quantity..that seemed to work OK.
I know this is not news to you but I discovered that when I went to write some of the SQL statements ( I don't remember which ones exactly now) I got an error that had to do with the default library set in Access 2010 (which I use). I had to reset the library back to DAO 3.x to get the code to work without error. Shouldn't the new libraries funtion with SQL seamlessly or did I perhaps do something incorrectly? This issue went away when I put the DAO Library ahead of the ActiveX....
Thanks for your prompt reply! i didn't expect that you personally give time for all comments and queries!!...btw, as ive said i already developing some applications via ms access but im interested in taking your VB tutorials does it cover vb-database programing? i suppose you are going to use access as your back end...are you planning to have MS SQL tutorial as well?? thanks! (i'm now searching within your site on how you deal clients abroad like me..im from philippines)
Reply from Richard Rost:
I wish I had time to personally answer EVERY post. I do the best I can. I cover VBA programming for Access in my 301-329 lessons. My "Visual Basic" courses are for VB6 (soon to be upgraded to VB2010) and are a whole separate program. Yes, SQL Server is coming up too. Lots in the pipeline. Customers outside the US are no problem. Everything can be downloaded or viewed online.
Sir, in the course outline you stated "Outlook security feature", i've been creating some basic access application at my work and im interested if you discussed also in this tutorial regarding auto email via VBA (my problem is the security prompt "....a program is trying to access") did you cover how to bypass this in your lesson?
Reply from Richard Rost:
Not really. This is by design. Microsoft doesn't want other programs sending spam via Outlook. You either have to deal with the prompt OR find an older version of Outlook that doesn't have it (I keep Outlook 2000 on one of my machines JUST for this purpose) OR use another email program. I have planned to create a seminar showing how to write your OWN email program using VBA. As soon as there are enough votes for it on the Waiting List, I'll put it together.
Hi Richard Well I've finished 329, I've inserted that missing code and all seems well, but after marking orders as received I'm still showing goods on order in ProductsT. I have been back through the lessons, but cannot find where I have gone wrong. Point me in the right direction please. Regards Len
As you mentioned, yeah partial order is interesting - but if you look at the table PurchaseOrderT, the default for PartsReceived is True - I think this is where you can adjust the quantity as partial if the return value is False.
Anyway while you are still deciding - I will play with the codes.
But I would really like to see is a RFID and BarCode Reader for Inventory. There are some merchandise comes in bulk as well. Most of them has a barcode already.
For warehousing delivery and receipts - this will also include RENTAL and RENTAL Contracts.
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.
This class for me was difficult. I checked all the code several times on several days but I cannot find where I am messing up. Even checked the tables used and their properties. (Also added the missing code).
So it is very tempting to just copy your db, because yours works. It might be because I am still working with XP and you with 2003.
I located the problem in the "BuildPOList" code. I can't get past an error message where Access asks for a variable on a yes/no field (it doesn't mention which). The event just doesn't work when changing the checkboxes. The labels don't work either. Still I am not able to find the difference between your code and mine.
Thanks in advance and looking forward to the next AC lesson eddy peanut geijselaers
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 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
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.
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.