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  
 

News      User Comments     History     Notify Me

1/1/2008 3:29:00 AM
Microsoft Access 329
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
Purchase order report, Emailing POs to vendors, sending bulk email from Access to Outlook

Permanent Link
Course Link: Microsoft Access 329
Post Reply

Null Values Comment from Shallena Ayers @ 3/6/2018
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.
Show Just This Thread        Post Reply
qtyonhand Comment from Robert Whishaw @ 2/11/2016
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.


Reply from Alex Hedley:

Was it not updated with this comment?
Show Just This Thread        Post Reply
Email via Access Comment from Deon @ 8/16/2013
Richard - good evening,

This is absolutely wonderful news and I'm sure there are many members of your access community who can hardly wait for this seminar to come in as well.

Cheers for now,

Deon


Reply from Richard Rost:

Yep. It's on the SHORT list. I'll be working on it soon.
Show Just This Thread        Post Reply
Email via Access Comment from Deon Riley @ 8/16/2013
Hi Richard,

Are you able to do something for us reference using bulk email using some VB or VBA programming that gets rid of the Outlook Security Warnings!

These warnings are annoying, and not condusive for running a small business.

If you are able to help with something to bypass these warnings would be so very much appreciated.

Best regards as always,

Deon


Reply from Richard Rost:

I'm going to do one better. I'm going to show you how to send email WITHOUT using Outlook. It's coming up in a new seminar very soon.
Show Just This Thread        Post Reply
Microsoft Access 329 Comment from Jim @ 10/26/2012
Richard,

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.
Show Just This Thread        Post Reply
Comment from Jim @ 10/23/2012
Richard,

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 you help as always.
Show Just This Thread        Post Reply
Microsoft Access 329 Comment from Gelo @ 3/1/2012
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.
Show Just This Thread        Post Reply
Microsoft Access 329 Comment from Gelo @ 2/28/2012
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.
Show Just This Thread        Post Reply
Comment from Len Jolly @ 1/20/2012
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
Show Just This Thread        Post Reply
Microsoft Access 329 Comment from Ben Chua @ 12/20/2011
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.
Show Just This Thread        Post Reply
Comment from Shams Momin @ 12/11/2011
hi Richard I really like to make partial order in receiving order for my db
Show Just This Thread        Post Reply
Microsoft Access 329 Comment from eddy geijselaers @ 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.

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
Show Just This Thread        Post Reply
Comment from Nick Fuller @ 1/5/2011
I'm also very interested to see receiving partial parts and even an entire inventory setup :)
Show Just This Thread        Post Reply
Bug in Access 329 Code Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from Terry Hopper @ 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.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 8+3:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE
7/31/2018Microsoft Access Developer 8
 

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
Change Email
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