Results 1 to 13 of 13
  1. #1
    bnecrush is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    7

    Creating a Purchase Order


    I am trying to create a Purchase Order Form. I have a table for Vendor Items and costs already established. I would like a form that has a field for a Purchase Order number (that I enter manually) then below a drop down that would allow me to pick from the list of items already established in the vendor items table, next to that a place to enter in a quantity ordered. There may be several items ordered from the vendor so there would have to be the option for several lines. I am quessing another table would have to be created to store the PO's. I am fairly new to access so any help would be great!

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Before you even think about forms and reports, need to define data relationships and table structures.

    Conventional structure for a purchasing db is a table for vendors, a table for products, a table for purchase orders and a table for purchase order details. The last is a junction table that relates each purchase order with products selected for that purchase. Then if you want to track payments on the purchase orders, more tables. There are existing db templates that might serve your needs and lots of off-the-shelf apps like Quickbooks if you don't want to reinvent the wheel.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    bnecrush is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    7
    The purchase order portion of this db is just a small part. I have a table for vendors and a seperate one for products already. The table for products is where the part numbers, part name, cost, etc is stored and there is a combo box to choose the vendor from. I need to create a table for purchase orders and one for purchase order details then. Payments do not need to be tracked at this time (maybe later though). Is the PO form then setup with a sub-form for the product list? My goal is to have a form on which the PO is started by entering a PO number. Then below a vendor is chosen through a combo box, below that (or in a row format) a combo box provides a list of parts that is chosen. Next a quantity is entered. I am pretty sure I can get the calculation portion to work, just not sure how to set up the tables so that it works the way I want it to.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, use form/subform arrangement. The vendor selection would go in the PO record and products would be selected in the PO Details subform.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    bnecrush is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    7
    Do you know of any sample database out there that has this in it? I tried Northwind but I cant seem to get to the design view in it.

  7. #7
    DatabaseMakers is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    London
    Posts
    12
    hold down shift when you open the db!~)

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, holding the shift key while opening db (NOT executable accde) will override most project Options settings.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    bnecrush is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    7
    Sorry, I have tried several times to open the file while holding down the shift key and still no design view. How do I save the file as "db" and not "accdb"?

    Thanks!

  10. #10
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Make sure that when you are doing the Shift + Enter key it is not from within the Access Menu pate (I don't recall the specific name that MS has for this page). Go to Windows Explorer and find your file. Single click on it to highlight it and then depress the Shift and Enter keys together. db is not a file type. They were short cutting data base.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What do you mean by 'save the file as "db" and not "accdb"'?

    If you downloaded the template (accdt) file then when you open it, it should prompt you to save file as accdb. Open the accdb with shift key held down, don't release until the file is fully open.

    Doesn't matter where open the db from, Windows or Access app, press the shift key and hold, then click the file you want, hold down the shift key. Works for me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    bnecrush is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2012
    Posts
    7
    Thanks June7, I finally got it to work!! Sorry but I have another question. I have the subform on the main form and it only allows me to enter one record. In other words when I go to create a PO it only allows me to order 1 item. When I try and enter a second one an error comes up saying "the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship......" can you tell me why?

    Thanks!!

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Possibly has to do with form RecordSource query. Would have to examine db. If you want to provide, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Purchase Order Totals
    By ChipVerdi55 in forum Access
    Replies: 3
    Last Post: 08-24-2012, 03:57 PM
  2. Purchase Order Template
    By kstewart in forum Access
    Replies: 3
    Last Post: 04-25-2012, 01:07 PM
  3. Purchase Order Report
    By jordanturner in forum Forms
    Replies: 1
    Last Post: 10-13-2010, 10:53 PM
  4. how to creat purchase order
    By arctushar@yahoo.com in forum Access
    Replies: 5
    Last Post: 10-07-2010, 08:14 PM
  5. Help With Purchase Order Form
    By SpeedyApocalypse in forum Forms
    Replies: 29
    Last Post: 04-09-2010, 07:06 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums